Treat string as condition in VBA

YasserKhalil
PlatinumLounger
Posts: 4858
Joined: 31 Aug 2016, 09:02

Treat string as condition in VBA

Post by YasserKhalil »

Hello everyone

I have a string that is considered a condition and I tried using Evaluate (it works sometimes but not all the time). Have a look at the following example:
I got Error 2015

Code: Select all

Sub Test()
    Dim s As String
    Dim result As Variant
    
    On Error Resume Next 'Turn on error handling
    
    's = "7 > ""five""" 'Invalid comparison - expecting a numeric value
    s = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"
    
    result = Evaluate(s)
    
    If Err.Number <> 0 Then 'Check for error
        Debug.Print "Error: " & Err.Description
        Err.Clear
    Else
        Debug.Print result 'Output: False
    End If
    
    On Error GoTo 0 'Turn off error handling
End Sub
The question posted here too https://stackoverflow.com/questions/762 ... g-evaluate

User avatar
HansV
Administrator
Posts: 77302
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Treat string as condition in VBA

Post by HansV »

Evaluate is intended for Excel formulas, not for VBA expressions. This would work (for whatever it's worth):

Code: Select all

    s = "OR(AND(7>5,10<15),NOT(20=30))"
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4858
Joined: 31 Aug 2016, 09:02

Re: Treat string as condition in VBA

Post by YasserKhalil »

I need to Evaluate VBA expressions as my line in the code looks like that

Code: Select all

If Replace(s, "X", i) Then
In the immediate window the result

Code: Select all

a(1, 21) <= 25 And a(1, 21) <> -1
and I need to Evaluate such a line

User avatar
HansV
Administrator
Posts: 77302
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Treat string as condition in VBA

Post by HansV »

You cannot use Evaluate for that purpose.
Regards,
Hans

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Convert VBA Mathematical Expression to Excel spreadsheet form

Post by DocAElstein »

Hello
This is intended just as a solution idea, it is intended as just a suggestion with a very simplified example, not a full working solution:

The idea is very simple and does not require anything clever: The idea is that you can write some coding to convert VBA mathematical expressions into those that would be very similar to, and work in the same way as, the equivalent mathematical expression in Excel spreadsheet form.
I am pretty sure this can be done. The problem is that to allow for all possible types of mathematical expression the coding would likely be very long and extremely tedious to write. So probably not a solution anyone would be interested in. It would not require any clever coding, just a lot of simple string manipulation coding. (There might also be some very clever short coding to do the same, but i am not considering that here, not yet anyway... )

However, if in a real life practical application you did know the typical forms likely to be in the VBA mathematical expression, then the coding to convert to Excel spreadsheet form would be simplified and might then make it a viable solution.

Just by way of example, here is a solution that is based on an actual expression that would take the form of the example given in the original post,
s = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"
, and more specifically it should solve for things of this general type of form:
( …… And …… ) Or ( ……. ) Or ( …… )

The coding would be required to give out a form such as that suggested here by Hans
s = "OR(AND(7>5,10<15),NOT(20=30))"
, ( and also suggested by BigBen over at scrapoverflow )

This does something close ….

Code: Select all

 Sub FuncIt()
Dim Es As String: Let Es = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"   '    Yasser's example
Debug.Print VBAMathStringToSpreadsheetString(Es)                          '    OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
Debug.Print Evaluate(VBAMathStringToSpreadsheetString(Es))                '    True
End Sub

'  https://eileenslounge.com/viewtopic.php?f=30&t=39654
Function VBAMathStringToSpreadsheetString(ByVal EsVBA As String) As String
 Let EsVBA = Replace(Replace(Replace(EsVBA, "Or ", "OR", , , vbTextCompare), "NOT ", "NOT", , , vbTextCompare), "and ", "AND", , , vbTextCompare)
Dim SptOr() As String
 Let SptOr() = Split(EsVBA, "OR")
Dim OrbIt As Variant, CntOr As Long: Let CntOr = -1
    For Each OrbIt In SptOr()
     Let CntOr = CntOr + 1
    Dim AndIt As Variant, strAnds As String, CntAnd As Long: Let CntAnd = -1
         For Each AndIt In Split(OrbIt, "AND")
          Let strAnds = strAnds & AndIt & ","
         Next AndIt
     Let SptOr(CntOr) = "AND" & Left(strAnds, (Len(strAnds) - 1))
     Let strAnds = "" '
    Next OrbIt
 Let VBAMathStringToSpreadsheetString = "OR(" & Join(SptOr(), ",") & ")"
End Function
It actually converts
((7 > 5) And (10 < 15)) Or (Not (20 = 30))
, to
OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
That is slightly different to the
OR(AND(7>5,10<15),NOT(20=30))
, but I think those last two expressions perform the same in an Excel spreadsheet or in the VBA Evaluate(" ") code line.

_._________

Here is the full workings and explanations of that particular example: https://bit.ly/44XR6J2
But remember this is just intended as an example. If you are likely to have VBA expressions differing from the general form ( …… And …… ) Or ( ……. ) Or ( …… ), then you would likely need to modify or rewrite completely the coding.


Alan
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

YasserKhalil
PlatinumLounger
Posts: 4858
Joined: 31 Aug 2016, 09:02

Re: Treat string as condition in VBA

Post by YasserKhalil »

Thank you very much Mr. Alan
But in fact, it is complicated vba expressions that includes arrays to compare specific string and numeric values.

User avatar
SpeakEasy
4StarLounger
Posts: 453
Joined: 27 Jun 2021, 10:46

Re: Treat string as condition in VBA

Post by SpeakEasy »

>o allow for all possible types of mathematical expression

Probably easier to leverage vbscript's Eval function, e.g

Code: Select all

Public Function vbsEval(strSourceCode As String) As Variant
    With CreateObject("MSScriptControl.ScriptControl")
        .Language = "vbscript"
        .AddObject "myclass", myclass, True
        vbsEval = .Eval(strSourceCode)
    End With
End Function
And then

msgbox vbsEval("((7 > 5) And (10 < 15)) Or (Not (20 = 30))")

will work as expected

One caveat, though. Microsoft have done their best to disable use of vbscript in Office apps (so you may need to re-enable it). They have their reasons.

Now, vbscript doesn't know about any of your application's variables, so you can't in this vanilla form do something like

msgbox vbsEval ("myvariable1 > myvariable2")

but there are some minor changes that can if necessary be implemented to allow this (and provide access to the hosting application's properties and methods), which would allow you to do something like:

msgbox vbsEval("activesheet.name & myvariable.test")

Unfortunately arrays, as required by YasserKhalil , are a bit more troublesome, although still feasible

YasserKhalil
PlatinumLounger
Posts: 4858
Joined: 31 Aug 2016, 09:02

Re: Treat string as condition in VBA

Post by YasserKhalil »

Thank you very much for sharing. All ideas are great.
Finally, I find my way by creating public procedure to be able to deal with all the groups I have.

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Treat string as condition in VBA

Post by DocAElstein »

Hi SpeakEasy. :)
SpeakEasy wrote:
16 May 2023, 09:58
Probably easier to leverage vbscript's Eval function, ...
…That is potentially very interesting to me as I like to play around with Evaluate. Unfortunately I am not getting it. :(
I tried your suggestion in a few versions of Excel 2003, 2007, 2010, 2013 on a diverse collection of different computers, old and new. The result is always the same:
Error because of undeclared variable, myclass
Image

If I go on to declare that variable, for example as Variant, then I still error at the same code line in the function, but this time with a type mismatch error
ImageImage

(If I change the type to Object, then I get a slightly different error : object cannot be nothing
Image
)

What am I missing?

_.___________________________

SpeakEasy wrote:
16 May 2023, 09:58
....Microsoft have done their best to disable use of vbscript in Office apps (so you may need to re-enable it). ....
….I don’t understand what that means? I assume you are not talking about making a reference to a library, since you are using Late Binding?
So what am I missing, again?, .. ( .. as usual, Lol :) ) ...


Alan

Code: Select all

 '     Probably easier to leverage vbscript's Eval function, e.g    https://eileenslounge.com/viewtopic.php?p=307503#p307503
Sub FuncSpeakEasyIt()
Dim Es As String: Let Es = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"   '    Yasser's example
Debug.Print vbsEval(Es)            '
End Sub

Public Function vbsEval(strSourceCode As String) As Variant
    With CreateObject("MSScriptControl.ScriptControl")
        .Language = "vbscript"
        .AddObject "myclass", myclass, True
        vbsEval = .Eval(strSourceCode)
    End With
End Function
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

User avatar
SpeakEasy
4StarLounger
Posts: 453
Joined: 27 Jun 2021, 10:46

Re: Treat string as condition in VBA

Post by SpeakEasy »

oops! Comment out the

.AddObject "myclass", myclass, True

line., it is an artifact of a more advanced version of the code,

User avatar
SpeakEasy
4StarLounger
Posts: 453
Joined: 27 Jun 2021, 10:46

Re: Treat string as condition in VBA

Post by SpeakEasy »

>I assume you are not talking about making a reference to a library

No. I am talking about the fact that MS nowadays think that VBscript is a security risk, and thus block it from working on various systems. It remains unclear to me what the exact criteria are for this blocking, so you may (or may not) be affected. The fix is a registry patch.

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Treat string as condition in VBA

Post by DocAElstein »

SpeakEasy wrote:
20 May 2023, 17:29
oops! Comment out the .AddObject "myclass", myclass, True
OK, thanks , - that does the trick in all my Excel versions and computers.
This VB script evaluate thing is certainly something am interested to look into. (I will take an initial guess that using it in VBA like we are here, it will be limited to evaluating mathematical expressions, and that works because most likely mathematical expressions look syntaxly similar or identical in VB to such mathematical expressions in VBA. Hence this way lends itself nicely to a workaround for the original Thread requirement
My guess is that the VB script evaluate thing is for evaluating stuff in VB , so it won’t have a clue what worksheet things are. If I understand correctly, one of the main things the VBA Evaluate is for is doing worksheet things within VBA, ( although I have hacked it into doing some interesting other things, usually to most people's great disgust, Lol !))

Do you or anyone have any references to any good older documentation on this VB script evaluate thing. I stress older as I have in the past found some much older VB documentation very good for things in VBA. The newer documentation I find personally less good on average, but the newer stuff tends to come up more on a search and the good older stuff is sometimes difficult to find. I think maybe some of this older documentation stuff stems maybe from the time that some attempt was still made from the makers of software to document it properly. The newer stuff is often left to us to find out for ourselves, and sometime newer Blog authors know how to get their stuff viewed, which causes some better older stuff to get lost down the google search produced list.

Alan.

( Edit, P.S. a quick thought - Maybe that some of that hacking of mine works as something in the VBA Evaluate does some things like the VB Evaluate , still, by coincidence, as perhaps something in the inner wirings of the VBA Evaluate is left over by chance as the VBA Evaluate back end coding might have originated in some of the older VB Evaluate back end coding? - Just a wild thought , one of those wild thoughts of mine which 1 in 10 times is sometimes correct…
I guess one thing I am thinking of is my famous
Evaluate “YouNameIt”
hack. That will run a macro somehow detached, or on an obscure dependence tree, allowing things to work that shouldn’t, and that may have a parallel to the VB Evaluate thing running a function, which is the thing that comes up most on a quick initial search for what the VB Evaluate is about…
)
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

User avatar
SpeakEasy
4StarLounger
Posts: 453
Joined: 27 Jun 2021, 10:46

Re: Treat string as condition in VBA

Post by SpeakEasy »

>VB script
Point of pedantry: VBscript, not VB Script. More specifically what we are actually leveraging is the Microsoft Script Control (which supports both javascript and vbscript)

>Do you or anyone have any references to any good older documentation
A starting point might be to add MSScriptControl as a reference (instead of the late binding I am doing here), and then you can examine all the methods and properties in the IDE.

>won’t have a clue what worksheet things are
As I mentioned, there are some "some minor changes that ... provide access to the hosting application's properties and methods". So yes, we can get it to recognize worksheets, and worksheet objects such as ranges and cells if we want. It is as simple as adding

.AddObject "app", Application, True

after the .Language line, i.e

Code: Select all

Public Function vbsEval(strSourceCode As String) As Variant
    With CreateObject("MSScriptControl.ScriptControl")
        .Language = "vbscript"
         .AddObject "app", Application, True
        vbsEval = .Eval(strSourceCode)
    End With
End Function
Then the following will work as expected:

MsgBox vbsEval("activesheet.name")
Range("A1") = "Example"
MsgBox vbsEval("range(""A1"")")


(there's a certain amount of COM magic that makes this work)

YasserKhalil
PlatinumLounger
Posts: 4858
Joined: 31 Aug 2016, 09:02

Re: Treat string as condition in VBA

Post by YasserKhalil »

I am trying to test the previous code of SpeakEasy but I got an error `a script engine for the specified language can not be created`
Although I select the reference Microsoft Script Control . I am using Windows 10 64Bit, Office 365 32Bit.

User avatar
SpeakEasy
4StarLounger
Posts: 453
Joined: 27 Jun 2021, 10:46

Re: Treat string as condition in VBA

Post by SpeakEasy »

>I got an error `a script engine for the specified language can not be created`

As I said in my post: Microsoft have done their best to disable use of vbscript in Office apps

The following Registry merge file should address this:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Common\COM Compatibility\{B54F3741-5B07-11cf-A4B0-00AA004A55E8}]
"ActivationFilterOverride"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Common\COM Compatibility\{B54F3741-5B07-11cf-A4B0-00AA004A55E8}]
"ActivationFilterOverride"=dword:00000001

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Treat string as condition in VBA

Post by DocAElstein »

YasserKhalil wrote:
22 May 2023, 15:51
got an error ....
Although I select the reference Microsoft Script Control . ...
Speak Easy is using Late Binding so the referrence to Microsoft Script Control should not be necerssary.
_._____
YasserKhalil wrote:
22 May 2023, 15:51
got an error `a script engine for the specified language can not be created`
.... I am using Windows 10 64Bit, Office 365 32Bit.
Yep as he said and said ... Microsoft have done their best to disable use of vbscript in Office apps .... (so you may need to re-enable it) .... I am talking about the fact that MS nowadays think that VBscript is a security risk, and thus block it from working on various systems. It remains unclear to me what the exact criteria are for this blocking, so you may (or may not) be affected. The fix is a registry patch......

- You have there a fairly new Office version, and as often with new microsoft things, you may need some registry fix to make something work like it used to, ( or stop it being broken )

Edit - I see SpeakEasy has answered and said that again
Last edited by DocAElstein on 23 May 2023, 10:00, edited 1 time in total.
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Treat string as condition in VBA

Post by DocAElstein »

This is very interesting stuff. Not sure yet for what it might be useful, but good to have in my mind, when for example I play around with Evaluate things, or any other unusual and novel solutions..
This following is all working for me in Excel 2003 – 2013, on various operating systems, up to and including windows 11

Code: Select all

 '                                          Probably easier to leverage vbscript's Eval function, e.g    https://eileenslounge.com/viewtopic.php?p=307503#p307503
Sub FuncSpeakEasyIt()
Dim Es As String: Let Es = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"   '    Yasser's example
Debug.Print vbsEval(Es) '
Debug.Print vbsEvalapp("workshEets.itEm(1).evaLuate(""="" & ""1+1"")")
Debug.Print vbsEvalapp("workshEets.itEm(1).evaLuate(""="" & ""1+1"" & ""1+1"")")
Debug.Print vbsEvalapp("wOrksheets.item(1).eValuaTe(""="" & ""1+1"" & "" & "" & ""1+1"")")
Debug.Print vbsEvalapp("worksHeets.iTem(1).evaluate(""="" & ""1+1-1"" & "" & "" & """""" and """""" & ""  & "" & ""1+1"")")
End Sub











'  https://eileenslounge.com/viewtopic.php?p=307628#p307628
Public Function vbsEval(strSourceCode As String) As Variant
    With CreateObject("MSScriptControl.ScriptControl")
     .Language = "vbscript"
     '.AddObject "myclass", myclass, True
     vbsEval = .eval(strSourceCode)
    End With
End Function
Public Function vbsEvalapp(strSourceCode As String) As Variant
    With CreateObject("MSScriptControl.ScriptControl")
        .Language = "vbscript"
         .AddObject "app", Application, True '  https://eileenslounge.com/viewtopic.php?p=307707#p307707
        vbsEvalapp = .eval(strSourceCode)
    End With
End Function 
That gives me the expected result in the Immediate Window of

True
2
13
22
1 and 2
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Treat string as condition in VBA

Post by DocAElstein »

SpeakEasy wrote:
22 May 2023, 14:40
>VB script
Point of pedantry: VBscript, not VB Script. More specifically what we are actually leveraging is the Microsoft Script Control (which supports both javascript and vbscript)
I think its helpful to be pedantic about these VB… things, for a couple of reasons
_ more likely to find something in a search in the overcrowded internet
_ These VB … things seem to be in a bit of a mess as regards definitions of them. I have even read people saying the definition is changed later with retrospect to try and make some sense of it all. It seems that anything VB and object orientated is a pretty vague and messed up subject, half the time no one knows what VB … thing means what ..
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

User avatar
DocAElstein
4StarLounger
Posts: 422
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: Treat string as condition in VBA

Post by DocAElstein »

SpeakEasy wrote:
23 May 2023, 08:12
The following Registry merge file should address this:
I don't know what any of that is about, and I do realise that i probably don't need the fix in my older systems, but I thought it might be intersting to see if I have anything like that. .... So far I never see any ClickToRun at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\?
Or is the point that something like that should be added, ( if I did need it ) ?
What is the point in posting, if you can’t post it beautifully like wot Alan does … William Sheikh","Spear, 1601

User avatar
HansV
Administrator
Posts: 77302
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Treat string as condition in VBA

Post by HansV »

These settings are for recent versions of Office that use the so-called Click-to-Run technology. I have no idea whether equivalent settings would work in older versions.
Regards,
Hans