VBA to convert a logical text string into a list of permutations

[gchilde1]

I am a reasonably experienced VBA Excel programmer, but this has got me stumped.
A text file contains strings of code such as the following:-
(Code#1/Code#2)+(Code#3/Code#4)

Where + = AND, / = OR

I am trying to write a piece of VBA code that can convert this into possible permutations of the logic in the statement.
The example above would need to return 4 possible lines.
Code#1 Code#3
Code#1 Code#4
Code#2 Code#3
Code#2 Code#4

The text string can come many configurations, including with nested brackets.

Code#1+((Code#2+Code#3)/Code#4)

Which would return 2 possible permutations...
Code#1 Code#2 Code#3
Code#1 Code#4

Any ideas?

 

[gchilde1]

Hi PGC,
This makes sense and the way you have simplified the notation is exactly what i'm trying to do.
It is the removal of the parentheses that is the difficult part.
I'm just looking at the code from mikerickson above. His recursive subroutine is a good idea, im just getting my head round it!

Many thanks for your reply,
GC

 

[mikerickson]

I was going to disagree about the removal of the parenthesis.

Yes, it makes human reading of the expression easier. But as a guide to coding to parse a string to find the primary operator, they are very useful.

 

[pgc01]

Hi

This is another option.

The function gets a string as input, the algebraic expression with the operations "+" and "*" and parentheses.
The output is the expression after removing the parentheses.

To try, write the expression in A1, for ex.

((a*b)*(c#c+yyy*4))*(A$B$C+ZZ)

and B1:

=NoPar(A1)

that results in:

a*b*c#c*A$B$C+a*b*c#c*ZZ+a*b*yyy*4*A$B$C+a*b*yyy*4*ZZ

Remark:

The objective of this code is by no means to be efficient. I wrote in such a way that it was quick to write and test.

Try:

(set the reference to Microsoft VBScript Regular Expressions 5.5)

' PGC201609 - gets rid of parentheses in an algebraic expression with the operations "+" and "*"
' Ex.: (a*b+c)*(d+e) results in a*b*d+a*b*e+c*d+c*e
Function NoPar(ByVal s As String) As String
Dim regex1 As RegExp, regex2 As RegExp
Dim sMult As String
Dim v1 As Variant, v1Arr As Variant, v2 As Variant, v2Arr As Variant

' get rid of useless parentheses
Set regex1 = New RegExp
regex1.Pattern = "(^|[^*])\(([^()]+)\)(?!\*)"

' multiply with parentheses
Set regex2 = New RegExp
regex2.Pattern = "([^\(\)\+\*]+)\*\(([^\(\)]+)\)|\(([^\(\)]+)\)\*([^\(\)\+\*]+)|\(([^\(\)]+)\)\*\(([^\(\)]+)\)"
                                 
s = Replace(s, " ", "")
Do
    Do While regex1.test(s): s = regex1.Replace(s, "$1$2"): Loop
    If Not regex2.test(s) Then Exit Do
    With regex2.Execute(s)(0)
        v1Arr = Split(.submatches(0) & .submatches(2) & .submatches(4), "+")
        v2Arr = Split(.submatches(1) & .submatches(3) & .submatches(5), "+")
        sMult = ""
        For Each v1 In v1Arr
            For Each v2 In v2Arr
                sMult = sMult & "+" & v1 & "*" & v2
            Next v2
        Next v1
        If Mid(s, .FirstIndex + .Length + 1, 1) = "*" Then sMult = "(" & Mid(sMult, 2) & ")" Else sMult = Mid(sMult, 2)
        s = Left(s, .FirstIndex) & sMult & Mid(s, .FirstIndex + .Length + 1)
    End With
Loop
NoPar = s
End Function

Another test, in A1

(a+b)*c*((d+e+f)*(g+h)+i)+(j*(k+l*(m+n+o)))

result

a*c*d*g+a*c*d*h+a*c*e*g+a*c*e*h+a*c*f*g+a*c*f*h+a*c*i+b*c*d*g+b*c*d*h+b*c*e*g+b*c*e*h+b*c*f*g+b*c*f*h+b*c*i+j*k+j*l*m+j*l*n+j*l*o

 

[pgc01]

Some other simple tests:



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-*******2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-***********border-color:#888888;background:#9CF " > </th><th style="border-***********border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-***********border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-***********border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(a+b)*(c+d)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*c+a*d+b*c+b*d</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(Code#1+Code#2)*(Code#3+Code#4)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">Code#1*Code#3+Code#1*Code#4+Code#2*Code#<br>3+Code#2*Code#4</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">Code#1*((Code#2*Code#3)+Code#4)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">Code#1*Code#2*Code#3+Code#1*Code#4</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*c+(a+b+v)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*c+a+b+v</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(a*b+c)*(d+e)</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">a*b*d+a*b*e+c*d+c*e</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">(((j*(k+l*(m+n+o)))))</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-******* ****border-color:#888888; ">j*k+j*l*m+j*l*n+j*l*o</td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td><td style="border-***********border-color:#000000; padding-******0.5em; padding-*****0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-******* ****border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-******1em" > [SimplifyAlgebraicExpression1.xlsm]Sheet1</td></tr></table>

 

[wideboydixon]

Awesome work PGC!! I hope you don't mind that I've taken what you did and added some comments and small changes. Here's my sheet:

Book1
	A	B
1	(Code#1/Code#2)+(Code#3/Code#4)	Code#1 Code#3
2		Code#1 Code#4
3		Code#2 Code#3
4		Code#2 Code#4
5
6	Code#1+((Code#2+Code#3)/Code#4)	Code#1 Code#2 Code#3
7		Code#1 Code#4
Sheet1

Here's the code I took, adapted and commented:

Option Explicit
Public Function GetLogicalPermutations(inputCode As String, outputCell As Range)

Dim removeParentheses As Object
Dim multiplyParentheses As Object
Dim expandedExpression As String
Dim v1 As Variant
Dim v1Arr As Variant
Dim v2 As Variant
Dim v2Arr As Variant
Dim result As Variant
Dim resultArray As Variant

' Normalise the input
inputCode = Replace(inputCode, "+", "*")
inputCode = Replace(inputCode, "/", "+")
inputCode = Replace(inputCode, " ", "")

' Get rid of useless parentheses (i.e. multiplications in parentheses)
Set removeParentheses = CreateObject("VBScript.RegExp")
removeParentheses.Pattern = "(^|[^*])\(([^()]+)\)(?!\*)"

' Multiply with parentheses
Set multiplyParentheses = CreateObject("VBScript.RegExp")
multiplyParentheses.Pattern = "([^\(\)\+\*]+)\*\(([^\(\)]+)\)|\(([^\(\)]+)\)\*([^\(\)\+\*]+)|\(([^\(\)]+)\)\*\(([^\(\)]+)\)"
                                 
Do
    ' Remove those unnecessary parentheses
    Do While removeParentheses.test(inputCode)
        inputCode = removeParentheses.Replace(inputCode, "$1$2")
    Loop
    
    ' If we don't have any parentheses to work on then we're done
    If Not multiplyParentheses.test(inputCode) Then Exit Do
    
    ' Split the expressions and variables inside the parentheses into two lists
    With multiplyParentheses.Execute(inputCode)(0)
        ' Split into arrays using "+" as the delimiter
        v1Arr = Split(.SubMatches(0) & .SubMatches(2) & .SubMatches(4), "+")
        v2Arr = Split(.SubMatches(1) & .SubMatches(3) & .SubMatches(5), "+")
        
        ' Multiply the expressions in parentheses
        expandedExpression = ""
        For Each v1 In v1Arr
            For Each v2 In v2Arr
                expandedExpression = expandedExpression & "+" & v1 & "*" & v2
            Next v2
        Next v1
        
        ' Add in extra parentheses around the expanded expression if necessary
        If Mid(inputCode, .FirstIndex + .Length + 1, 1) = "*" Then expandedExpression = "(" & Mid(expandedExpression, 2) & ")" Else expandedExpression = Mid(expandedExpression, 2)
        
        ' Replace the expanded expression in the output
        inputCode = Left(inputCode, .FirstIndex) & expandedExpression & Mid(inputCode, .FirstIndex + .Length + 1)
    End With
Loop

' Now extract all the results
resultArray = Split(inputCode, "+")
For result = 0 To UBound(resultArray)
    outputCell.Offset(result, 0).Value = Replace(resultArray(result), "*", " ")
Next result

End Function
Public Sub TestLogic()

GetLogicalPermutations Range("A1").Value, Range("B1")
GetLogicalPermutations Range("A6").Value, Range("B6")

End Sub

WBD

 

[pgc01]

I hope you don't mind that I've taken what you did and added some comments and small changes

Not at all. It makes the code easier to read.
And you added the next step, which is to list the summands in separate cells, that in this case are the "permutations" that gchilde1 is looking for.

Just a small remark,

In the case of the "Get rid of useless parentheses" that you added "(i.e. multiplications in parentheses)", that's not always the case.
I am getting rid of redundant parentheses, parentheses that are not needed for the calculation of the expression (they may have another function, for ex. to make the expression easier to read according to some logic).

For ex. in:

a+(b+c)
a+(b*c+d)+e

the parentheses are not needed. In the first case there is just a sum inside and in the second case a multiplication and a sum.

As I said just a small remark.

 

[wideboydixon]

In the case of the "Get rid of useless parentheses" that you added "(i.e. multiplications in parentheses)", that's not always the case.

Yep I saw that; I meant to type "e.g." and not "i.e."

What are two letter between friends?

WBD

 

[pgc01]

Yes, I thought so.
I just wanted to clarify in case someone else was trying to read and understand the code.

 

[gchilde1]

Guys,

Thank you all so much for all your time and effort in answering my question.
This has opened my eyes to many new ideas and VBA techniques.
This was my first post on this forum and im so happy with the response.

Best Regards,
GC

 

[wideboydixon]

Guys,

Thank you all so much for all your time and effort in answering my question.
This has opened my eyes to many new ideas and VBA techniques.
This was my first post on this forum and im so happy with the response.

Best Regards,
GC

You're welcome. Interesting problems always get more attention than the countless INDEX/MATCH questions that occur

WBD