I pass a big range from an Excel spreadsheet to the parameter ExcelRange of a user defined function (UDF) written in VBA.
- I can declare the parameter ExcelRange As Range and then convert it to Variant:
Dim VariantArray As Variant
VariantArray = ExcelRange.Value2
or
- I can directly declare the parameter ExcelRange of the UDF as Variant.
In either case, inside a loop, I will convert each element of VariantArray to a Double variable.
Which is more efficient, 1. or 2.?