Logical String Evaluation

[Guest]

Does anyone know how to evaluate a logical string in VBA? An example of one
is:

"Month=4"

If the variable 'Month' is 4, then the string would be true.

Thanks,
VBA Dabbler

 

[Bob Phillips]

If Month = 4 Then
... whatever

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Do you mean, simply:

If Month = 4 Then
...
...
End If

 

[Mark Bigelow]

Try:

Application.Evaluate("Month=4")

Mark

 

[Bob Phillips]

I think you mean

Application.Evaluate(month = 4)

as month is a variable, which seems a pointless use of Evaluate to me when
you can do an If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

BTW, not a good idea to use Month as a variable name as that takes out the
VBA Month function.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub Tester1()
Dim m As Long
m = 4
Dim sStr As String
sStr = "m=4"
Debug.Print Evaluate(sStr)

End Sub

returns Error 2009

If that is what you were suggesting. the evaluate command has no knowledge
of VBA variables. It is a virtual worksheet cell.

 

[Bernd Plumhoff]

Option Explicit

Sub a()
Dim m As String

m = "1+2=3"
Debug.Print Evaluate(m)
m = "1+2=4"
Debug.Print Evaluate(m)

End Sub

will result in (press CTRL + g)
True
False

Avoid VBA keywords as variable names... (I took m, not
Month

HTH,
Bernd

 

[Guest]

Thanks for pointing me back to the evaluate method - I had tried it
unsuccessfully and started looking elsewhere.

I did get it to work.

Thanks,
VBA Dabbler

 

[Tom Ogilvy]

How about showing us what works.

 

[Guest]

No, I want to evaluate the string as though it were 'code', and the string
can change for subsequent evaluations.

The example I gave, however, would error on 'Type mismatch'.

Thanks,
VBA Dabbler

 

[Guest]

You're right. However, the string would be concatenated with literals for
comparison. Hence, if m=4, as you have it, the concatenated string to be
evaluated would be "4=4".

 

[Guest]

Agreed.

BTW, not a good idea to use Month as a variable name as that takes out the
VBA Month function.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Certainly. Try this.

MonthNumber = 4
OperatorString = "="
CriteriaValue = 4
If Evaluate(MonthNumber & OperatorString & CriteriaValue) Then
Msgbox "It Works!"
End If

 

[Bob Phillips]

I was responding to Mark.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hello,

I tried it and something else:

Sub OddStuff()
Dim mNum As Long, opStr As String, myCrit As Long
mNum = 4
opStr = "="
myCrit = 4
If Evaluate(mNum & opStr & myCrit) Then _
MsgBox "It Works! But not very well!"
End Sub

Sub ConventionalVB()
Dim mNum As String
Const MY_CRIT As Long = 4
Let mNum = "Month=4"
If Val(Mid$(mNum, InStr(1, mNum, "=", vbBinaryCompare) + 1, Len(mNum))) _
= MY_CRIT Then MsgBox "It Works!"
End Sub

Looks like OddStuff() is about ~450% slower on a single call and ~1,050%
slower on ten thousand calls, never mind the fact that it won't work in a
non-Excel environment.

If I was trying to parse a string, I might just do that.

Regards,
Nate Oliver

 

[Bob Phillips]

Nate,

Two points

You method doesn't allow an operator, that is presumably why the OP opted
for Evaluate.

It is 10 times quicker to use a simple If test. In fact, even allowing for
an operator like so
n = Timer
For i = 1 To 1000000
mNum = 4
Select Case opStr
Case "<": j = mNum < 4
Case ">": j = mNum > 4
Case "<>": j = mNum <> 4
Case "=": j = mNum = 4
End Select
Next i
Debug.Print "SimpleVB=" & Timer - n

is twice as quick as yours.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

Two points:

1) "=" is the operator in my sub.

2) We're clearly working with different assumptions.

You're assuming mNum is passed to the routine as Long Integer, if it is then
I agree, drop all of this monkey business, it's a no brainer.

I assumed mNum is passed to the Sub as a String, in which case, parse it the
way most experienced VB[A] developers would.

Regards,
Nate Oliver

 

[Bob Phillips]

Bob,

Two points:

1) "=" is the operator in my sub.

But that is not a variable as it is part of the formula. The only way you
can make it variable without code change is using Evaluate!

2) We're clearly working with different assumptions.

You're assuming mNum is passed to the routine as Long Integer, if it is then
I agree, drop all of this monkey business, it's a no brainer.

No I am assuming that Month is a variable, because we were told it is so, so
we don't need a string assumption, in fact assuming something when told
otherwise is perveres.

I assumed mNum is passed to the Sub as a String, in which case, parse it the
way most experienced VB[A] developers would.

Oooh!

 

[Tom Ogilvy]

That's fine, but that isn't what you originally asked.

your code does

Evaluate("4=4")

But thanks for clearing it up.