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 |