Guides the User with Prompts to go through a procedure and Records User Input

Question

I am making a prototype worksheet that follows 9 step problem solving procedure.

Some improvements I'm considering would be to iterate over some of it instead of making a new code line for every single variant. More or less I need to optimize this code so I can learn how to do this type of thing in the future.

It's not a very straight looping process and I can't think of any good ways to set it up.

Here is a picture of the sheet that the values will go into.

Here is my code

Sub nine_Step()

Dim step1, step2, step3, step4a, step4b, step4c, step4d, step4e, step5a, step5b, step5c, step5d, step5e, step6a, step6b, step6c, step6d, step6e, step7, step8, step9 As Variant


step1 = InputBox("Step 1: Define the Problem.")
    Cells(2, 3).Value = step1


step2 = InputBox("Step 2: What is the Measured Actual Value?")
    Cells(4, 3).Value = step2


step3 = InputBox("Step 3: What is the Target Value Desired?")
    Cells(6, 3).Value = step3

    If step3 <> "" Then
        Cells(7, 3).Value = step3 - step2
    Else:
    End If

step4a = InputBox("Step 4: Why is " & step1 & " happening?")
    Cells(9, 3).Value = step4a
step4b = InputBox("Step 4: Why is " & step4a & " happening?")
    Cells(10, 3).Value = step4b
step4c = InputBox("Step 4: Why is " & step4b & " happening?")
    Cells(11, 3).Value = step4c
step4d = InputBox("Step 4: Why is " & step4c & " happening?")
    Cells(12, 3).Value = step4d
step4e = InputBox("Step 4: Why is " & step4d & " happening?")
    Cells(13, 3).Value = step4e


step5a = InputBox("Step 5: Describe your First Possible Solution")
    Cells(15, 3).Value = step5a
step5b = InputBox("Step 5: Describe your Second Possible Solution")
    Cells(16, 3).Value = step5b
step5c = InputBox("Step 5: Describe your Third Possible Solution")
    Cells(17, 3).Value = step5c
step5d = InputBox("Step 5: Describe your Fourth Possible Solution")
    Cells(18, 3).Value = step5d
step5e = InputBox("Step 5: Describe your Fifth Possible Solution")
    Cells(19, 3).Value = step5e


step6a = InputBox("Step 6: Experiment Measurement Results Solution 1")
    Cells(21, 3).Value = step6a
step6b = InputBox("Step 6: Experiment Measurement Results Solution 2")
    Cells(22, 3).Value = step6b
step6c = InputBox("Step 6: Experiment Measurement Results Solution 3")
    Cells(23, 3).Value = step6c
step6d = InputBox("Step 6: Experiment Measurement Results Solution 4")
    Cells(24, 3).Value = step6d
step6e = InputBox("Step 6: Experiment Measurement Results Solution 5")
    Cells(25, 3).Value = step6e


