Ranges and Arrays

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)
  End If' 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
  Else
  GetArray = Arrayname
  End If

' 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
  Next i

GetArray = TempA
  End If

End Function