Better Performance

[NickRed18]

Good Morning Everyone! (at least at the moment where I am located),

I am starting to get pretty deep into my current project, and I am having to take into account of the performance of my macro. I have 2 arrays - Both are 2D. One is 360x4 and the other is 120x4, and I have a seperate "FillArrays" Sub dedicated to fill them because they are used in multiple Subs. They are filled by looping through my 4 columns to collect all the items I have. I've read somewhere that it is better to change elements in an array, rather than changing cells in a worksheet (efficiency wise).

Is there a way to create a true Public array where I do not have to call another Sub to fill them?

 

[Norie]

How exactly are you populating the arrays?

 

[NickRed18]

They are filled by looping through my 4 columns [in my spreadsheet of data] to collect all the items I have.

For i = 1 to 360
    For j = 1 to 4
        Array1(i,j) = Sheet1.Cell(i+1,j)             'i+1 due to header
    Next j
Next i

For i = 1 to 120
    For j = 1 to 4
        Array2(i,j) = Sheet2.Cell(i+1,j)             'i+1 due to header
    Next j
Next i

 

[Norie]

Can you post the code?

 

[NickRed18]

This is my FillArrays() Sub. CountList() gets me the values of n and m.

Sub FillArrays()
    Call CountList                                  'Calls n & m - number of items and active items
    ReDim Items(1 To n, 1 To 4) As String           'Master list of items
    ReDim ActiveItems(1 To m, 1 To 4) As String     'Active list of items
    Dim i, j As Integer                             'Counting integers
    
    For i = 1 To n
        For j = 1 To 4
            Items(i, j) = Worksheets("MasterList").Cells(i + 1, j + 1)
        Next j
    Next i
    
    For i = 1 To m
        For j = 1 To 4
            ActiveItems(i, j) = Worksheets("ActiveItems").Cells(i + 1, j + 1)
        Next j
    Next i
End Sub



'Then this is the sub that is callling FillArrays
Private Sub RemoveItemButton_Click()
    Call Module1.FillArrays
    Dim i, j As Integer
    Dim pn As String
    
    pn = LookupItemInterface.LookupItemList.Value
    
    For i = 1 To n
        If pn = Items(i, 2) Then
        End If
    Next i
            
    
    
    LookupItemInterface.LookupItemList.Value = ""
End Sub

 

[Norie]

You don't need to loop to fill your arrays from the ranges in your code.

Array1 = Sheet1.Cells(2, 1).Resize(360, 4)
Array2 = Sheet2.Cells(2, 1).Resize(120, 4)

 

[NickRed18]

I'm getting an error - "can't assign to array".
I have a testing sheet with a header and 22 rows & 2 columns of random text/numbers.

Sub Rangetest()
    Dim ArrayTest1(1 To 22, 1 To 2) As String
    ArrayTest1 = Worksheets("TestSheet").Cells(2, 1).Resize(22, 2)
End Sub

 

[Norie]

Don't specify the dimensions or type for the array.

Sub Rangetest()
Dim ArrayTest1 As Variant
    ArrayTest1 = Worksheets("TestSheet").Cells(2, 1).Resize(22, 2)
End Sub

 

[NickRed18]

Wow thank you! That seems much more simple.

Now should I still have my separate FillArrays Sub, and just call it whenever I need my 2 arrays? Or should I just have these lines of code in each of my other Subs (the ones currently calling FillArrays)?