Variable IF statement (in VBA)

[Post Tenebras Lux]

I would like to build a variable IF statement, that is being run in VBA code,
that draws the operand (>, < , = , >=, <=) from a cell on the spreadsheet.
The purpose is to permit the user to test a variety of conditions (which the
rest of the code does). Sort of like allowing the user to build an SQL query
on the fly.

For example:
I want to replace the following code:
If Signal > 0 and Signal < 100 then
'do something
End if

with something like this:

Dim strOperand1 as string, strOperand2 as string

If Signal strOperand1 0 and Signal strOperand2 100 then
' do something
End if

strOperand1 and strOperand2 would contain, for example, ">" and "<",
respectively. Each of these values would be entered by the spreadsheet user
into a cell directly (which is range named - so that strOperand1 =
Range("Operand1").value - for example).

I've tried & Evaluate(Operand1) & , but that doesn't work.

Any suggestions would be greatly appreciated!

 

[Die_Another_Day]

Try using Select Case

Case "<"
Blah,Blah
Case ">"
Blah,Blah
Etc...

Post back if you have problems...

HTH

Die_Another_Day

 

[Jim Thomlinson]

Evaluate works for me...

In cell A1 put >
In cell A2 put <
In cell A3 put =

Now run this code...
Sub test()
MsgBox Evaluate("10 " & Range("A1").Value & " 5")
MsgBox Evaluate("10 " & Range("A2").Value & " 5")
MsgBox Evaluate("10 " & Range("A3").Value & " 5")
End Sub

It is important to note that evaluate takes a full equation as it's
argument, not just the operand.

 

[Post Tenebras Lux]

If Evaluate(lngLower & strOperand_L & aIndicator(i, 0)) And
Evaluate(aIndicator(i, 0) & strOperand_R & lngUpper) Then



Works like a charm. Thanks!