arrays - Excel VBA 使用数组加速代码

Question

我正在尝试创建一个数组，将值存储在数组中，然后将数组的值写入 VBA 中的电子表格。这段代码在我的电脑上运行需要 1 个多小时，我认为数组真的可以加快代码速度。

但是，我需要帮助来创建数组、从组合框中填充数组，最后将数组的值写入工作表。

创建一个n维数组

用组合框的值填充 n 维数组。

遍历所有组合框。

将值存储在数组中

将数组中的值写入电子表格

子 WantToUseArray()

Dim k As Integer
Dim l As Integer

Sheets("Test").ComboBox1.ListIndex = 0

For l = 0 To 25
Sheets("Test").ComboBox3.ListIndex = l

Sheets("Test").ComboBox2.ListIndex = 0

For n = 0 To 25
Sheets("Test").ComboBox4.ListIndex = n

Sheets("Points").Select

Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(LR, "A").Value = Sheets("Test").Range("G5").Value
Cells(LR, "B").Value = Sheets("Test").Range("G6").Value

Cells(LR, "C").Value = Sheets("Test").Range("O5").Value
Cells(LR, "D").Value = Sheets("Test").Range("O6").Value

Cells(LR, "E").Value = Sheets("Test").Range("X5").Value
Cells(LR, "F").Value = Sheets("Test").Range("X6").Value

Cells(LR, "G").Value = Sheets("Test").Range("G6").Value + Sheets("Test").Range("X6").Value

Cells(LR, "H").Value = Sheets("Test").Range("X6").Value + Sheets("Test").Range("G6").Value

Cells(LR, "I").Value = Sheets("Test").Range("K40").Value
Cells(LR, "J").Value = Sheets("Test").Range("K41").Value

Cells(LR, "K").Value = Sheets("Test").Range("K51").Value
Cells(LR, "L").Value = Sheets("Test").Range("K52").Value



Next
Next

End Sub

Answer 1

此代码通过每个 combobox在给定的 worksheet , 生成 array包含每个 comobox 的列表值列表，然后将所有内容打印到第一列。 myArray 只有一个维度。其内容为其他arrays .如果 comoboxes有不同的列表长度，一个 jagged array被 build 。

为了帮助可视化 arrays , 启用 Locals Window通过在菜单栏中查看，然后选择 Locals Window .请参见代码下方的图片。

Option Explicit
Sub main()
Dim ws                                                 As Worksheet
Dim mainArray()                                          As Variant
Dim ctrl                                               As Object
Dim numComboBoxes                                      As Long

    Set ws = ActiveSheet

    numComboBoxes = GetNumberOfComboBoxesInSheet(ws)
    mainArray = GenerateJaggedArrayComboBoxListValues(ws, numComboBoxes)
    PrintArray ws, mainArray
End Sub

Function GetNumberOfComboBoxesInSheet(ByRef ws As Worksheet) As Long
Dim ctrl As Object
    For Each ctrl In ws.OLEObjects
        If TypeName(ctrl.Object) = "ComboBox" Then
            GetNumberOfComboBoxesInSheet = GetNumberOfComboBoxesInSheet + 1
        End If
    Next ctrl
End Function

Function GenerateJaggedArrayComboBoxListValues(ByRef ws As Worksheet, ByVal numComboBoxes As Long) As Variant()
Dim ctrl                                                As Object
Dim tempPrimaryArray()                                  As Variant
Dim tempArray()                                         As Variant
Dim x                                                   As Long
Dim y                                                   As Long
Dim listNum                                             As Long

   ReDim tempPrimaryArray(0 To numComboBoxes - 1)
    x = 0
    For Each ctrl In ws.OLEObjects
        If TypeName(ctrl.Object) = "ComboBox" Then
            y = 0
            For listNum = 0 To ctrl.Object.ListCount - 1
                ReDim Preserve tempArray(0, 0 To y)
                tempArray(0, y) = ctrl.Object.List(listNum, 0)
                y = y + 1
            Next listNum
            tempPrimaryArray(x) = tempArray
            Erase tempArray
            x = x + 1
        End If
    Next ctrl
GenerateJaggedArrayComboBoxListValues = tempPrimaryArray()
End Function

Sub PrintArray(ByRef ws As Worksheet, ByRef mainArray As Variant)
Dim counter                                             As Long
Dim x                                                   As Long
Dim y                                                   As Long
Dim tempArray()                                         As Variant

    counter = 1
    For x = LBound(mainArray, 1) To UBound(mainArray, 1)
        tempArray = mainArray(x)
        For y = LBound(tempArray, 2) To UBound(tempArray, 2)
            ws.Range("A" & counter) = tempArray(0, y)
            counter = counter + 1
        Next y
    Next x
End Sub