Dynamically Populating VBA Array Variables To Store Lists of Data

Dynamically Fill VBA Array Variables

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 Sub

Method 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 Sub

Method 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 Sub

If 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 Sub

Method 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 myTable As ListObject
Dim cell As Range
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 Sub

Any 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.

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)