Quick Jump
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

wellsrPRO

Coming Fall 2017

The new best way to learn VBA

Absolutely FREE when you join the waitlist



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:

Solar Eclipses of 21st Century

You want to store the values to an array so you can do some quick data manipulation.

Once you know how big your range is, simply plug in your range into the example macro. It’s important that your array be declared as Variant data type.

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:

Excel VBA Assign Range to Array Proof

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.

Common Errors

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.

More Tips

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

The .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!

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

The .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!


Application Ideas

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.


Comments

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!

Please subscribe to my email list and share this article with your friends on Facebook, Twitter, and Google+.

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!


wellsrPRO

Coming Fall 2017

The new best way to learn VBA

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.