VBA Code to parse boolean equations

[Rhadamanthys]

Hi guys
I'm new to the forum and can use some help coding the following in VBA so a macro can convert the data in all cells

I have an excel spreadsheet that has Boolean expressions in text form like so:
AND(AND(Condition 1, NOT(Condition 2)), OR(Condition 3, Condition 4, Condition 5, Condition 6, Condition 7, Condition 8, Condition 9))

I need this to be displayed in an easier to read format (spaced or tabbed) to get a view like this
AND

AND

Condition 1

NOT

Condition 2

OR

Condition 3

Condition 4
Condition 5
Condition 6
Condition 7
Condition 8
Condition 9

So basically I need a routine that looks at the contents of the cell from left to right and uses the brackets and commas to parse the data based on these rules

1- When I run into the first “(“, replace it with (carriage return) + (3 spaces)
2- IF I run into the next “(“, replace it with (carriage return) + (previously established tab) + (3 spaces)
3- If I run into a “)”, (previously established tab) - (3 spaces) – reduces the space count for tab only
4- If I run into a “,”,replace it with (carriage return) + (previously established tab)

Can somebody please help me code this in VBA....?

[Jan Karel Pieterse]

What about:

http://excelformulabeautifier.com/

[Rhadamanthys]

Thats exactly what I'm looking for (but in VBA code)
I need to have the code in a macro since the table that the routine needs to run on is updated regularly......

[Paul_Hossler]

I like to use a user defined function for things like that

Option Explicit

'1- When I run into the first “(“, replace it with (carriage return) + (3 spaces)
'2- IF I run into the next “(“, replace it with (carriage return) + (previously established tab) + (3 spaces)
'3- If I run into a “)”, (previously established tab) - (3 spaces) – reduces the space count for tab only
'4- If I run into a “,”,replace it with (carriage return) + (previously established tab)

Function ReformatBoolean(s As String) As String
    Dim iNumSpaces As Long, iChar As Long
    Dim sNewString As String, sNewString2 As String

    iNumSpaces = 0
    sNewString = s
    sNewString2 = vbNullString
    
    sNewString = Replace(sNewString, " (", "(")
    sNewString = Replace(sNewString, "( ", "(")
    sNewString = Replace(sNewString, " )", ")")
    sNewString = Replace(sNewString, ") ", ")")
    sNewString = Replace(sNewString, " ,", ",")
    sNewString = Replace(sNewString, ", ", ",")
    
    
    For iChar = 1 To Len(sNewString)
        Select Case Mid(sNewString, iChar, 1)
            
            Case "("
                iNumSpaces = iNumSpaces + 3
                sNewString2 = sNewString2 & vbLf & Space(iNumSpaces)
            
            Case ")"
                If iNumSpaces > 3 Then iNumSpaces = iNumSpaces - 3
                sNewString2 = sNewString2 & vbLf & Space(iNumSpaces)
            
            Case ","
                sNewString2 = sNewString2 & vbLf & Space(iNumSpaces)
            
            Case Else
                sNewString2 = sNewString2 & Mid(sNewString, iChar, 1)
        End Select
    Next iChar
    sNewString2 = Trim(sNewString2)

    Do While (Right(sNewString2, 1) = vbLf) Or (Right(sNewString2, 1) = " ")
        sNewString2 = Left(sNewString2, Len(sNewString2) - 1)
    Loop
    
    sNewString2 = Trim(sNewString2)
    
ReformatBoolean = sNewString2

End Function

Paul

[Rhadamanthys]

Paul,
That looks like what I need
Excuse the ignorance, but how to I insert this into a macro? I dont see any cells or columns being referenced....
So if I wanted this routine to be run on the contents of column P in my spreadsheet (or have the contents of P reformatted with these rules in column Q) - how should I do this...?
The thing is it needs to be as part of a macro, since the spreadsheet updates regularly and the macro needs to be run on the spreadsheet anyway to parse other data - I just wanted to add this to that macro....

[Rhadamanthys]

Nevermind Paul
I got it, so the userdefined makes this a new fomula in excel. I just might be able to make this work
Thanks man - youre a life saver

[Paul_Hossler]

Also, the code could be incorporated into a sub that reformated the cells' values, and then there'd be no worksheet formulas


Paul

[Rhadamanthys]

Paul your solution worked to perfection
I appled the UDF on one spreadhseet and it worked great. I need to apply the same formatting on another dataset (which I assumed wrongly had the same type of equation formatting) however its totally different
Can you please please help me out with a UDF for this one too....?

:= tdt2s.{[(Condition_1 AND
Condition_2) OR
(Condition_3 AND
Condition_4) OR
(Condition_5 AND
Condition_6)] AND
NOT.(Condition_7 OR
Condition_8)} AND
Condition_9;

[Paul_Hossler]

Sure, but it'd be helpful if you would provide the revised rules similar to

1- When I run into the first “(“, replace it with (carriage return) + (3 spaces)
2- IF I run into the next “(“, replace it with (carriage return) + (previously established tab) + (3 spaces)
3- If I run into a “)”, (previously established tab) - (3 spaces) – reduces the space count for tab only
4- If I run into a “,”,replace it with (carriage return) + (previously established tab)


I see braces and :='s and NOT. 's etc.


You looking for one smarter UDF or 2 separate?

Me, I'd go with a single one that could handle both cases

Paul

[Rhadamanthys]

I've been trying to wrap my head around this new format, and its really hard to put rules into words (to make the end result look like the other UDF)

Making a single UDF that comes up with the same format will be really difficult (I'm guessing since I cant even put it into words)
The problem is the operand in the second case comes at the end of the condition (in the first case it appears before the brackets)
I guess a different display would still help make it more readable

The following set of rules should work for this type of equation
When I run into ".", replace it with a carriage return + (previously established tab) + 3 spaces
When I run into a space, replace it with a carriage return + (previously established tab)
Each occurrence of "(", "[" or "{" is removed and increases the previously established tab count by 3 spaces
Each occurrence of ")", "]" or "}" is removed and decreases the previously established tab count by 3 spaces

[Paul_Hossler]

You might want to look at the rules and see if you want changes

Option Explicit
 
 
Function ReformatBoolean2(s As String) As String
    Dim iNumSpaces As Long, iChar As Long
    Dim sNewString As String, sNewString2 As String
     
    iNumSpaces = 0
    sNewString = s
    sNewString2 = vbNullString
     
    
    Call pvtTrim(sNewString, ".()[]{}")
     
     
    For iChar = 1 To Len(sNewString)
        Select Case Mid(sNewString, iChar, 1)
             
'Each occurrence of "(", "[" or "{" is removed and increases the previously established tab count by 3 spaces
        Case "(", "[", "{"
            iNumSpaces = iNumSpaces + 3
'Each occurrence of ")", "]" or "}" is removed and decreases the previously established tab count by 3 spaces)
        Case ")", "]", "}"
            If iNumSpaces > 3 Then iNumSpaces = iNumSpaces - 3
             
'When I run into ".", replace it with a carriage return + (previously established tab) + 3 spaces
        Case "."
            iNumSpaces = iNumSpaces + 3
            sNewString2 = sNewString2 & vbLf & Space(iNumSpaces)
        
'When I run into a space, replace it with a carriage return + (previously established tab)
        Case " "
            sNewString2 = sNewString2 & vbLf & Space(iNumSpaces)
             
        Case Else
            sNewString2 = sNewString2 & Mid(sNewString, iChar, 1)
        End Select
    
    Next iChar
    
    sNewString2 = Trim(sNewString2)
     
    Do While (Right(sNewString2, 1) = vbLf) Or (Right(sNewString2, 1) = " ")
        sNewString2 = Left(sNewString2, Len(sNewString2) - 1)
    Loop
     
    sNewString2 = Trim(sNewString2)
     
    ReformatBoolean2 = sNewString2
     
End Function

'sub to remove spaces before and after char
'not elegant, but easy to maintain
Private Sub pvtTrim(ByRef s As String, c As String)
    Dim i As Long
    Dim c2 As String
    
    For i = 1 To Len(c)
        
        c2 = Mid(c, i, 1)
        
        Do While InStr(s, "  " & c2) > 0
            s = Replace(s, "  " & c2, c2)
        Loop
        Do While InStr(s, c2 & "  ") > 0
            s = Replace(s, c2 & "  ", c2)
        Loop
    
    Next i
End Sub

Paul

[Rhadamanthys]

Paul

I tried the UDF, but there seems to be something a wee bit off
See how it rendered the following equation
tdt400ms.[Condition_1 OR Condition_2 OR (NOT Condition_3 AND ((Condition_4 OR Condition_5) OR (NOT Valid-Condition_1 AND NOT Valid-Condition_2)))] AND (Condition_6 OR Condition_7)

Render.jpg

[snb]

or ?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," OR ",CHAR(10)&"OR"&CHAR(10))," AND NOT ",CHAR(10)&"AND NOT"&CHAR(10))," AND ",CHAR(10)&"AND"&CHAR(10)),"[",CHAR(10)&"[")

