Store VBA variable conditionals in a cell

Question

I am simplifying my question to get rid of details not necessary for my question.

For a trivial example of what I'm trying to do, here is simple code:

Dim result As Boolean
Dim var1 as integer
Dim rule As string
rule = "(var1 = 3)"
var1 = getthisfromsomewhere()

'How to evaluate the rule?
result = ????(rule)

Basically, I want to evaluate a string that if it was in an if statement, it would return true or false.

I have seen the Evaluate() function but that seems to work on cells, not on VBA variables. So, can this be done?

[Explanation of purpose: I am unfortunately required to use VBA for this project even though it is not ideal. We want the rules to be editable without going into the VBA code itself. Basically we will have a sheet named RULES that will have in column A the code to evaluate if true. And in column B put a string to log if the result when evaluating column A is true. The VBA code has dozens, maybe a couple hundred variables. The list of rules will be in the dozens to maybe a couple of hundred rules. The rules change occasionally so we have to update the rules list. The rule can be an arbitrary Boolean expression made up from those variables. ((var1 = 1) and (var2 = 2) and (var3 = 3)) is a valid rule assuming var1, var2 and var3 are defined in the VBA code.]

If I'm understanding this correctly I think you would be better off creating a form where you can regulate the conditions a bit better. A box for the base message, a bool for holding, a drop down for person. — Warcupine
well a form would also use VBA, the issue with free hand typing is are they going to recognize small typos that are causing errors/bad results? You could still have the form write to cells, it would just make it a little less likely for there to be issues. setting a variable to equal something in a form is much less error prone than free typing and much less error handling and validation required. — Warcupine
I mean you can set a variable to pull a value from a field, so you can present it as var1 = 3 they just need to put the 3 into the var1 field, now var1 in the code = 3. instead of trying to search for var1, using a select statement of somekind to determine the operator and then setting var1 to 3 is going to be magnitudes more code. If you don't want to use a form thats fine, I just thought it would be easier for you. — Warcupine
I am still not following how that code would work. I edited my question to be more succinct. I hope you are able to provide sample code to work in my example. Thanks. — Paul

Warcupine Warcupine · Accepted Answer · 2019-10-11T13:24:43

Okay, so getting into this I think I have a solution, I just made a basic example otherwise it would have taken me all day.

There are three "parts" to this, a sheet to store the rules, a form to create the rules, and a class to evaluate the rules.

Here's the form:

I'm lazy and didn't give it a real name, or add labels but the gist is they pick the variable and operator, then enter a value. After that they log the rule, it goes to the sheet to be stored there. (I didn't put in any way to modify them but that should be fairly easy.)

Once they have the rules, they evaluate them all.

Code for form:

Option Explicit

Private Sub Cmd_LogRules_Click()
    'Log the rules
    Dim lr As Long

    With ThisWorkbook.Sheets(1)
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(lr + 1, 1).value = ComboBox1.value & " " & ComboBox2.value & " " & TextBox1.value
    End With
End Sub

Private Sub Cmd_Evaluate_Click()
    'Execute rules
    Dim ruledict As Object
    Dim vardict As Object
    Dim lr As Long
    Dim i As Long
    Dim ruleclass As Class1
    Dim rulesplit As Variant
    Set vardict = load_vardict()
    Set ruledict = CreateObject("Scripting.Dictionary")
    'Not sure if you need the ruledict but I'm not sure how elaborate this needs to be and will be helpful probably
    With ThisWorkbook.Sheets(1)
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To lr
            Set ruleclass = New Class1
            rulesplit = Split(.Cells(i, 1).value, " ")
            ruleclass.load rulesplit(1), rulesplit(2)
            ruledict.Add rulesplit(0), ruleclass
            .Cells(i, 2).value = ruleclass.evaluate_rule(vardict(rulesplit(0)))
        Next
    End With
End Sub
Private Function load_vardict() As Object
    'Not sure where you are getting the real variable values but they are being stored in this dictionary
    Dim tempdict As Object

    Set tempdict = CreateObject("Scripting.Dictionary")

    tempdict.Add "var1", 2
    tempdict.Add "var2", 4
    tempdict.Add "var3", 6

    Set load_vardict = tempdict

End Function
Private Sub UserForm_Initialize()
    'Add any additional variables/operators you need
    With ComboBox1
        .AddItem "Var1"
        .AddItem "Var2"
        .AddItem "Var3"
    End With

    With ComboBox2
        .AddItem "="
        .AddItem "<>"
    End With

End Sub

Here is the log sheet:

And here is the class code:

Option Explicit

Private pOperator As String
Private pValue As Variant

Public Sub load(ByVal op As String, ByVal val As Variant)
    'Parameters are byval because it was yelling at me, there's probably a way to make them byref and have it accept them.
    'You might not need a class but if it gets more complicated it will be helpful
    pOperator = op
    pValue = val

End Sub

Public Function evaluate_rule(value As Long) As Boolean
    'Select case for operators, there might be a more clever way of doing this...
    Select Case pOperator
        Case "="
            If value = pValue Then
                evaluate_rule = True
            Else
                evaluate_rule = False
            End If
        Case "<>"
            If value <> pValue Then
                evaluate_rule = True
            Else
                evaluate_rule = False
            End If
        End Select
End Function

There are some unnecessary things in the code, for this example, like the rule dictionary but I think they will be helpful to you, so I threw them in.

EDIT: I guess the core of it is to use a dictionary where the key is the "variable" and the item is the rule. Though I still think the form is a good idea to keep it all clean.

Store VBA variable conditionals in a cell

1 Answers