Dynamic variable state combinations

[Mbrolass]

Hi,

I'm new to visual basic and have been using Excel for some time. I have searched the forums for a solution to my problem and the closest I have is from http://www.excelforum.com/showthread...t=combinations however... I don't understand the solution.

My Problem:

On one sheet (input) I ask a user to input the number of variables to be considered (e.g. 2 variables). On another sheet (States) I read in the number of variables and ask the user to input a number of states for each variable (e.g. variable 1 has x states, variable 2 has y states). Once the user inputs the number of states I then ask then user to input the names of each state for each variable (e.g. State 1 for variable 1 = True, State 2 for variable 1 = False, State 1 for variable 2 = True, State 2 for variable 2 = False). The number of variables and the number of states can vary.

I have managed to create VB macros to this point and now I am stuck. I would like read in the states and create a table with all possible combinations in the cells. E.g. if there are 2 variables there will be two columns and, using the example above, the table will look like:

Variable 1 Variable 2
True True
True False
False True
False False

If there are 3 binary true/false variables the table becomes:

Variable 1 Variable 2 Variable 3
True True True
True True False
True False True
True Flase False
False True True
False True False
False False True
False False True

The state inputs always start in cell E1 in the states sheet with a space after each variable state list. E.g. if variable 1 has 2 states they will be listed in E1 and E2, Variable 2 states will start in E4.

Can anyone help? I hope I have provided enough information.

Mbrolass.

[mrice]

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

[Mbrolass]

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!!!

[Mbrolass]

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.

[mrice]

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

[Mbrolass]

Works perfectly now. Thanks for all your help.