Excel VBA Assign Range to Array

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

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

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.

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:

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

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.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.