excel - 计算输入/输出的组合

Question

一个 Excel 文件中有 3 个工作表（“方向”、“计算”、“组合”），每个工作表如下所示：

“方向”表： 这是用户应该与之交互的唯一表；用户基本上复制有关工作表“计算”的信息

“计算”表： 此表包含输入（简单整数）和输出列表（我添加了 G 列，它解释了哪些公式用于计算 F 列的输出）

目的是创建一个 VBA 脚本，将“方向”列 C 中的值插入“计算”列 D，然后从“计算”列 F 中提取结果输出，并将所有内容保存在工作表“组合”中的单个表中。结果表应该基本上包含所有输入和相应输出的所有组合，如下所示：

问题是这个解决方案是不可扩展的，这意味着它现在只能使用 3 个输入和 4 个输出。对于不同数量的输入/输出，脚本需要手动调整才能正常工作（添加新变量、向 FOR 循环添加新层等）。有没有办法让这个脚本针对任意数量的输入和输出进行自我调整？

我在这里使用的脚本：

Sub AllCombinations()

Application.ScreenUpdating = False

Dim thisWB As Workbook
Dim sheetDirections As Worksheet, sheetCalc As Worksheet, sheetComb As Worksheet
Dim input1 As Integer, input2 As Integer, input3 As Integer
Dim output1 As Integer, output2 As Integer, output3 As Integer, output4 As Integer
Dim inputValues1() As String, inputValues2() As String, inputValues3() As String
Dim inputCoords1 As String, intputCoords2 As String, inputCoords3 As String
Dim outputCoords1 As String, outputCoords2 As String, outputCoords3 As String
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim rowNr As Integer

Set thisWB = ThisWorkbook
Set sheetDirections = thisWB.Worksheets("directions")
Set sheetCalc = thisWB.Worksheets("calculation")
Set sheetComb = thisWB.Worksheets("combinations")

' save coordinates of input/output
inputCoords1 = sheetDirections.Cells(2, 2).Value
inputCoords2 = sheetDirections.Cells(3, 2).Value
inputCoords3 = sheetDirections.Cells(4, 2).Value
outputCoords1 = sheetDirections.Cells(2, 6).Value
outputCoords2 = sheetDirections.Cells(3, 6).Value
outputCoords3 = sheetDirections.Cells(4, 6).Value
outputCoords4 = sheetDirections.Cells(5, 6).Value

' clear whole sheet before use
sheetComb.Cells.Clear

' print names in first row
sheetComb.Cells(1, 1).Value = sheetDirections.Cells(2, 1).Value
sheetComb.Cells(1, 2).Value = sheetDirections.Cells(3, 1).Value
sheetComb.Cells(1, 3).Value = sheetDirections.Cells(4, 1).Value
sheetComb.Cells(1, 4).Value = sheetDirections.Cells(2, 5).Value
sheetComb.Cells(1, 5).Value = sheetDirections.Cells(3, 5).Value
sheetComb.Cells(1, 6).Value = sheetDirections.Cells(4, 5).Value
sheetComb.Cells(1, 7).Value = sheetDirections.Cells(5, 5).Value

' split input variables, separator is ';'
inputValues1 = Split(sheetDirections.Range("C2").Value, ";")
inputValues2 = Split(sheetDirections.Range("C3").Value, ";")
inputValues3 = Split(sheetDirections.Range("C4").Value, ";")

' input/output calculation and printing
rowNr = 2
For i1 = 0 To UBound(inputValues1)
    For i2 = 0 To UBound(inputValues2)
        For i3 = 0 To UBound(inputValues3)
            ' inputs - print
            sheetComb.Cells(rowNr, 1).Value = inputValues1(i1)
            sheetComb.Cells(rowNr, 2).Value = inputValues2(i2)
            sheetComb.Cells(rowNr, 3).Value = inputValues3(i3)
            ' outputs - insert
            sheetCalc.Range(inputCoords1).Value = inputValues1(i1)
            sheetCalc.Range(inputCoords2).Value = inputValues2(i2)
            sheetCalc.Range(inputCoords3).Value = inputValues2(i3)
            ' outputs - print
            sheetComb.Cells(rowNr, 4).Value = sheetCalc.Range(outputCoords1).Value
            sheetComb.Cells(rowNr, 5).Value = sheetCalc.Range(outputCoords2).Value
            sheetComb.Cells(rowNr, 6).Value = sheetCalc.Range(outputCoords3).Value
            sheetComb.Cells(rowNr, 7).Value = sheetCalc.Range(outputCoords4).Value

            rowNr = rowNr + 1
        Next i3
    Next i2
Next i1

Application.ScreenUpdating = True

End Sub

标签： excelvba