VBA - Evaluate Conditions as String

Question

I have come across a very strange scenario. In a function I will receive a string of condition to be evaluated.

E.g.

(a>b and (b=2 or c!=3))

Where a,b and c are my variable names.

I tried and searched a lot but didn't get anything meaningful.

So my question is: Is it possible to evaluate a string like this? If yes, please provide me some hint on this.

@AshReva "(a>b and (b=2 or c!=3))" can be one of such strings — TechGeek, Mar 13 '13 at 21:56
no my question was what you want to do when you recieve such strings? do you want to compare or what? — DevelopmentIsMyPassion, Mar 13 '13 at 22:04
@AshReva: Simply need to evaluate the condition which may result in **true** or **false** — TechGeek, Mar 13 '13 at 22:07
If that's all you're actually receiving then no, it's not possible because you don't have enough information. We don't know the starting value of a, b or c is, thus, math cannot be performed. — StoriKnow, Mar 13 '13 at 22:14
@Sam: In VBScript there is something like Execute and ExecuteFunction. Isn't there anything like that in VBA? — TechGeek, Mar 13 '13 at 22:21
To my knowledge, there is no "Eval" function in VBA like there is in VBScript and JavaScript. I don't think the Excel Evaluate() would work since it's an Application method that seems to work on worksheet ranges. — raybiss, Mar 13 '13 at 23:42
See my answer here: http://stackoverflow.com/a/14492230/990403. Your variables values would somehow have to be shared with the tempGetConstValue function. Probably just by entering their values into the function as the evaluation is. If you want help with that let me know. — mkingston, Mar 14 '13 at 00:36
Some of the other answers to that question might be relevant to you if you have a reasonably restricted range of possible variable values. — mkingston, Mar 14 '13 at 00:38

score 2 · Accepted Answer · answered Mar 14 '13 at 11:03

2

Alternative way, add a reference to Microsoft Script Control

Dim vx As MSScriptControl.ScriptControl
Set vx = New MSScriptControl.ScriptControl

a = 100
b = 200
c = 300
Cond = "(a>b and (b=2 or c<>3))"

With vx
    .Language = "VBScript"
    .AddCode "function stub(a,b,c): stub=" & Cond & ": end function"

    result = .Run("stub", a, b, c)
End With

MsgBox result

Note you will need to replace != with <> as the former is not valid in VB* (and and/or is not valid in jScript)

answered Mar 14 '13 at 11:03

Alex K.

171,639
30
264
288

Thank u for this great code. Can you please help me in evaluating Cond="(InStr(t,2,1)=""2"")" I tried a lot but it is giving error – TechGeek Mar 14 '13 at 12:42
What is the `instr()` supposed to do? If `t` is intended to be a string it wont work. As it stands its looking for "*1* in *2*" (treating them as strings) starting from position *integer* `t`. If the `instr` string you receive does not map exactly to the VBScript equivalent in terms of its behaviour none of these approaches will work. – Alex K. Mar 14 '13 at 13:38

score 1 · Answer 2 · answered Mar 14 '13 at 00:48

Here's a proper answer to your question, rather than just a comment.

You need to:

Set a reference to Microsoft Visual Basic for Applications Extensibility x.x (Tools/References) in the VBIDE.
Trust access to the VBA project object model (use Google to find out how to do this for your version of Excel).
Run initValues() then call getConstantValue("(a>b and (b=2 or c<>3))")

Code:

Option Explicit

Dim a As Long
Dim b As Long
Dim c As Long

Sub initValues()
    a = 3
    b = 2
    c = 4
End Sub

Function getConstantValue(constStr As String) As Variant

    Dim oMod As VBIDE.CodeModule
    Dim i As Long, _
        num As Long

    Set oMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

    For i = 1 To oMod.CountOfLines
        If oMod.Lines(i, 1) = "Function tempGetConstValue() As Variant" Then
            num = i + 1
            Exit For
        End If
    Next i

    oMod.InsertLines num, "tempGetConstValue = " & constStr

    getConstantValue = Application.Run("tempGetConstValue")

    oMod.DeleteLines num

End Function

Function tempGetConstValue() As Variant
End Function

VBA - Evaluate Conditions as String

2 Answers2

Linked