November 12, 2016 November 12, 2016/ 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 FirstThis 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() 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 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() 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 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() 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() 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 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() 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 Sub 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 :) November 12, 2016/ |