Introduction | Example | Tutorial | Applications | Comments
Introduction - Assign Range to Array
Discover how easy it is to assign a range to an array using Excel VBA. I’ll also show you how to avoid common errors, like the run-time error 13 type mismatch.
Example - Assign Range to Array
Sub AssignRangeToArrayDemo() 'Demonstrates how to assign a range to an array Dim MyArray() As Variant 'unallocated array MyArray = Range("A1:G311").Value2 End Sub
Did you find this helpful?
Please support wellsr.com by purchasing an add-in below. Your support helps me continue to post VBA tutorials like this one.
Record your mouse clicks and cursor movements and convert them directly into VBA macros so you can automate your mouse.
Import hundreds of macro examples from wellsr.com without ever leaving Excel and organize your personal macro library with this best-selling add-in.
Tutorial - Assign Range to Array
Set the Stage
Alright, let’s dive a little deeper. Let’s pretend you have a data table in Excel showing all the future solar eclipses in the world for the next several years, like this:
You want to store the values to an array so you can do some quick data manipulation.
To prove the values are assigned to an array, I’ve added a Watch to the variable MyArray. Take a look at the response in the Watch Window:
You can see the array went from unallocated to allocated. It now has 311 rows and is 7 columns wide, just like our initial range.
There’s a good chance you made it to this page because you tried something similar and got an error. I’m going to walk you through some of these common errors and how to avoid them.
If you don’t put the
.Value2 on the end of your range, you may or not have gotten an error saying Run-time error ‘13’: Type mismatch.
If you got the error, it’s because you have a worksheet name or a workbook name shown in front of your range. Take a look at this example:
Sub AssignRangeToArrayDemoBad1() 'THIS MACRO WILL GENERATE AND ERROR Dim MyArray() As Variant 'unallocated array MyArray = ActiveSheet.Range("A1:G311") 'Creates a Type mismatch error End Sub
The macro above will generate an error because it has
ActiveSheet in front of the range. However, the following, very similar macro, will not produce an error:
Sub AssignRangeToArrayDemoOkay() 'THIS MACRO WILL NOT GENERATE AND ERROR, but is not ideal Dim MyArray() As Variant 'unallocated array MyArray = Range("A1:G311") 'No Type mismatch error End Sub
There’s no error because there’s no sheet name or workbook name in front of the Range.
Don’t worry - the demonstration above doesn’t mean you can’t reference a range on another sheet or a different workbook!
All you have to do is put a
.Value2 on the end of your Range and you can reference whatever sheet you want in front of your range. Take a look:
Sub AssignRangeToArrayDemo2() 'Demonstrates how to assign a range to an array Dim MyArray() As Variant 'unallocated array MyArray = Sheets("sheet1").Range("A1:G311").Value2 End Sub
Why .Value2, you ask? I’ll explain in the next section.
Value vs. Value2 vs. Text
You may already know that appending .Value2 to the end of your range isn’t the only way to grab the contents of your cell… but it’s the best way!
Let me explain. There are 3 common ways to pull text from a cell:
- Range.Text Property
- Range.Value Property
- Range.Value2 Property
.Text property renders the text exactly as it is on your screen. If your column width is too narrow and the value in the cell is shown as ###, that’s exactly what the .Text property will return. Regardless of this limitation, this method doesn’t work when trying to assign a range to an array anyway!
.Value property returns the value of the cell in whatever format it was in in Excel and converts it to the VBA equivalent data type. This is usually a fine way to extract the data, unless your data is formatted as currency or a date. Transmitting as a date may be useful, but the currency can cause some issues. The VBA currency data type only carries 4 decimal places, so your cell value may get truncated when converted to VBA.
.Value2 property is similar to the .Value property with one notable difference. It doesn’t check the format of your cells so it doesn’t try to store your elements as dates or currencies. This ensures your values are extracted exactly as they should be - with no truncation.
You can actually see in the Watch Window screenshot I presented earlier that the date in MyArray(1,1) is stored as the serial number date value Excel uses, instead of a formatted date. That’s because I used Value2 instead of Value!
Value2 is also the fastest way to extract data from a cell, so it’s a double win!
Using Excel VBA to assign a range to an array is an extremely valuable skill. Once in the array, you can work with the data much faster than you could if you manipulated it in its native range format.
I’m sure you all have found great uses for storing ranges in arrays. I’d love to hear about them! Leave me a comment and let me know what kind of neat things you’ve done.
I hope you’ll reach out to me via my VBA Consulting page if you’re working on a macro, but it’s getting you frustrated. I will ease your frustration!
If you’re ready to free up your time in the office, visit my Excel Add-ins page for some powerful automation products. Some of them are free!