 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Evaluate 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? 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,<CCB>>800) 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.  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Please someone help me with Evaluate function. I have some rules written > in [quoted text clipped - 45 lines] > way > to read and execut the string formulas? Thank you very much Bob > The problem it is not CCA as a variable, but as part of teh string, as is > the " and the &. [quoted text clipped - 62 lines] > > way > > to read and execut the string formulas? > Thank you very much Bob > > > 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/ 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? > > Thank you very much Bob > > [quoted text clipped - 18 lines] > =========== > http://members.cox.net/tglbatch/wsh/ > Thank you Lavedas, but Bob's solution here is better because it will be > easyer to wright rules like [quoted text clipped - 8 lines] > > 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/ 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? > The problem it is not CCA as a variable, but as part of teh string, as is > the " and the &. [quoted text clipped - 62 lines] > > way > > to read and execut the string formulas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.