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
