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.
One thought on “How to declare array in Excel VBA?”