Learning VBA - BODMAS Generator

Question

I'm teaching myself VBA using online tutorials. Using what I've learned so far I've made a simple BODMAS question generator. However I cannot figure out how to compute the answer to the question generated. Here's what I've done:

Dim Indicator As String, Equation As String, IndicatorNum As Integer, RandNum As Integer, Answer As Integer

Type EqnStatements
   Statement1 As Integer
   Statement2 As Integer
   Statement3 As Integer
End Type

Type Indicators
   Indicator1 As String
   Indicator2 As String
End Type

Private Sub IndicatorGenerator()
    IndicatorNum = Int(Rnd * 4)
    Select Case IndicatorNum
    Case Is = 0
        Indicator = "+"
    Case Is = 1
        Indicator = "-"
    Case Is = 2
        Indicator = "*"
    Case Is = 3
        Indicator = "/"
End Select
End Sub

Private Sub StatementGenerator()
RandNum = Int(Rnd * 10 + 1)
End Sub

Sub EquationGenerate()
Dim Eqn As EqnStatements, Ind As Indicators

    StatementGenerator
        Eqn.Statement1 = RandNum
    StatementGenerator
        Eqn.Statement2 = RandNum
    StatementGenerator
        Eqn.Statement3 = RandNum

    IndicatorGenerator
        Ind.Indicator1 = Indicator
    IndicatorGenerator
        Ind.Indicator2 = Indicator

    Equation = Eqn.Statement1 & " " & Ind.Indicator1 & " " & Eqn.Statement2 & " " & Ind.Indicator2 & " " & Eqn.Statement3

    Cells(2, 3) = Equation
    End Sub


Sub AnswerShow()

  Answer = Eqn.Statement1 & Ind.Indicator1 & Eqn.Statement2 & Ind.Indicator2 & Eqn.Statement3
  Cells(3, 3) = Answer

End Sub

The first main Sub works fine, and produces the output. When I run the second main sub (AnswerShow) I get an error: "Object required". I'm not sure how to get it to calculate the equation using the values stored in the variables.

Have a look at this, to find out how you can use objects defined it another `Sub` in a new one. http://stackoverflow.com/questions/22980061/excel-vba-object-sub-call-with-2-object-parameters-gives-compile-error-expected — Tom K., Jul 27 '16 at 15:24
By now you only define them in `EquationGenerate()`. For learning how to define objects globally, have a look at this: http://stackoverflow.com/questions/21380724/best-practice-for-creating-a-public-object-in-excel-vba — Tom K., Jul 27 '16 at 15:45

score 0 · Accepted Answer · answered Jul 27 '16 at 15:43

The reason why you're facing the 'object required' error, is that you're attempting to use the variable Eqn within the AnswerShow procedure, however this variable does not exist.

You assigned values to the properties of the object variable Eqn, within the EquationGenerate procedure, however this object was lost from memory once this procedure finished running.

You would have to pass the Eqn object variable into the AnswerShow procedure to be able to access the properties.

I would also recommend using a function to generate the number, instead of a procedure with a public variable. See below code.

Private Function Indicator() As String

    Dim indicator_num As Integer

    indicator_num = Int(Rnd * 4)
    Select Case indicator_num
        Case 0
            Indicator = "+"
        Case 1
            Indicator = "-"
        Case 2
            Indicator = "*"
        Case 3
            Indicator = "/"
    End Select

End Function

Private Function RandNum() As Integer

    RandNum = Int(Rnd * 10 + 1)

End Function

Sub EquationGenerate()

    Dim Eqn As EqnStatements, Ind As Indicators

    Eqn.Statement1 = RandNum
    Eqn.Statement2 = RandNum
    Eqn.Statement3 = RandNum

    Ind.Indicator1 = Indicator
    Ind.Indicator2 = Indicator

    Equation = Eqn.Statement1 & " " & Ind.Indicator1 & " " & Eqn.Statement2 & " " & Ind.Indicator2 & " " & Eqn.Statement3

    Cells(2, 3) = Equation

    AnswerShow Eqn, Ind

End Sub


Sub AnswerShow(ByRef Eqn As EqnStatements, ByRef Ind As Indicators)

    Answer = Eqn.Statement1 & Ind.Indicator1 & Eqn.Statement2 & Ind.Indicator2 & Eqn.Statement3
    Cells(3, 3) = Answer

End Sub

score 0 · Answer 2 · answered Jul 27 '16 at 15:46

You are storing the characters '*', '/', '+' and '-' in memory locations called `Indicator'. However this does not make them take the function of an indicator.

Theoretically you would need to write code the interpreted each of those characters and then commanded the appropriate calculation:

`If Ind = "*" then Ans = x * y

In your case there'd be a fair bit of additional coding to get the sequence right.

As it happens VBA has an evaluate function (https://msdn.microsoft.com/en-us/library/office/ff193019.aspx) that will evaluate a string formula. So you could just change your AnswerShow line to

Answer = Evaluate(Eqn.Statement1 & Ind.Indicator1 & Eqn.Statement2 & Ind.Indicator2 & Eqn.Statement3)

My rep isn't high enough to publicly mark your answer as useful, so I'll leave a comment. The 'evaluate' function was the final piece to make the code work, in addition to luke_t's answer. Much appreciated — M.White, Jul 28 '16 at 10:08

Learning VBA - BODMAS Generator

2 Answers2