Excel VBA Arrays For Beginners

Here’s one I avoided for a long time. Why? I found it hard to conceptualise what a VBA ‘array’ is. I came to understand it like this: it’s a range of cells that lives in VBA’s memory. Yes, we can’t ‘see’ an array – we can only extract parts of it; but, in Excel’s mind, it’s a table of data – cells containing values or, in our case, text strings. A table in Excel’s memory!

💻FREE: YOUR EXCEL CHEATSHEET MINI-COURSE

00:00 Introduction
01:20 Key Concepts & Techniques
03:00 VBA Coding Start
03:40 Excel VBA Split Function
06:05 Lbound And Ubound Functions
07:35 Using Array Index Numbers
09:40 Integrate For-Next Loop
12:20 Integrate Offset Method

If you can get your head around this, the implications are intriguing. We work with tables or ranges of data in Excel all the time – and it can be onerous: formulae such as VLOOKUP can take time, and even macros can feel slow when they write to or read from the spreadsheet. So, what if there wasn’t a spreadsheet at all? What if the data ‘lived’ in VBA instead? It’s mind-bending … but could it work?

💼DOWNLOAD FILES

Get ready for some slick VBA macros, some difficult coding to get it all working, and at least one ‘punch-the-air’ moment. Welcome to Excel VBA Arrays For Beginners!

When might a VBA array be useful for in the real world? My customer wanted me to ‘fragment’ a single cell of text entries, separated by semi-colons, to separate cells. So “ABC;DEF;GHI” would become “ABC”, “DEF”, “GHI”, with those values entered into different cells in the same column. It’s possible with formulae, of course, and a formula-based solution is included in the download file. But, what if you didn’t have the spreadsheet real estate to implement the formulae? Perhaps you just want a faster, leaner solution? Let’s look at how to do it with arrays in Excel VBA.

VBA’s Split function gets the party started. It does a lot of work: the function separates the text string (eg. the original text entry) into chunks using a ‘delimiter’. A particular character is needed to separate or ‘delimit’ the text – in our case a semi-colon. Yes, some kind of delimiter is essential for this application of arrays in Excel VBA.

So, in the background, Excel has created an ‘array’ comprising our 3 smaller text strings. Visualising this, however, is difficult – which is why it took me so long to work out arrays. As I demonstrate in the video, the Msgbox technique for externalizing variable values simply doesn’t work with arrays. Arrays require a special variable type – or at least a modification of a string variable – in the format VARIABLENAME(). Those brackets seem to make everything work – but also make the code rather opaque / inaccessible. What’s actually going on?

💻FREE: YOUR EXCEL CHEATSHEET MINI-COURSE

Things get easier when you understand the values in the array (ie “ABD”, “DEF”, “GHI”) are allocated an index number. Which must mean 1,2,3, right? Well, not quite. Because arrays always start at 0. Why? I don’t know and if you do – please enlighten me in the YouTube comments! So, the values in the array are allocated index numbers of 0,1 and 2. The Lbound (lower bound) and Ubound (upper bound) functions in VBA demonstrate this and do work with Msgbox, as I show in the video.

We can use the index number to externalize the values in the array and finally pull back the curtain on what is going on. I use the construct Msgbox Chris_Array(0) in the video to extract the values. Progress! But, the task is not yet complete. How do we now input the array’s values into separate cells in the spreadsheet?

You can use a For-Next loop in combination with an array to get this done. A loop can ‘count through’ the array’s index (0,1,2) and write the corresponding values to a cell using eg. Range(“A1”) = Chris_Array(Array_Counter), where Chris_Array is the variable storing the array, and a Array_Counter is the integer variable controlling the loop. It’s probably easier to understand watching the video demonstration – I promise it will be worth your while!

A final VBA technique brings everything together. I’ve said many times on the channel that the Offset method can work with a For-Next loop like fish and chips: magic happens when these two are paired together. As the counting variable increments up, the macro works down the spreadsheet to list the values of the array in adjacent rows. Job done! See how I set it up in the video and remember the download files from the start AND end are available and should help.

Did you get it all working and did you have a ‘punch-the-air’ moment? Can you see application for VBA arrays in your work? I would love to hear your thoughts in the YouTube comments – I will get back to you there as usual.

💻FREE: YOUR EXCEL CHEATSHEET MINI-COURSE

[TigerSpreadsheetSolutions]

🔥WATCH NEXT
COPY DATA FROM ONE SHEET TO ANOTHER WITH EXCEL VBA

[henrikijonkoping]

After watching your video today, I adjusted my code, the result lead to a "punch in the air" moment. Great thank you!

[nicoledewaard]

Thanks Chris, it's very helpful for my project. I like that you first explain the concepts and then the application.

[tlee]

Another FANTASTIC tutorial !! Thank you !!

[mohamadkuk]

Amazing!
Finally a perfect explanation 🔥

[kensimba]

I loove this and Chris Ur a life saver and a genius. Always tuned to your channel and God bless you abundantly. Keep making this video's

[henrikijonkoping]

Great Chris, but in my mind to "Split" is as a method in VBA! But here it is a function.

[keithdangerfield]

Chris, I first stumbled into an array when I used application.unique in VBA and couldn't understand why it didn't spill the values as in Excel. Once I realised what was going on I used count to confirm the number of entries and then specified a range for them to display. Not as elegant as your method.
I also like how you can easily populate an array by specifying a whole table.
It's interesting how, because the first array key is 0, you can offset from within the range you're printing to rather than from outside it, as would usually be the case.
You can also visualise the array from within VBA by looking in the Locals Window. It shows the whole array and it's values.

[Husky_Passion]

arrays are a bit slower than dictionaries, and you can only use integers as key (argument), but you can affect any type to them.
Also to find the one you need, there is no .exists method (needs loops instead).
Also boring to use redim preserve when you don't know the size of the array before hand, and even worse you can only redim the last dimension.
Also arrays are way trickier to use in classes than dictionaries so i stop all together using arrays (except for single string operations and fast read of big range).
Trick : you can declare an array without dim, and directly use redim instead (useful when one limit is a variable, wich would error with dim)
I once made an array of arrays of arrays.... and each array was a custom TYPE, wich would have been harder with dictioneries (needs classes, etc)

[nikolaypetrov]

Hi, i have question about the VBA arrays, how to make this range - Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select dynamic ? Doesn't matter of the name of the sheets ?

[freddyyang]

A late comment, but I love this array formula! It truely moves me away from the good-old Python.