Dynamic variable state combinations

This is an interesting problem and it lends itself to a recursive programming approach.

The code below uses a couple of procedures (ProcessArray and AddCodes) which call each other repeatedly while an array of possible combinations reduces in size until it is only one variable big. At this point, the line of values is written to the spreadsheet.

To keep things simpler for me, I’ve hard coded a set of variables (V1 to V5) and their possible states (S1 – Sn) . In the example, variable 1 has 4 states, variable 2 has 3, variable 3 has 4, variable 4 has 4 and finally variable 5 has five. This should result in 4x3x4x4x5 = 960 combinations.

You will need to adapt the code to fit in with the plans that you have to ask the user to define the number of variables and their possible states and get the positioning on the sheet the way that you want it.

I hope that this is useful.

Dim VariableCount As Integer
Dim MaxStates As Integer
Dim CurrentRow As Integer
Dim PrecedingCells()


Sub Test()
CurrentRow = 0
CurrentArrayColumn = 1
Dim SubArray()
VariableCount = 5
MaxStates = 5
Dim VariableStateArray()
ReDim SubArray(VariableCount - 2, MaxStates - 1)
ReDim PrecedingCells(VariableCount - 2)

ReDim VariableStateArray(VariableCount - 1, MaxStates - 1)

VariableStateArray(0, 0) = "V1S1"
VariableStateArray(0, 1) = "V1S2"
VariableStateArray(0, 2) = "V1S3"
VariableStateArray(0, 3) = "V1S4"
VariableStateArray(1, 0) = "V2S1"
VariableStateArray(1, 1) = "V2S2"
VariableStateArray(1, 2) = "V2S3"
VariableStateArray(2, 0) = "V3S1"
VariableStateArray(2, 1) = "V3S2"
VariableStateArray(2, 2) = "V3S3"
VariableStateArray(2, 3) = "V3S4"
VariableStateArray(3, 0) = "V4S1"
VariableStateArray(3, 1) = "V4S2"
VariableStateArray(3, 2) = "V4S3"
VariableStateArray(3, 3) = "V4S4"
VariableStateArray(4, 0) = "V5S1"
VariableStateArray(4, 1) = "V5S2"
VariableStateArray(4, 2) = "V5S3"
VariableStateArray(4, 3) = "V5S4"
VariableStateArray(4, 4) = "V5S5"

Call ProcessArray(VariableStateArray)
End Sub


Sub ProcessArray(WorkingVariableStateArray As Variant)
StateCount = 0
For M = 0 To UBound(WorkingVariableStateArray, 2)
    If WorkingVariableStateArray(N, M) <> "" Then StateCount = StateCount + 1
Next M
Dim SubArray()
Call ReduceArray(WorkingVariableStateArray, SubArray, N)
For M = 0 To StateCount - 1
    PrecedingCells(VariableCount - UBound(WorkingVariableStateArray, 1) - 1) = WorkingVariableStateArray(N, M)
    Call AddCells(PrecedingCells, SubArray)
Next M
End Sub




Sub AddCells(ByVal PrecedingCells As Variant, ByVal SmallerArray As Variant)
    If UBound(SmallerArray, 1) <> 0 Then
        PrecedingCells(UBound(PrecedingCells) - UBound(SmallerArray, 1) + 1) = SmallerArray(0, Z)
        Call ProcessArray(SmallerArray)
    Else
        For B = 0 To UBound(SmallerArray, 2)
            CurrentRow = CurrentRow + 1
            For A = 0 To UBound(PrecedingCells)
                Cells(CurrentRow, A + 1) = PrecedingCells(A)
            Next A
            Cells(CurrentRow, UBound(PrecedingCells) + 2) = SmallerArray(0, B)
        Next B
    End If
End Sub


Sub ReduceArray(ByVal OriginalArray As Variant, ByRef NewArray As Variant, ByVal ElementToIgnore As Integer)
    ReDim NewArray(UBound(OriginalArray, 1) - 1, UBound(OriginalArray, 2))
    CurrentNewArrayRow = 0
    For X = 0 To UBound(OriginalArray, 1)
        If X <> ElementToIgnore Then
            CurrentNewArrayRow = CurrentNewArrayRow + 1
            For Y = 0 To UBound(OriginalArray, 2)
                NewArray(CurrentNewArrayRow - 1, Y) = OriginalArray(X, Y)
            Next Y
        End If
    Next X
End Sub

Thanks Martin,

This is absolutely brilliant... I think it would have taken me about a year to figure this out alone. You are a star!!!

Martin,

I can't thank you enough for this program. I wonder if I can ask you for one more favour... in your current program variable 5 has the largest number of states. I tried running the same program, slightly modified to have 2, 4 and 3 states for 3 variables only. In this example I get the following output:

V1S1 V2S1 V3S1
V1S1 V2S1 V3S2
V1S1 V2S1 V3S3
V1S1 V2S1
V1S1 V2S2 V3S1
V1S1 V2S2 V3S2
V1S1 V2S2 V3S3
V1S1 V2S2
V1S1 V2S3 V3S1
V1S1 V2S3 V3S2
V1S1 V2S3 V3S3
V1S1 V2S3
V1S1 V2S4 V3S1
V1S1 V2S4 V3S2
V1S1 V2S4 V3S3
V1S1 V2S4
V1S2 V2S1 V3S1
V1S2 V2S1 V3S2
V1S2 V2S1 V3S3
V1S2 V2S1
V1S2 V2S2 V3S1
V1S2 V2S2 V3S2
V1S2 V2S2 V3S3
V1S2 V2S2
V1S2 V2S3 V3S1
V1S2 V2S3 V3S2
V1S2 V2S3 V3S3
V1S2 V2S3
V1S2 V2S4 V3S1
V1S2 V2S4 V3S2
V1S2 V2S4 V3S3
V1S2 V2S4

I believe this is because the MaxStates variable is set to 4 (which is the largest number of states in any variable). How can the code be altered so that it doesn't matter which variable has the largest number of states? The output shouldn't contain blank cells.

Thanks again.

Mbrolass.

Opps - sorry!

The following altered version of the AddCodes procedure should help. It checks that the last value is not a blank before writing the rest

Sub AddCells(ByVal PrecedingCells As Variant, ByVal SmallerArray As Variant)
    If UBound(SmallerArray, 1) <> 0 Then
        PrecedingCells(UBound(PrecedingCells) - UBound(SmallerArray, 1) + 1) = SmallerArray(0, Z)
        Call ProcessArray(SmallerArray)
    Else
        For B = 0 To UBound(SmallerArray, 2)
            If SmallerArray(0, B) <> "" Then
                CurrentRow = CurrentRow + 1
                For A = 0 To UBound(PrecedingCells)
                    Cells(CurrentRow, A + 1) = PrecedingCells(A)
                Next A
                Cells(CurrentRow, UBound(PrecedingCells) + 2) = SmallerArray(0, B)
            End If
        Next B
    End If
End Sub

Works perfectly now. Thanks for all your help.