Arrays as best tables alternative in Excel VBA

As I mentioned in the title – arrays are awesome. This is one of the main things You should learn working with Excel VBA. In case of data, I think I can safely say, that arrays are amazing alternative for tables in VBA. To prove that statement I’d like to start with 1-dimension array.
But first things first – You may ask:

What the heck are thooose arrays?!

I’m glad You asked, because I am prepared to share some info with You 😉
I will not share You definitions from other websites or from books kinda What is VBA? I’ll just show You how I understand arrays and how am I using them to deal with Excel cases and… other stuff.

So, getting to the point I already mentioned above: 1-dimension array – what is that? In the simple words, it’s like a 1 column table, but virtual.

Let’s compare looping in standard Excel table and in array to prove why are they awesome.

Code for looping test in Excel table:

Sub test1()

Dim tajm As Double
Dim i As Long

tajm = Timer

With ThisWorkbook.Sheets(1)

    For i = 1 To x
    
        .Range("A" & i) = i
    
    Next

End With

Debug.Print Round(Timer - tajm, 3)

End Sub

where x stands for rows number, same for Sub test2 (You’ll see in the table down below).

Code for looping test in array table:

Sub test2()

Dim arr As Variant
Dim tajm As Double
Dim i As Long

tajm = Timer

arr = ThisWorkbook.Sheets(1).Range("A1:A" & x) 'e.g. "A1:A1000" etc

For i = LBound(arr) To UBound(arr)
    
    arr(i, 1) = i
    
Next i

ThisWorkbook.Sheets(1).Range("A1:A" & x) = arr

Debug.Print Round(Timer - tajm, 3)

End Sub

In second case first of all I had to set the range of array, then loop on it and in the end insert results into the Excel range back again.

I made a few tests with those simple codes (inserting the number of iteration into cells) for couple different row numbers. The results are shown in the table below in seconds.

Rows	Array	Table
100	0,000	0,034
1000	0,002	0,090
10000	0,017	0,849
100000	0,188	8,500
1000000	1,727	82,105

Conclusions

As You can see for the small amount of data (rows) there is no visible difference for user in those methods, so in that case there is no need to make it harder in code – to use array.

But even that, based on tests, we can clearly see the multiplication in time for the same rows.

That ‘visible’ difference is in the 100 000 rows and above. I mean the time for standard table increased above a second.
It is a huuge gap between the results – arrays are about 40times quicker! And that gap You would feel, as a user or as a developer, unbelievable difference. It really means a lot!

Don’t You think now that arrays are the best tables alternative in VBA?!

Remember also that was a simple task in every iteration. That time would be much higher having conditionals and others stuff inside loops.

Keep that in mind building a code what would suits more your data.

Next post: https://simpleexcelvba.com/can-arrays-store-cell-properties-like-font-colors-or-fill-colors/