Notes tests, string, manipulation of text files and string manipulations

**DocAElstein** · 10-02-2022, 06:21 PM

hhhhkh

**DocAElstein** · 05-13-2023, 01:49 PM

In support of this main forum post
https://eileenslounge.com/viewtopic.php?f=30&t=39654

Convert VBA Mathematical Expression to Excel spreadsheet form

Proposed solution type.
I would describe this solution type as very unclever. Its simple but tedious and complicated. Its just using simple string manipulation to change a VBA code mathematical string into the equivalent in Excel spreadsheet form.
The specific example given here would be a very limited solution only applicable to some specific forms, such as in the OPs given example:
s = "((7 > 5) And (10 < 15)) Or (Not (20 = 30))"
Here is the Excel spreadsheet form given by Hans
s = "OR(AND(7>5,10<15),NOT(20=30))"
, ( and also BigBen over at scrapoverflow
Evaluate("OR(AND(7>5,10<15),NOT(20=30))")
)

Here a full solution rambling development, then later a simplified function

This example type solution will be limited to a general form that will have one or more Ors applying to some mathematical logic that my include some Ands. This sort of form
( …… And …… ) Or ( ……. ) Or ( …… )
So similar to the OPs
((7 > 5) And (10 < 15)) Or (Not (20 = 30))

Coding description ( Coding here: https://www.excelfox.com/forum/showt...ll=1#post20038 )
In Rem 0 is 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.
We find that in some situations the extra spaces have no effect on the final calculation, so that is helpful in simplifying the conversion changes necessary.
The conclusion is then, we need to get rid of spacers after expressions as Excel does not seem to like those, so that is done in Rem 1

Rem 2
The crux of this limited solution is to first split by the Ors in the VBA string code line.
'2a) we then examine each of the Or bits, by that I mean the Or elements , or in other words Each of the OrbIts are the bracket bits from the general form
( …… And …… ) Or ( ……. ) Or ( …… )
In this limited solution example, we check for convert any Ands to the Excel spreadsheet form. The converted form is then used to overwrite the original VBA code line form.

Finally, in '2b) , the modified Or bits are reJoined

In the example we end up with
OR(AND((7 > 5) ,(10 < 15)) ,AND(NOT(20 = 30)))
, which is close enough to the actual required
OR(AND (7>5,10<15),NOT(20=30))
, such that in Rem 3 , it gives the correct result in Evaluate(" ")

That’s it!

Full coding and simplified function form in next post

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**DocAElstein** · 05-13-2023, 02:06 PM

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