Excel VBA使用数组加速代码

Question

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

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

1. 创建一个n维数组
2. 用ComboBoxes的值填充n维数组。
3. 遍历所有ComboBoxes。
4. 将值存储在数组中
5. 将值从数组写入电子表格 亚WantToUseArray() .ComboBox1.ListIndex=0表示l=0到25页(“测试”).ComboBox3.ListIndex=l页(“测试”).ComboBox2.ListIndex=0(n=0到25页(“测试”).ComboBox4.ListIndex=n张(“点”).ComboBox4.ListIndex=n张(“点数”).Select Dim LR，LR =单元格(Rows.Count，1).End(xlUp).Row +1单元(LR，"A").Value =Sheet(“Test”).Range(G5).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 =Sheet(“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”).Range(“G6”)X6 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”).Range(K52).Value下一端)

Answer 1

此代码遍历给定worksheet中的每个worksheet，生成包含每个comobox列表的列表值的array，然后将所有内容打印到第一列中。myArray只有一个维度。它的内容是其他arrays。如果comoboxes具有不同的列表长度，则创建一个jagged array。

要帮助可视化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

​

​