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