Excel VBA using an array to speed up code

Question

I am trying to create an array, store values in the array and then write the values of the array to a spreadsheet in VBA. This codes takes 1+ hour to run on my computer and I think that an array could really speed up the code.

However, I need help with creating the array, populating the array from the comboboxes and finally write the values of the array to the worksheet.

Create an n-dimensional array
Fill the n-dimensional array with the values of the ComboBoxes.
Iterate through all ComboBoxes.
Store values in the array

Write values from the array to the spreadsheet

Sub 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

I might be overlooking something.. but why are you using a nested for loop in this case? — Slaqr
– Slaqr, Commented Aug 22, 2018 at 14:10
Thank you for your reply! I am iterating through 4 ComboBoxes, the for loop loops through 26 items in ComboBox3 and ComboBox4. — Waimea
– Waimea, Commented Aug 22, 2018 at 14:12
In the code above you are putting values (l or n) into the comboboxes' Listindex, so a different item in the list will be selected.. Is it the selected item values you want to store to an array? — Slaqr
– Slaqr, Commented Aug 22, 2018 at 14:17
Also, the lines that copy values from one sheet to another are included in a 25x25 nested loop, but there aren't any variables in these lines. Is the data to be copied dependent on the value of the comboboxes? (Just trying to clarify the situation before coming up with a possible solution) — Slaqr
– Slaqr, Commented Aug 22, 2018 at 14:19
I attached an image with some of the output of my code. The output is dependent on the ComboBoxes and everytime I change the value of the comboboxes the values to be copied changes. Län1 = ComboBox1, Kommun1 = ComboBox3, Län2 = ComboBox2, Kommun2 = ComboBox4. In the best case scenario I would like to change the n-dimension of the array to add or remove variables! — Waimea
– Waimea, Commented Aug 22, 2018 at 14:23

learnAsWeGo · Accepted Answer · 2018-08-22 17:22:42Z

2

This code goes through each combobox in a given worksheet, generates an array that contains the list values for each comobox list, then prints all of the contents into that first column. myArray only has a single dimension. Its contents are other arrays. If the comoboxes have different list lengths, a jagged array is created.

To help visualize the arrays, enable the Locals Window by going to view in the menu bar and then selecting Locals Window. See pic below the code.

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

edited Aug 22, 2018 at 17:22

answered Aug 22, 2018 at 17:09

learnAsWeGo

2,2823 gold badges15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Waimea Over a year ago

Thank you very much for your reply and for your code learnAsWeGo! This works very fast on my computer and I am going to follow your example and look at the locals window to visualize the arrays.

learnAsWeGo Over a year ago

@Waimea glad to hear it!!

Waimea Over a year ago

Would it be possible to get the output of your code to match the way the data is arranged in my image? :)

learnAsWeGo Over a year ago

@Waimea if i get a chance i will try!

Collectives™ on Stack Overflow

Excel VBA using an array to speed up code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related