Using variables for operators

[Kellogg]

Dear Great Minds of Excel,


Is it possible to use a variable to represent an operator. For example, in an if then statement I want the operator to be able to be changed based on a users input. Can I replace >= with Oprtr after defining Oprtr as ">="?

Dim Oprtr
OprtrA = ">="
OprtrB = "<="

oRow = 1
oCol = 4

'In the following formula I want to replace the >= with the variable Oprtr

     If ws.Cells(oRow, oCol).Offset(0, 0) > "" And Format(ws.Cells(oRow, oCol).Offset(0, dte), "yyyy/mm/dd") >= Format(tbxVehicleDateFrom, "yyyy/mm/dd") And Format(ws.Cells(oRow, oCol).Offset(0, dte), "yyyy/mm/dd") <= Format(tbxVehicleDateTo, "yyyy/mm/dd") Then


'The new formula would be:

     If ws.Cells(oRow, oCol).Offset(0, 0) > "" And Format(ws.Cells(oRow, oCol).Offset(0, dte), "yyyy/mm/dd") [B][COLOR=#FF0000]OprtrA[/COLOR][/B] Format(tbxVehicleDateFrom, "yyyy/mm/dd") And Format(ws.Cells(oRow, oCol).Offset(0, dte), "yyyy/mm/dd") [B][COLOR=#FF0000]OprtrB[/COLOR][/B] Format(tbxVehicleDateTo, "yyyy/mm/dd") Then

Is this possible in vba?


Thanks for any assistance on this.


/s/

Craig

 

[Scott Huish]

You'd have to build a complete string including the operator and use Evaluate to return the TRUE/FALSE value. I'm going to show a simpler version here to give you an example.

Sub test()
Dim OprtrA As String, OprtrB As String
OprtrA = ">="
OprtrB = "<="

MsgBox "7" & OprtrA & "9=" & Evaluate("7" & OprtrA & "9")
MsgBox "7" & OprtrB & "9=" & Evaluate("7" & OprtrB & "9")
End Sub

 

[Kellogg]

Scott,

Thanks for the quick reply. Are you saying that Evaluate("7" & OprtrA & "9") should return True after substituting the operator, using Oprtr = "<="? This concept is very new to me. I will try and insert your example into a userform button and test. Then give a shot at building a string. Will respond when I get right. Thanks again


/s/


Craig

 

[Kellogg]

Scott,

Trying this out on a simpler if-then statement the syntax is not working. I attempted to copy your example but how do I get the entire if-then in the evaluate format including the carriage returns? How do I fill in the entire formula?

Dim OprtrA As String, OprtrB As String
OprtrA = "<"
OprtrB = ">"


ColWdtTot = 94

'[B][COLOR=#FF0000]Original[/COLOR][/B]
Dim PgOrin As String
'If ColWdtTot < 90 Then
'PgOrin = xlPortrait
'ElseIf ColWdtTot >= 90 Then
'PgOrin = xlLandscape
'End If


'[B][COLOR=#FF0000]New Modified[/COLOR][/B]
If (Evaluate("If" & ColWdtTot & " " & OprtrA & " 90")) = True Then
PgOrin = xlPortrait
ElseIf (Evaluate("If" & ColWdtTot & " " & OprtrB & " 90")) = True Then
PgOrin = xlLandscape
Else
End If

When I try this the page orientation sets at Portrait instead of Landscape. Did I code this right?


Thanks again for the review.



/s/


Craig

 

[Scott Huish]

There should be no IF in your Evaluate statement

If Evaluate(ColWdTot & OprtrA & "90") Then

You can put the = TRUE but it is not necessary

 

[Kellogg]

Scott,

I understand the concept and it works. Thanks. I'm hoping to build the complex string from the example. Thanks again.