Excel convert Range to Array in VBA

Arrays have more powerful options than simple Excel ranges but, happily, it’s very easy to convert a range of cells into an array.

Or sometimes you have a mix of arrays and ranges that need to be managed as a collection of arrays.  That’s what we’ve needed to make the NetworkDays() and NetworkDays_Intl() functions work properly.

Here’s a simple Range2Array function that you can use:

Simple on it’s own but very useful when dealing with a mix of arrays and ranges.

There’s no special conversion function necessary, just copy the range variable into an array (Variant) variable.  VBA handles the variable type conversion. If you don’t need the whole function, here’s the main code line for your own VBA:

array = Range 

Here’s the code:

 

Public Function Range2Array(Range1 As Range) As Variant

‘ converts an incoming cell range into an array

‘ use with MergeArrays() to include ranges in a merged array

Dim arr1 As Variant

arr1 = Range1

Range2Array = arr1

End Function

Excel now has Dynamic Arrays – Windows, Mac and more …

Dynamic Arrays now in Excel for iPhone and iPad

Bonus for ‘Real Time Excel’ readers