aztariq
11-10-2013, 08:36 PM
Hi all. I am having trouble with a function I'm trying to write. The user has a sheet in the workbook that has an ActiveX button on it which when clicked opens InputBoxes where the user enters values which are then put into an array.
The catch is....
If they enter nothing or press enter, it needs to continue to the next InputBox
If they press Cancel then it needs to exit the whole subroutine altogether
NOTE: YES, I know I can have a loop go through the array (greatly reducing the amount of written code) and maybe use recursion to continually call InputBox and stick the values in but I've been away from vba and all programming for a while so I just coded each iteration line by line. Even with the loop I still have the same problem of differentiating between nothing being entered and the cancel button being hit.
Here is my code:
Sub Slope()
Dim Slopes() As Double
ReDim Slopes(11, 1)
Slopes(0, 0) = Application.InputBox("1st Month Put Slope", "Slopes")
If Slopes(0, 0) = False Then Exit Sub
Slopes(0, 1) = Application.InputBox("1st Month Call Slope", "Slopes")
If Slopes(0, 1) = False Then Exit Sub
Slopes(1, 0) = Application.InputBox("2nd Month Put Slope", "Slopes")
If Slopes(1, 0) = False Then Exit Sub
Slopes(1, 1) = Application.InputBox("2nd Month Call Slope", "Slopes")
If Slopes(1, 1) = False Then Exit Sub
Slopes(2, 0) = Application.InputBox("3rd Month Put Slope", "Slopes")
If Slopes(2, 0) = False Then Exit Sub
Slopes(2, 1) = Application.InputBox("3rd Month Call Slope", "Slopes")
If Slopes(2, 1) = False Then Exit Sub
Slopes(3, 0) = Application.InputBox("4th Month Put Slope", "Slopes")
If Slopes(3, 0) = False Then Exit Sub
Slopes(3, 1) = Application.InputBox("4th Month Call Slope", "Slopes")
If Slopes(3, 1) = False Then Exit Sub
Slopes(4, 0) = Application.InputBox("5th Month Put Slope", "Slopes")
If Slopes(4, 0) = False Then Exit Sub
Slopes(4, 1) = Application.InputBox("5th Month Call Slope", "Slopes")
If Slopes(4, 1) = False Then Exit Sub
Slopes(5, 0) = Application.InputBox("6th Month Put Slope", "Slopes")
If Slopes(5, 0) = False Then Exit Sub
Slopes(5, 1) = Application.InputBox("6th Month Call Slope", "Slopes")
If Slopes(5, 1) = False Then Exit Sub
Slopes(6, 0) = Application.InputBox("7th Month Put Slope", "Slopes")
If Slopes(6, 0) = False Then Exit Sub
Slopes(6, 1) = Application.InputBox("7th Month Call Slope", "Slopes")
If Slopes(6, 1) = False Then Exit Sub
Slopes(7, 0) = Application.InputBox("8th Month Put Slope", "Slopes")
If Slopes(7, 0) = False Then Exit Sub
Slopes(7, 1) = Application.InputBox("8th Month Call Slope", "Slopes")
If Slopes(7, 1) = False Then Exit Sub
Slopes(8, 0) = Application.InputBox("9th Month Put Slope", "Slopes")
If Slopes(8, 0) = False Then Exit Sub
Slopes(8, 1) = Application.InputBox("9th Month Call Slope", "Slopes")
If Slopes(8, 1) = False Then Exit Sub
Slopes(9, 0) = Application.InputBox("10th Month Put Slope", "Slopes")
If Slopes(9, 0) = False Then Exit Sub
Slopes(9, 1) = Application.InputBox("10th Month Call Slope", "Slopes")
If Slopes(9, 1) = False Then Exit Sub
Slopes(10, 0) = Applicaion.InputBox("11th Month Put Slope", "Slopes")
If Slopes(10, 0) = False Then Exit Sub
Slopes(10, 1) = Application.InputBox("11th Month Call Slope", "Slopes")
If Slopes(10, 1) = False Then Exit Sub
Slopes(11, 0) = Application.InputBox("12th Month Put Slope", "Slopes")
If Slopes(11, 0) = False Then Exit Sub
Slopes(11, 1) = Application.InputBox("12th Month Call Slope", "Slopes")
If Slopes(11, 1) = False Then Exit Sub
' MsgBox Slopes(0, 0)
' MsgBox Slopes(0, 1)
End Sub
The catch is....
If they enter nothing or press enter, it needs to continue to the next InputBox
If they press Cancel then it needs to exit the whole subroutine altogether
NOTE: YES, I know I can have a loop go through the array (greatly reducing the amount of written code) and maybe use recursion to continually call InputBox and stick the values in but I've been away from vba and all programming for a while so I just coded each iteration line by line. Even with the loop I still have the same problem of differentiating between nothing being entered and the cancel button being hit.
Here is my code:
Sub Slope()
Dim Slopes() As Double
ReDim Slopes(11, 1)
Slopes(0, 0) = Application.InputBox("1st Month Put Slope", "Slopes")
If Slopes(0, 0) = False Then Exit Sub
Slopes(0, 1) = Application.InputBox("1st Month Call Slope", "Slopes")
If Slopes(0, 1) = False Then Exit Sub
Slopes(1, 0) = Application.InputBox("2nd Month Put Slope", "Slopes")
If Slopes(1, 0) = False Then Exit Sub
Slopes(1, 1) = Application.InputBox("2nd Month Call Slope", "Slopes")
If Slopes(1, 1) = False Then Exit Sub
Slopes(2, 0) = Application.InputBox("3rd Month Put Slope", "Slopes")
If Slopes(2, 0) = False Then Exit Sub
Slopes(2, 1) = Application.InputBox("3rd Month Call Slope", "Slopes")
If Slopes(2, 1) = False Then Exit Sub
Slopes(3, 0) = Application.InputBox("4th Month Put Slope", "Slopes")
If Slopes(3, 0) = False Then Exit Sub
Slopes(3, 1) = Application.InputBox("4th Month Call Slope", "Slopes")
If Slopes(3, 1) = False Then Exit Sub
Slopes(4, 0) = Application.InputBox("5th Month Put Slope", "Slopes")
If Slopes(4, 0) = False Then Exit Sub
Slopes(4, 1) = Application.InputBox("5th Month Call Slope", "Slopes")
If Slopes(4, 1) = False Then Exit Sub
Slopes(5, 0) = Application.InputBox("6th Month Put Slope", "Slopes")
If Slopes(5, 0) = False Then Exit Sub
Slopes(5, 1) = Application.InputBox("6th Month Call Slope", "Slopes")
If Slopes(5, 1) = False Then Exit Sub
Slopes(6, 0) = Application.InputBox("7th Month Put Slope", "Slopes")
If Slopes(6, 0) = False Then Exit Sub
Slopes(6, 1) = Application.InputBox("7th Month Call Slope", "Slopes")
If Slopes(6, 1) = False Then Exit Sub
Slopes(7, 0) = Application.InputBox("8th Month Put Slope", "Slopes")
If Slopes(7, 0) = False Then Exit Sub
Slopes(7, 1) = Application.InputBox("8th Month Call Slope", "Slopes")
If Slopes(7, 1) = False Then Exit Sub
Slopes(8, 0) = Application.InputBox("9th Month Put Slope", "Slopes")
If Slopes(8, 0) = False Then Exit Sub
Slopes(8, 1) = Application.InputBox("9th Month Call Slope", "Slopes")
If Slopes(8, 1) = False Then Exit Sub
Slopes(9, 0) = Application.InputBox("10th Month Put Slope", "Slopes")
If Slopes(9, 0) = False Then Exit Sub
Slopes(9, 1) = Application.InputBox("10th Month Call Slope", "Slopes")
If Slopes(9, 1) = False Then Exit Sub
Slopes(10, 0) = Applicaion.InputBox("11th Month Put Slope", "Slopes")
If Slopes(10, 0) = False Then Exit Sub
Slopes(10, 1) = Application.InputBox("11th Month Call Slope", "Slopes")
If Slopes(10, 1) = False Then Exit Sub
Slopes(11, 0) = Application.InputBox("12th Month Put Slope", "Slopes")
If Slopes(11, 0) = False Then Exit Sub
Slopes(11, 1) = Application.InputBox("12th Month Call Slope", "Slopes")
If Slopes(11, 1) = False Then Exit Sub
' MsgBox Slopes(0, 0)
' MsgBox Slopes(0, 1)
End Sub