step7 = InputBox("Step 7: When did you impliment a solution? Enter a date in 
XX/XX/XXXX format")

If step7 = "" Then
    MsgBox ("Find out when you implimented a solution and enter it when you get a chance.")
Else:
    Cells(28, 3).Value = step7
End If


step8 = InputBox("Step 8: Now that the solution is implemented perminently, what is the current measurement?")
    Cells(31, 3).Value = step8

    If step8 <> "" Then
        Cells(32, 3).Value = Cells(6, 3).Value - step8
    Else:
    End If

step9 = InputBox("Step 9: Any other Ideas that have sprung up since you have started this nine step related to this area?")
    Cells(34, 3).Value = step9

MsgBox ("You have successfully completed this 9 Step.  Congrats!")



End Sub

Answer 1

The way you are declaring your variables isn't what you expect it to be. Luckily, you're declaring variants anyway. But if you weren't declaring Variants they would all by default be Variants.

Dim step1 as variant
Dim step2 as variant, step3 as variant

You need to give all variables a type.

You also have some superfluous variables -

step6a = InputBox("Step 6: Experiment Measurement Results Solution 1")
    Cells(21, 3).Value = step6a
step6b = InputBox("Step 6: Experiment Measurement Results Solution 2")
    Cells(22, 3).Value = step6b

Since you don't re-reference these variables later on, you can just write to the cell

.Cells(21,3) = InputBox("Step 6: Experiment Measurement Results Solution 1")
.Cells(22,3) = InputBox("Step 6: Experiment Measurement Results Solution 2")

---

Guiding the user

You mention you want to guide the user here. When you prompt them for input, you should tell them what type of input, and then check it's the correct Type.

 Cells(7, 3).Value = step3 - step2

This will error if there are values that aren't numerical. You can define what type you want.

You asked me

What is the Measured Actual Value?

But I didn't know if you wanted what kind of measurement or an integer. This can cause a mismatch error later on.

 step3 = Application.InputBox("Step 3: What is the Target Value Desired?", Type:=1)

Now you require me to enter a number value. You control what happens if I don't put in the expected type.

---

Refactoring

You see yourself doing the same thing over and over. Normally you would farm this out to a function, sending arguments and getting data back. But, you're using the InputBox which is already a function. So how to make this cleaner? Arrays!

Make yourself an input array and an output array. We know how many questions there are, so no problem.

Dim questions(1 to 21) as String
Dim answers(1 to 21) as Variant

And then populate your questions and pass them to inputbox with a type, and set each consecutive item in the answers array to the response. Then a simple loop

For i = 9 to 25
  .Cells(3,i) = answers(i-8)
Next

There are some other things you could do with your strings and whatnot, but that's up to you.

---

For the brave

Something like this -

Const STEP1 As String = "Define the Problem."
Const STEP2 As String = "What is the Measured Actual Value?"
Const STEP3 As String = "What is the Target Value Desired?"
Const STEP4a As String = "Why is " & questions(1) & " happening?"
Const STEP4b As String = "Why is " & questions(4) & " happening?"
Const STEP4c As String = "Why is " & questions(5) & " happening?"
Const STEP4d As String = "Why is " & questions(6) & " happening?"
Const STEP4e As String = "Why is " & questions(7) & " happening?"
Const STEP5a As String = "Step 5: Describe your First Possible Solution"
Const STEP5b As String = "Step 5: Describe your Second Possible Solution"
Const STEP5c As String = "Step 5: Describe your Third Possible Solution"
Const STEP5d As String = "Step 5: Describe your Fourth Possible Solution"
Const STEP5e As String = "Step 5: Describe your Fifth Possible Solution"
Const STEP6a As String = "Step 6: Experiment Measurement Results Solution 1"
Const STEP6b As String = "Step 6: Experiment Measurement Results Solution 2"
Const STEP6c As String = "Step 6: Experiment Measurement Results Solution 3"
Const STEP6d As String = "Step 6: Experiment Measurement Results Solution 4"
Const STEP6e As String = "Step 6: Experiment Measurement Results Solution 5"
Const STEP7 As String = "Step 7: When did you impliment a solution?"
Const STEP8 As String = "Step 8: Now that the solution is implemented perminently, what is the current measurement?"
Const STEP9 As String = "Step 9: Any other Ideas that have sprung up since you have started this nine step related to this area?"

Dim questions(1 To 21) As String
questions(1) = STEP1
questions(2) = STEP2
questions(3) = STEP3
questions(4) = STEP4a
questions(5) = STEP4b
questions(6) = STEP4c
questions(7) = STEP4d
questions(8) = STEP4e
questions(9) = STEP5a
questions(10) = STEP5b
questions(11) = STEP5c
questions(12) = STEP5d
questions(13) = STEP5e
questions(14) = STEP6a
questions(15) = STEP6b
questions(16) = STEP6c
questions(17) = STEP6d
questions(18) = STEP6e
questions(19) = STEP7
questions(20) = STEP8
questions(21) = STEP9

Then you can loop through like

For i = 1 To 21
    answers(i) = InputBox(questions(i))
End Sub

But you might need a custom argument in case you need to restrict what Type the user can enter. This you cannot do with a UserForm unless you get into keypresses and whatnot.