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
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
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 & ","
Let SptOr(CntOr) = "AND" & Left(strAnds, (Len(strAnds) - 1))
Let strAnds = "" '
Let VBAMathStringToSpreadsheetString = "OR(" & Join(SptOr(), ",") & ")"
It actually converts
((7 > 5) And (10 < 15)) Or (Not (20 = 30))
OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
That is slightly different to the
, but I think those last two expressions perform the same in an Excel spreadsheet or in the VBA Evaluate(" ")
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.