Transferring data from a worksheet into a VBA routine, or the other way round, is one of the most frequent tasks carried out in VBA programming. Fortunately there is a simple way to do it:
In a subroutine:
Name the data range in the spreadsheet
Declare a variant variable in the VBA code
Variable = Rangename.value
Dim DataArray as Variant
DataArray = Range("RangeName").value
Note that there is no need to specify the size of the array, it is automatically set to the size of the specified range.
With functions it is even easier:
Function FuncName(MyData as Variant) as Variant
MyData = MyData.value
There are a couple of traps to watch out for though:
If a function may be used as a UDF or called from another VBA routine, you need to check if the data being passed is a worksheet range or if it is already an array.
If the function parameter is a single cell range it will not be converted into an array.
If the parameter is a single row range it will be converted to a 2D array with a single row. A 1D array therefore needs to be converted into the same form for consistency.
Similarly, a single cell range, if converted into an array, will be converted into a 1D, base zero array. For consistency it needs to be converted into a 2D, base 1 array, with 1 row and 1 column.
The code below carries out all these tasks:
Public Function GetArray(Arrayname As Variant) As Variant
Dim TempA() As Variant, LBound1 As Long
Dim UBound1 As Long, UBound2 As Long
Dim i As Long, j As Long
' If Arrayname is not an array, convert it into one.
' IsArray is true for multi-cell ranges, but not for a single cell range
If Not IsArray(Arrayname) Then
Arrayname = Array(Arrayname)
' If Arrayname is a range, convert it into an array containing the range cell values
If TypeName(Arrayname) = "Range" Then
GetArray = Arrayname.Value
'Otherwise simply allocate the array to GetArray
GetArray = Arrayname
' Check for a 1D array, or a base 0 array
On Error Resume Next
UBound2 = UBound(GetArray, 2)
' Convert to base 1
If UBound2 = 0 Then
LBound1 = LBound(GetArray)
UBound1 = UBound(GetArray)
ReDim TempA(1 To 1, 1 To UBound1 - LBound1 + 1)
j = 1
For i = LBound1 To UBound1
TempA(1, j) = Arrayname(i)
j = j + 1
GetArray = TempA