Evaluate text formula in VBA
 

Please someone help me with Evaluate function. I have some rules written in
Access table as string with predefined variables in it like:

and(" & CCA & "<30, " & CCB & "800)
and(""" & AAA & """=""" & AAB & """, """ & BBA & """<""" & BBB & """)
and(" & CCA & "<24, """ & BBA & """=""8o6t56565a"")

Then I'm trying to get these rules, supply with the same variables, Evaluate
them in excel and get True or False results. The code lookes like:

Sub HitRules()
Dim k As Integer
Dim DBS As New ADODB.Connection
Dim rstAl As New ADODB.Recordset
Dim AAA, AAB, BBA, BBB As String
Dim CCA, CCB As Long
Dim hhhh

With DBS
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "D:\TestDB.mdb"
End With
rstAl.Open "Select * from Rules", DBS, adOpenStatic, adLockReadOnly
AAA = "0503"
AAB = "0503"
BBA = "8o6t56-a"
BBB = "8o6t56-b"
CCA = 22
CCB = 1200
For k = 0 To rstAl.RecordCount - 1
hhhh = CStr(rstAl!Rule) 'formulas provided above are stored in
rstAl!Rule
Debug.Print k+1 & " - " & Evaluate(hhhh) 'Result should be True or
False
rstAl.MoveNext
Next k
rstAl.Close
DBS.Close
End Sub

But it does not work... It seems that Evaluate does not understands variable
names (AAA, AAB, etc.) and the result is always False(?). And on the 3'rd
rule, error msg appeares: runtime error 13 - type mismatch...
Please advice me something how to avoid this problem. Is there any other way
to read and execut the string formulas?

Evaluate text formula in VBA
 

The problem it is not CCA as a variable, but as part of teh string, as is
the " and the &.

I would use placeholders for the variable and parse it in the code. For
instance, in the database store

AND(<CCA<30,<CCB800)

and then within the code use

x = Range("M1").Value
x = Replace(x, "<CCA", CCA)
x = Replace(x, "<CCB", CCB)
MsgBox Evaluate(x)

I have used an Excel range instead of the recordset item, same principle.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gvaram" wrote in message
...
Please someone help me with Evaluate function. I have some rules written
in
Access table as string with predefined variables in it like:

and(" & CCA & "<30, " & CCB & "800)
and(""" & AAA & """=""" & AAB & """, """ & BBA & """<""" & BBB & """)
and(" & CCA & "<24, """ & BBA & """=""8o6t56565a"")

Then I'm trying to get these rules, supply with the same variables,
Evaluate
them in excel and get True or False results. The code lookes like:

Sub HitRules()
Dim k As Integer
Dim DBS As New ADODB.Connection
Dim rstAl As New ADODB.Recordset
Dim AAA, AAB, BBA, BBB As String
Dim CCA, CCB As Long
Dim hhhh

With DBS
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "D:\TestDB.mdb"
End With
rstAl.Open "Select * from Rules", DBS, adOpenStatic, adLockReadOnly
AAA = "0503"
AAB = "0503"
BBA = "8o6t56-a"
BBB = "8o6t56-b"
CCA = 22
CCB = 1200
For k = 0 To rstAl.RecordCount - 1
hhhh = CStr(rstAl!Rule) 'formulas provided above are stored in
rstAl!Rule
Debug.Print k+1 & " - " & Evaluate(hhhh) 'Result should be True or
False
rstAl.MoveNext
Next k
rstAl.Close
DBS.Close
End Sub

But it does not work... It seems that Evaluate does not understands
variable
names (AAA, AAB, etc.) and the result is always False(?). And on the 3'rd
rule, error msg appeares: runtime error 13 - type mismatch...
Please advice me something how to avoid this problem. Is there any other
way
to read and execut the string formulas?

Evaluate text formula in VBA
 

Thank you very much Bob

"Bob Phillips" wrote:

The problem it is not CCA as a variable, but as part of teh string, as is
the " and the &.

I would use placeholders for the variable and parse it in the code. For
instance, in the database store

AND(<CCA<30,<CCB800)

and then within the code use

x = Range("M1").Value
x = Replace(x, "<CCA", CCA)
x = Replace(x, "<CCB", CCB)
MsgBox Evaluate(x)

I have used an Excel range instead of the recordset item, same principle.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gvaram" wrote in message
...
Please someone help me with Evaluate function. I have some rules written
in
Access table as string with predefined variables in it like:

and(" & CCA & "<30, " & CCB & "800)
and(""" & AAA & """=""" & AAB & """, """ & BBA & """<""" & BBB & """)
and(" & CCA & "<24, """ & BBA & """=""8o6t56565a"")

Then I'm trying to get these rules, supply with the same variables,
Evaluate
them in excel and get True or False results. The code lookes like:

Sub HitRules()
Dim k As Integer
Dim DBS As New ADODB.Connection
Dim rstAl As New ADODB.Recordset
Dim AAA, AAB, BBA, BBB As String
Dim CCA, CCB As Long
Dim hhhh

With DBS
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "D:\TestDB.mdb"
End With
rstAl.Open "Select * from Rules", DBS, adOpenStatic, adLockReadOnly
AAA = "0503"
AAB = "0503"
BBA = "8o6t56-a"
BBB = "8o6t56-b"
CCA = 22
CCB = 1200
For k = 0 To rstAl.RecordCount - 1
hhhh = CStr(rstAl!Rule) 'formulas provided above are stored in
rstAl!Rule
Debug.Print k+1 & " - " & Evaluate(hhhh) 'Result should be True or
False
rstAl.MoveNext
Next k
rstAl.Close
DBS.Close
End Sub

