How to declare array in Excel VBA?

I don’t want to make a duplicate of official Microsoft Office website, because it is not the case and I see no point in it. You are here, because You were there. Now You are looking for more details or examples how to declare array to deal with common, daily tasks.

I’m glad You are here. I want to share with You how am I using arrays.

First of all, mainly I’m using them to avoid operating on range, I mean group of cells with all their properties. Thanks to that, working on Excel ranges – tables is much, much slower. I even proved that in my previous post https://simpleexcelvba.com/arrays-as-the-best-table-alternative-in-excel-vba/

Range as array

So, how to easily declare an array? For example You can build an array from first column till the last filled row. Just follow this:

Dim ws As Worksheet
Dim arr As Variant
Dim lastRow As Long

Set ws = ThisWorkbook.Sheets(1)

With ws

    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    arr = .Range(.Cells(1, 1), .Cells(lastRow, 1))

End With

Above method is really good when You just want to put those values into virtual table to work faster.

Looping through table to array

The same range You can declare in other way knowing the size of an array. If You need to do any operation, conditional w.e. on values before putting into array, this method is for You:

Dim ws As Worksheet
Dim arr As Variant
Dim lastRow As Long, i As Long

Set ws = ThisWorkbook.Sheets(1)

With ws

    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    ReDim arr(1 To lastRow)
    
    For i = LBound(arr) To UBound(arr)
        If .Cells(i, 1) > 0 Then
            arr(i) = .Cells(i, 1)
        Else
            arr(i) = 0
        End If
    Next i

End With

Remember, there is no “the only way” to write your code. Sometimes it is faster, sometimes slower, the main thing it should be yours, you need to feel this, understand this. There will be the time You will see that your code needs improvement, or not.

But enough of “coaching”, because I’m not any master or guru. I’m only telling You that, because I know that from experience.

Words list as array

Coming to the next approach of declarations, sometimes I’m using them also this way:

Dim ws As Worksheet
Dim arr As Variant
Dim lastRow As Long, lastCol As Long

Set ws = ThisWorkbook.Sheets(1)

With ws

    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    arr = Array("word1", "word2", "word3")
    
    .Range(.Cells(1, 1), .Cells(lastRow, lastCol)).AutoFilter _
Field:=14, Criteria1:=arr, Operator:=xlFilterValues
    
End With

Or “shorter”:

    .Range(.Cells(1, 1), .Cells(lastRow, lastCol)).AutoFilter _
Field:=14, Criteria1:=Array("word1", "word2", "word3"), _
Operator:=xlFilterValues

Mainly this is for filtering data. In most of cases I have that predefined filter conditions, but sometimes I’m gathering that conditions with loop into array, just like I showed You before.

Of course this is not everything. I just wanted to show You how I declare arrays in most of my macros. I hope it will help someone, at least a little bit. Feel free to share your opinion and suggest anything.