[Rhadamanthys]

Hi snb, that puts a carriage return after each variable or operand, without the tabs that group the equation based on brackets, wont help understand it at all....

[snb]

I don't think it's very complicated to substitute all brackets with as many 'indents' as desired.

[Rhadamanthys]

I tried every which way myself really, but I dont know how to manage the "count" of indents factor - since every bracket left to right should increase the indent and each subsequent one reduces it
If you can think of a way to factor this in, it would help me out. Thus far the closest I`ve gotten is the UDF option that Paul gave

[snb]

In my view this gives a very readable result:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," OR (",CHAR(10)&"OR"&CHAR(10)&" (")," AND (",CHAR(10)&"AND"&CHAR(10)&" ("),"[",CHAR(10)&"["),"]",CHAR(10)&"]")

[Aussiebear]

I tried every which way myself really, but I dont know how to manage the "count" of indents factor - since every bracket left to right should increase the indent and each subsequent one reduces it
If you can think of a way to factor this in, it would help me out. Thus far the closest I`ve gotten is the UDF option that Paul gave

Should this not be read right to left rather than left to right?

[Rhadamanthys]

Hi guys
snb, your fomula is inserting indents, but the view is not exaclty what I`m looking for
Maybe the attached picture will show you what I mean
As you can see, Paul`s UDF is the closest I`m getting to what I want (in reality I would like the formatting of the first UDF, but I have no clue how to even word the rules)
The spacing is very slightly off. Paul if youre out there, please help correct the codeRender.jpg

[snb]

You didn't resize the column enough to show the result of my formula properly.