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
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheet with just one click.
Tutorial - Assign Range to Array
Assigning a range to an array is a great way to speed up your macro. Working with arrays is typically a lot faster than controlling the individual cells in your spreadsheet. If you’d like to become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.
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 AN 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 AN ERROR, but it's 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!