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.
J
ust 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
