Recursive code to generate a parametric sweep of input values

Question

I generate a set of data based off every combination of inputs. The source range that's inputs requires headers to identify the parameters. In its present state I'm not content as it's not as straightforward as I'd like it to be. Specifically the module variables can be cleaned up but I'm not seeing how.

A simple example with 2 columns

---------------------
| Column1 | Column2 |
|---------|---------|
|    A    |    1    |
|    B    |    2    |
|    C    |    3    |
---------------------

generates

---------------------
| Column1 | Column2 |
|---------|---------|
|    A    |    1    |
|    A    |    2    |
|    A    |    3    |
|    B    |    1    |
|    B    |    2    |
|    B    |    3    |
|    C    |    1    |
|    C    |    2    |
|    C    |    3    |
---------------------

Adding a 3 column input table

-------------------------------
| Column1 | Column2 | Column3 |
|---------|---------|---------|
|    A    |    1    |   F1    |
|    B    |    2    |   F2    |
|    C    |    3    |   F3    |
|         |         |   F4    |
-------------------------------

generates

-------------------------------
| Column1 | Column2 | Column3 |
|---------|---------|---------|
|    A    |    1    |   F1    |
|    A    |    1    |   F2    |
|    A    |    1    |   F3    |
|    A    |    1    |   F4    |
|    A    |    2    |   F1    |
 . . . . . . . . . . . . . . .
|    C    |    2    |   F4    |
|    C    |    3    |   F1    |
|    C    |    3    |   F2    |
|    C    |    3    |   F3    |
|    C    |    3    |   F4    |
-------------------------------

and so on.

The class module InputColumn is used to help compartmentalize the logic for each column and its corresponding max depth being equivalent to CountOfValues.

'@PredeclaredId
'@Folder("ParametricSweep.Model")
Option Explicit

Private Type THelper
    ColumnIndex As Long
    Values As Variant
    Identifier As String
End Type

Private this As THelper

Public Property Let Values(ByVal Value As Variant)
    this.Values = Value
End Property
Public Property Get Values() As Variant
    Values = this.Values
End Property

Public Property Get CountOfValues() As Long
    CountOfValues = UBound(Values) - LBound(Values) + 1
End Property

Public Property Let Identifier(ByVal Value As String)
    this.Identifier = Value
End Property
Public Property Get Identifier() As String
    Identifier = this.Identifier
End Property

Public Function Self() As InputColumn
    Set Self = Me
End Function

Public Function Create(ByVal ColumnIndex As Long, ByVal sourceArea As Range) As InputColumn
    Dim populatedCells As Range
    Set populatedCells = sourceArea.SpecialCells(XlCellType.xlCellTypeConstants)

    With New InputColumn
        .Identifier = populatedCells(1, 1).Value2
        .Values = Application.WorksheetFunction.Transpose(populatedCells.Offset(1).Resize(populatedCells.Rows.Count - 1).Value2)

        Set Create = .Self
    End With
End Function

The sweep of values is generated by populating the inputColumns() array then stepping into PopulateArray 1 for the first (topmost) level. After the call to PopulateArray it recursively descends one level deeper until it's populating the last column. As each loop returns it increments the array recursionInputColumnPopulationIndex() that identifies which element should populated for the corresponding recursion depth.

Option Explicit

Private parentArray() As Variant
Private inputColumns() As InputColumn
Private maxRecursionDepth As Long
Private recursionInputColumnPopulationIndex() As Long
Private populationRow As Long

Public Sub TestRecursive()
    Dim foo As Variant
    foo = RecursiveParametricSweep(Sheet1.Range("C5").CurrentRegion, True)

    Dim bar As Variant
    bar = RecursiveParametricSweep(Sheet1.Range("C16").CurrentRegion, False)
End Sub

Public Function RecursiveParametricSweep(ByVal inputSourceArea As Range, ByVal includeHeader As Boolean) As Variant
    maxRecursionDepth = inputSourceArea.Columns.Count

    ReDim inputColumns(1 To maxRecursionDepth)
    ReDim recursionInputColumnPopulationIndex(1 To maxRecursionDepth)

    PopulateInputColumns inputSourceArea

    Dim rowCount As Long
    rowCount = GetRowCount(inputColumns)
    If includeHeader Then
        ReDim parentArray(0 To rowCount, 1 To maxRecursionDepth)

        Dim headerRow As Long
        headerRow = LBound(parentArray)
        Dim headerColumn As Long
        For headerColumn = LBound(inputColumns) To UBound(inputColumns)
            parentArray(headerRow, headerColumn) = inputColumns(headerColumn).Identifier
        Next

        populationRow = LBound(parentArray) + 1
    Else
        ReDim parentArray(1 To rowCount, 1 To maxRecursionDepth)
        populationRow = LBound(parentArray)
    End If

    PopulateArray 1, includeHeader
    RecursiveParametricSweep = parentArray
