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)

10598

[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 code10599

[snb]

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

[Rhadamanthys]

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

Hey snb, shows up the same even if I resize. Try it out. This is what the original cell has
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)

[Rhadamanthys]

I take that back, the Not Valid Cond 1 & 2 line wasnt fully displayed which it does upon widening the column
Looking at the result, the render is different (and still acceptable), the only problem is the indents are not being applied throughout. I hope to replace the brackets with indents. If you look at the NOT Cond 3
AND Cond 4 or 5
theyre at the same level as the start of the equation......

If I can get the view in the pic, its also acceptable. you tihnk your formula can do it snb?10600

[Rhadamanthys]

Gents
Just wanted to let you know I sorted this thing out in the end
Paul`s UDF was good. Its the revised rule that I put for the second UDF that screwed things up
"(" was only increasing detent spacing and ")" was decreasing it
I modified the code to make it do a carriage return and increase spacing like it was on the first UDF and it worked like a charm
In both cases the end result had a lot of extra empty lines, but after running the UDF, I copied, pasted as values and then replaced chr10 spaces chr10 with chr10 and the end result is great
Thanks again for all your help

[Paul_Hossler]

I've been playing with the second case a little more.

The issue is with (I think) your rules:



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


For

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)


CurrentIndentLevel (CIL) = 0 = no spaces

The 'dot' is replaced with a CR to end the line and there 3 spaces at the beginning of the next

The [ is removed and the 3 spaces remain, but the CIL = 3 + 3 = 6

Condition_1 is added after the 3 spaces

The space after Condition_1 is replaced with a CR and the CIL is now 6 spaces.

etc.

Still looking at it




Option Explicit
Function ReformatBoolean2a(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)

'When I run into ".", replace it with a carriage return + (previously established tab) + 3 spaces
Case "."
iNumSpaces = iNumSpaces + 3
sNewString2 = sNewString2 & "~" & Left("######################################", iNumSpaces)

'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 a space, replace it with a carriage return + (previously established tab)
Case " "
sNewString2 = sNewString2 & "~" & Left("######################################", iNumSpaces)

Case Else
sNewString2 = sNewString2 & Mid(sNewString, iChar, 1)
End Select

Next iChar

sNewString2 = Trim(sNewString2)

Do While (Right(sNewString2, 1) = "~") Or (Right(sNewString2, 1) = " ")
sNewString2 = Left(sNewString2, Len(sNewString2) - 1)
Loop

sNewString2 = Trim(sNewString2)

ReformatBoolean2a = sNewString2

End Function



I did a 'test' version to play with the logic. Tildas for CR, and # for space just to see better

[Rhadamanthys]

Hi Paul
yeah I noticed that yesterday. It gets fixed if we replace the brackets with a carriage return + tab count + 3 spaces. Instead of just increasing the count

[Paul_Hossler]

glad you got it sorted out

I 'think' I was kind of maybe possibly getting a little close to where you wanted to go

Paul

PS -- don't forget to make it [Solved] using thread tools

[Aussiebear]

Shouldn't the last AND and the subsequent Conditions 6 & 7(indented 3 spaces) line up with the first condition rather than with the tdt2s statement?

[Paul_Hossler]

I was in the process of experiment in that area (until the OP said he was good). I was replacing the '.' with a <cr>, but not changing indent counter. The '.' has no decrementing matching char, so it looked 'unbalanced' to me

Paul