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

This entry was posted in Arrays, Excel, UDFs and tagged , , , . Bookmark the permalink.

4 Responses to Ranges and Arrays

  1. Pingback: Evaluate Function « Newton Excel Bach, not (just) an Excel Blog

  2. Johann de Boer says:

    Many thanks for sharing this function! It was just what I was after, and really simplifies the problem of being able to interchange between ranges and arrays easily.


  3. jan martens says:

    Hi, thanks for this practical function. When used on the worksheet (in an index formula) all is perfect. I have a problem, when used in vba. My aim is to append named ranges of different lengths . The name manager is looped, when a named range is selected, the name is passed to the get array function, who writes the array with the named range values to the spreadsheet. All goes well except for the writing, I get only the name of the named ranges on the spreadsheet . It would be great if you helped me out. At your invitation, I can send a file. Have a good day.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.