Variable IF statement (in VBA)

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!

Try using Select Case

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

Post back if you have problems...

HTH

Die_Another_Day

Post Tenebras Lux wrote:
> 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!

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.
--
HTH...

Jim Thomlinson


"Post Tenebras Lux" wrote:

> 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!
>
>

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



Works like a charm. Thanks!



"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Post Tenebras Lux" wrote:
>
> > 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!
> >
> >