End Function

Private Sub PopulateArray(ByVal recursionDepth As Long, ByVal includeHeader As Boolean)
    Dim populateElementCount As Long
    For populateElementCount = 1 To inputColumns(recursionDepth).CountOfValues
        If recursionDepth < maxRecursionDepth Then
            PopulateArray recursionDepth + 1, includeHeader

            recursionInputColumnPopulationIndex(recursionDepth) = recursionInputColumnPopulationIndex(recursionDepth) + 1
        Else
            Dim columnPopulationIndex As Long
            For columnPopulationIndex = recursionDepth To 1 Step -1
                parentArray(populationRow, columnPopulationIndex) = inputColumns(columnPopulationIndex).Values(recursionInputColumnPopulationIndex(columnPopulationIndex) + 1)
            Next

            recursionInputColumnPopulationIndex(recursionDepth) = recursionInputColumnPopulationIndex(recursionDepth) + 1
            populationRow = populationRow + 1
        End If
    Next
    recursionInputColumnPopulationIndex(recursionDepth) = 0
End Sub

Private Function GetRowCount(ByRef inputColumns() As InputColumn) As Long
    GetRowCount = 1
    Dim depthCounter As Long
    For depthCounter = LBound(inputColumns) To UBound(inputColumns)
        GetRowCount = GetRowCount * inputColumns(depthCounter).CountOfValues
    Next
End Function

Private Sub PopulateInputColumns(ByVal inputSourceArea As Range)
    Dim populateInputColumnsCounter As Long
    For populateInputColumnsCounter = LBound(inputColumns) To UBound(inputColumns)
        Set inputColumns(populateInputColumnsCounter) = InputColumn.Create(populateInputColumnsCounter, inputSourceArea.Columns(populateInputColumnsCounter))
    Next
End Sub
```

Answer 1

This looks like a decent recursive implementation. So, this is mainly about style and readability.

First, I would like to talk about two things which are really hard and could use some improvement here, naming and consistency of semantic levels. Then I have some more miscellaneous comments.

Naming

Generally, the naming is not too bad, but some of the names are a bit misleading, e.g. the function name GetRowCount suggests that it returns the number of rows of whatever I give it. However, what it returns is the number of rows of the later output. So, one could simply call it OutputRowCount, which also follows the general guideline to use nouns that describe the return value for functions.

Another things with the naming is that it sticks a bit much to the role of things in the implementation and not to what they are. Naming things after what they are can make understanding the code much faster. E.g. using inputColumnIndex instead of recursionDepth would immediately tell the reader that you are dealing with this specific input column right now. Then the technical maxRecursionDepth could also be lastColumnIndex or countOfColumns. Similarly, the rather ominous recursionInputColumnPopulationIndex(recursionDepth) could be currentInputIndex(inputColumnIndex). So generally, my advice would be to name things after what they are and not their implementation purpose.

Consistency of Semantic Levels

Programmers, me included, are notoriously bad a keeping semantic levels consistent in procedures. To explain what I mean take a look at RecursiveParametricSweep. It does some high level orchestration like calling the procedure to populate the input columns, calling a function to get the number of output rows, calling the procedure to populate the output array and assigning the return value. However, in between it goes into the detail of how to handle headers. This is a break in semantic level of the procedure that throws one off a bit when first reading the procdure. This could be avoided by extracting either an InitializeOutputArray or a WriteHeaders procedure.

Miscellaneous

• The global variable maxRecursionDepth is a bit superfluous since it is just UBound(inputColumns), which is a bit clearer semantically, I think.
• Instead of incrementing recursionInputColumnPopulationIndex(recursionDepth), you could just always set it to populateElementCount at the start of the loop, which might be called currentInputIndex instead.
• Istead of keeping track of the indeces for the columns in an array, you might just as well keep track of the values in an array instead. That would put the choice of value closer to the action for the specific column.
• It looks a bit strange to have the populationRow counter as a module level variable. You could avoid this by passing it ByRef into the recursive procedure.
• It might make sense to extract a PopulateRow procedure to keep semantic levels consitsent.