But it does not work... It seems that Evaluate does not understands
variable
names (AAA, AAB, etc.) and the result is always False(?). And on the 3'rd
rule, error msg appeares: runtime error 13 - type mismatch...
Please advice me something how to avoid this problem. Is there any other
way
to read and execut the string formulas?

Evaluate text formula in VBA
 

On May 22, 2:35 pm, Gvaram wrote:
Thank you very much Bob

"Bob Phillips" wrote:
The problem it is not CCA as a variable, but as part of teh string, as is
the " and the &.

I beg to differ with Mr. Phillips original assessment. I believe the
problem was with the string stored in the database. This worked for
me ...

Sub test()
CCA = 22
CCB = 1200
hhhh = "AND(" & CCA & "<30, " & CCB & "800)"
MsgBox Evaluate(hhhh)
End Sub

Note that all I did was add a leading and a trailing double quote to
the 'rule' string.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

Evaluate text formula in VBA
 

One more question Bob - one of the rule is
instr(<AAA,<AAB)0 after parsing it becomes
instr("8o6t56-a","8o6t56-b")=0 and
Evaluate("instr("8o6t56-a","8o6t56-b")=0") returnes compile error

Is there any solution?


"Bob Phillips" wrote:

The problem it is not CCA as a variable, but as part of teh string, as is
the " and the &.

I would use placeholders for the variable and parse it in the code. For
instance, in the database store

AND(<CCA<30,<CCB800)

and then within the code use

x = Range("M1").Value
x = Replace(x, "<CCA", CCA)
x = Replace(x, "<CCB", CCB)
MsgBox Evaluate(x)

I have used an Excel range instead of the recordset item, same principle.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gvaram" wrote in message
...
Please someone help me with Evaluate function. I have some rules written
in
Access table as string with predefined variables in it like:

and(" & CCA & "<30, " & CCB & "800)
and(""" & AAA & """=""" & AAB & """, """ & BBA & """<""" & BBB & """)
and(" & CCA & "<24, """ & BBA & """=""8o6t56565a"")

Then I'm trying to get these rules, supply with the same variables,
Evaluate
them in excel and get True or False results. The code lookes like:

Sub HitRules()
Dim k As Integer
Dim DBS As New ADODB.Connection
Dim rstAl As New ADODB.Recordset
Dim AAA, AAB, BBA, BBB As String
Dim CCA, CCB As Long
Dim hhhh

With DBS
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
.Open "D:\TestDB.mdb"
End With
rstAl.Open "Select * from Rules", DBS, adOpenStatic, adLockReadOnly
AAA = "0503"
AAB = "0503"
BBA = "8o6t56-a"
BBB = "8o6t56-b"
CCA = 22
CCB = 1200
For k = 0 To rstAl.RecordCount - 1
hhhh = CStr(rstAl!Rule) 'formulas provided above are stored in
rstAl!Rule
Debug.Print k+1 & " - " & Evaluate(hhhh) 'Result should be True or
False
rstAl.MoveNext
Next k
rstAl.Close
DBS.Close
End Sub

But it does not work... It seems that Evaluate does not understands
variable
names (AAA, AAB, etc.) and the result is always False(?). And on the 3'rd
rule, error msg appeares: runtime error 13 - type mismatch...
Please advice me something how to avoid this problem. Is there any other
way
to read and execut the string formulas?

Evaluate text formula in VBA
 

Thank you Lavedas, but Bob's solution here is better because it will be
easyer to wright rules like
and([AAA]=[AAB],[BBA]<[BBB]) then
and(""" & AAA & """=""" & AAB & """, """ & BBA & """<""" & BBB & """)

Could you help me with some new problem?
instr([AAA],[AAB])=0 after parsing it becomes
instr("8o6t56-a","8o6t56-b")=0 because AAA and AAB are strings.
Problem is that
Evaluate("instr("8o6t56-a","8o6t56-b")=0") returnes compile error

Is there any solution?


"T Lavedas" wrote:

On May 22, 2:35 pm, Gvaram wrote:
Thank you very much Bob

"Bob Phillips" wrote:
The problem it is not CCA as a variable, but as part of teh string, as is
the " and the &.

I beg to differ with Mr. Phillips original assessment. I believe the
problem was with the string stored in the database. This worked for
me ...

Sub test()
CCA = 22
CCB = 1200
hhhh = "AND(" & CCA & "<30, " & CCB & "800)"
MsgBox Evaluate(hhhh)
End Sub

Note that all I did was add a leading and a trailing double quote to
the 'rule' string.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

Evaluate text formula in VBA
 

On May 22, 4:05 pm, Gvaram wrote:
Thank you Lavedas, but Bob's solution here is better because it will be
easyer to wright rules like
and([AAA]=[AAB],[BBA]<[BBB]) then
and(""" & AAA & """=""" & AAB & """, """ & BBA & """<""" & BBB & """)

Could you help me with some new problem?
instr([AAA],[AAB])=0 after parsing it becomes
instr("8o6t56-a","8o6t56-b")=0 because AAA and AAB are strings.
Problem is that
Evaluate("instr("8o6t56-a","8o6t56-b")=0") returnes compile error

Is there any solution?

The problem is that your definitions of or the substitution operation
needs to provide more double quotes. The string being evaluated needs
to look like this ...

"instr(""8o6t56-a"",""8o6t56-b"")=0"

for the Evaluate to work. I would suggest that the fix be made in the
variable definition ...

AAA = """8o6t56-a"""
AAB = """8o6t56-b"""

All in all what you are trying to accomplish is very confusing to me.
I really haven't a clue how you plan to implement this - all those
definitions and all the substitutions - how will you ever get them all
right? The code just doesn't seem to be the least bit scalable to me,
but then, as I said, I'm confused.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/