Dynamically Populating VBA Array Variables To Store Lists of Data
What Are Arrays?
Arrays are a variant type variable that you can use in VBA coding to store a list of data. Think of it as a mini-spreadsheet inside of a single variable. You store data into an array by referring to a reference number that corresponds with the location that the piece of data is positioned in.
Below is an example of an array that is holding all the month names within a year. Notice that the reference number starts at zero instead of one.
You can also have multi-dimensional arrays. Below is a two-dimensional array that looks much like a typical spreadsheet is setup. You can create even more dimensions if you need to (ie think “Data Cube”), but in my experience two dimensions is the max that a typical person will ever need.
Now that you’ve had a quick overview of what arrays are, let’s get into the meat of this article and learn various ways to dynamically resize these variables to fit all of your data.
Method 1: Resizing First
This method resizes the array to the size of your target data or list before actually placing the values inside the array. This can be a good option if you know beforehand how many items you need to store.
Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Given Size
Dim myArray() As Variant
Dim DataRange As Range
Dim cell As Range
Dim x As Long
'Determine the data you want stored
Set DataRange = ActiveSheet.UsedRange
'Resize Array prior to loading data
ReDim myArray(DataRange.Cells.Count)
'Loop through each cell in Range and store value in Array
For Each cell In DataRange.Cells
myArray(x) = cell.Value
x = x + 1
Next cell
'Print values to Immediate Window (Ctrl + G to view)
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x
End SubMethod 2: Resizing As You Go
This VBA method resizes the array variable right before storing each item. The key command here is “Preserve“. This tells the code to keep all the stored items in the Array while increasing it’s storage capacity. If you forgo having Preserve in your code immediately after you ReDim, the array variable will wipe clean of data previously stored in it before re-dimensioning.
Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Given Size
Dim myArray() As Variant
Dim DataRange As Range
Dim cell As Range
Dim x As Long
'Determine the data you want stored
Set DataRange = ActiveSheet.UsedRange
'Loop through each cell in Range and store value in Array
For Each cell In DataRange.Cells
ReDim Preserve myArray(x)
myArray(x) = cell.Value
x = x + 1
Next cell
'Print values to Immediate Window (Ctrl + G to view)
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x
End SubMethod 3: Creating From A Delimited String
Another way you can populate an array variable is through a delimiter. A delimiter is simply a designated set of character(s) that separates out your values. Have you ever heard of a CSV file? CSV stands for “Comma-Separated Values” where a comma symbol tells your computer how to separate each value apart from one another.
You can use this same concept to make your own delimited string or (more realistically) read a delimited string exported from your database software to populate the array. In my below example code I am taking a set range and turning it into a delimited string with the characters “;|;” separating each value.
Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Given Size
Dim myArray As Variant
Dim myString As String
Dim DataRange As Range
Dim cell As Range
Dim x As Long
'Determine the data you want stored
Set DataRange = ActiveSheet.Range("A1:A100")
'Loop through each cell in Range and store value in delimited string
For Each cell In DataRange.Cells
myString = myString & ";|;" & cell.Value
Next cell
'Remove first delimiter from string (;|;)
myString = Right(myString, Len(myString) - 3)
'Create an array with the Split() function
myArray = Split(myString, ";|;")
'Print values to Immediate Window (Ctrl + G to view)
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x
End SubIf you are already starting with a delimited string, use this simplified VBA code to accomplish the same thing.
Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Given Size
Dim myArray As Variant
Dim myString As String
Dim x As Long
'Store delimited string to a variable
myString = "Apple;|;Orange;|;Pear;|;Peach;|;Grapes"
'Create an array with the Split() function
myArray = Split(myString, ";|;")
'Print values to Immediate Window (Ctrl + G to view)
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x
End SubMethod 4: Pulling From An Excel Table [My Favorite Option!]
This is my favorite way to populate array variables dynamically because it has a user-interface aspect to it (ie it allows you to make changes to the inputs without rewriting the code). Now you may be thinking to yourself that a couple of the VBA snippets above are pulling from ranges that you could easily substitute with a named range in your code. This is true, but tables have the auto-expanding feature that can make you 100% certain from a visual perspective that you are picking up all your values.
You will want to note that when you size an Array variable from a Range or Table, the first reference number will be a One instead of the typical Zero.
Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Single Columned Table
Dim myArray() As Variant
Dim TempArray() As Variant
Dim myTable As ListObject
Dim x As Long
'Set path for Table variable
Set myTable = ActiveSheet.ListObjects("Table1")
'Create Array List from Table
TempArray = myTable.DataBodyRange.Columns(1)
'Convert from vertical to horizontal array list
myArray = Application.Transpose(TempArray)
'Loop through each item in the Table Array (displayed in Immediate Window [ctrl + g])
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x
End SubAny Other Methods?
Are there any other methods you use to populate arrays dynamically? I would love to hear from you in the comments section below and maybe I can add some other options to the ones I’ve already discussed in the article.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.