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
Pingback: Evaluate Function « Newton Excel Bach, not (just) an Excel Blog
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.
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.
Yes, please send a sample file to my gmail address (dougaj4)