Code:
' https://eileenslounge.com/viewtopic.php?f=30&t=39654
Sub EvaluateVBAStringConversionConfusions()
Rem 0 some initial investigating into what we can and we cannot get away with in terms of having the final Excel spreadsheet form slightly different to the actual required one. Specifically we are looking at the effects of extra spaces in the Excel spreadsheet form. This is because the VBA string code line differs in some places to the Excel spreadsheet form in that it has some extra spaces.
Dim EsVBA As String, EsExcel As String
Let EsVBA = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"
Let EsExcel = "OR(AND(7>5,10<15),NOT(20=30))"
Dim Res As Variant: Let Res = Evaluate(EsExcel) ' Wahr - OK
Let EsExcel = "OR(AND( 7 > 5,10<15) ,NOT( 20 = 30))"
Let Res = Evaluate(EsExcel) ' Wahr - OK
Let EsExcel = "OR(AND( 7 > 5,10<15) ,AND(NOT( 20 = 30)))"
Let Res = Evaluate(EsExcel) ' Wahr - OK
Let EsExcel = "OR(AND (7>5,10<15),NOT(20=30))"
Let Res = Evaluate(EsExcel) ' Error ..... we need to get rid of spacers after expressions as Excel does not like those
Rem 1 we need to get rid of spacers after expressions as Excel does not seem to like those
Dim EsVBAinExcel As String
Let EsVBAinExcel = Replace(EsVBA, "Or ", "OR", 1, -1, vbTextCompare) ' Note - TextCompare is important to make it not case sensitive
Let EsVBAinExcel = Replace(EsVBAinExcel, "NOT ", "NOT", 1, -1, vbTextCompare)
Let EsVBAinExcel = Replace(EsVBAinExcel, "and ", "AND", , , vbTextCompare)
' Or
Let EsVBAinExcel = Replace(Replace(Replace(EsVBA, "Or ", "OR", , , vbTextCompare), "NOT ", "NOT", , , vbTextCompare), "and ", "AND", , , vbTextCompare)
Rem 2 Do the Or conversion
Dim SptOr() As String
Let SptOr() = Split(EsVBAinExcel, "OR", -1, vbBinaryCompare)
' Or
Let SptOr() = Split(EsVBAinExcel, "OR") ' Binary compare is excact and it is default
'2a) Convert And s in each Or
Dim OrbIt As Variant, CntOr As Long: Let CntOr = -1 ' -1 allows us to tart at the indicie of zero when we increase by 1 at the start of each loop
For Each OrbIt In SptOr()
Let CntOr = CntOr + 1
Dim SptAnd() As String
Let SptAnd() = Split(OrbIt, "AND")
Dim AndIt As Variant, strAnds As String, CntAnd As Long: Let CntAnd = -1 ' -1 allows us to tart at the indicie of zero when we increase by 1 at the start of each loop
For Each AndIt In SptAnd()
Let strAnds = strAnds & AndIt & ","
Next AndIt
Let strAnds = Left(strAnds, (Len(strAnds) - 1)) ' take off last comma
Let strAnds = "AND" & strAnds
Let SptOr(CntOr) = strAnds ' Change the OrbIt to the form that will work in Excel spreadsheet
Let strAnds = "" ' Empty strAnds so that I can use the variable again in the next loop
Next OrbIt
' '2b) ' build up the final or
Let EsVBAinExcel = Join(SptOr(), ",")
Let EsVBAinExcel = "OR(" & EsVBAinExcel & ")"
' At this point we have OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
' , which is close enought to what we want OR(AND (7>5,10<15),NOT(20=30))
Rem 3
Debug.Print EsVBAinExcel
Let Res = Evaluate(EsVBAinExcel)
End Sub
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
Reply With Quote
Bookmarks