You are on the Home/Publications & Training/Case Studies/Range To-From Array page
Web This Site

1001: Exchanging Data Between an Excel Range and a VBA Array

The intuitive way to exchange data between a VBA array and an Excel range might be to write a loop that does the transfer.  Unfortunately, it is very inefficient in terms of processing speed.


A much faster way to exchange data is to use a Variant data type and simply assign values as in the example below. 

Function ExcelToVBA(Rng As Range)
    Dim Arr As Variant
    Arr = Rng.Value
    End Function

The resulting structure in the variant will depend on the shape of the range.

Rows in range   Columns in range   Structure of data in variant
1 1 Data type depends on the cell's content:
Number: Double
Text: String
Boolean: Boolean
Date: Date
Error value: Error
1 N 2D array with 1 row and N columns
M 1 2D array with M rows and 1 column
M N 2D array with M rows and N columns