Returning Arrays From VBA User Defined Functions

Returning Arrays From VBA User Defined Functions
http://www.cpearson.com/excel/returningarraysfromvba.aspx

Introduction

If you often write your own User Defined Functions (UDFs) in VBA or in a COM or Automation Add-Ins (clickhere for information about writing your own functions in VBA; click here for information about writing COM Add-Ins, or click here for information about writing Automation Add-Ins), you have likely needed to or at least found it useful to return an array as the result of your VBA function. This allows the user to array enter your function (click here for information about array formulas) into a range of cells on the worksheet to display the contents of the array that is returned by your UDF.

This page looks at a few issue that might arise when you are returning arrays from UDFs.

Choosing The Right Return Array Size

At its simplest, the size of the returned array can be mandated by the function and require that the user use an array that size in order to get all the results. The Excel function LINEST function works this way. You must array-enter that function into a range of cells that is 5 rows tall and 2 columns wide. If you enter it into a larger range, Excel fills out the unused elements of the range with #N/A errors. If you enter it into a smaller range, you will not get all the values created by LINEST.

To mandate the size of the returned array, simply declare the array to that size and setting the result of the function to that array. For example,

    Function Test() As Variant
        Dim V() As Variant
        Dim N As Long
        Dim R As Long
        Dim C As Long
        ReDim V(1 To 3, 1 To 4)
        For R = 1 To 3
            For C = 1 To 4
                N = N + 1
                V(R, C) = N
            Next C
        Next R
        Test = V
    End Function

This function simply returns an array with 3 rows and 4 columns that contains the integers from 1 to 12. 

Returning such a fixed-size array can be useful if the number of results does not vary with the number and/or values of the inputs to the function. However, this is usually not the case.

In the majority of circumstances, if your UDF is going to return an array, that array will vary in size and the size will depend on any one or more of three things: the size of the range into which the UDF was entered, the number of elements passed into the function, and, of course, the nature and function of the UDF itself. The Application.Caller object, when used in a UDF called from a worksheet range, is a Range reference to the range from which your UDF was called.

CAUTION: Application.Caller will be a Range object only when the function in which it appears was called from a worksheet cell. If the function was called from another VB procedure, Application.Caller will be an Error-type Variant and most any attempt to use it will result in a Type Mismatch (13) error. If the code containingApplication.Caller was called via the OnAction property of a Shape object on a worksheet, Application.Caller will be a String containing the name of the sheet. Therefore, if your function might be called from another VB procedure rather than only from a worksheet cell, you should test Application.Caller with the IsObjectfunction to ensure that it is indeed an object before attempting to access any of its properties.

CAUTION: In Excel 2003, a new object, Application.ThisCell, was introduced. It is similar in nature to Application.Caller, but differs when a UDF is array entered into a range of more than one cell. Application.Caller will return the a Range reference to the entire range in which the UDF was array-entered.Application.ThisCell returns a reference to the first (upper left) cell in the range from which the UDF was called. Frankly, I'm not sure why Application.ThisCellwas introduced in the first place.

In the example code on this page, we will not test Application.Caller with IsObject. For simplicity and brevity, we will assume that the function is being called from a worksheet.

Using Application.Caller, you can determine the number of rows and columns from which the function was called. For example,

    Function Test()
        Dim CallerRows As Long
        Dim CallerCols As Long
        With Application.Caller
            CallerRows = .Rows.Count
            CallerCols = .Columns.Count
        End With
        Test = CallerRows * CallerCols
    End Function

In this code, the variables CallerRows and CallerCols get the number of rows and columns in the range from which the function was called. If you want to return an array the same size as the range from which the function was called, you can use code like the following:

Function Test() As Variant
    Dim CallerRows As Long
    Dim CallerCols As Long
    Dim CallerAddr As String
    Dim Result() As Long
    Dim N As Long
    Dim RowNdx As Long
    Dim ColNdx As Long
        
    With Application.Caller
        CallerRows = .Rows.Count
        CallerCols = .Columns.Count
    End With
    ReDim Result(1 To CallerRows, 1 To CallerCols) 
    For RowNdx = 1 To CallerRows
        For ColNdx = 1 To CallerCols
            N = N + 1
            Result(RowNdx, ColNdx) = N
        Next ColNdx
     Next RowNdx
     Test = Result
End Function

This function returns an array with the same dimensions as the range from which the function was called, and simply fills those elements with the first Rows*Columns integers.

You can, of course, ignore the size of the range from which the function was called and return an array with the dimensions required by your function's purpose. In this case, if the function is entered into a range smaller than the result array of the function, the result array is trunctated on the bottom and on the right to the size of the calling range. If the function was entered into a range larger than the size of the returned array, Excel fills the unused cells with #N/A values. This is the normal behavior of Excel's own array functions. For example, if you array enter =ROW(A1:A3) into cells B1:B5, the result will be 1 2 3 #N/A #N/A. The ROW(A1:A3)returns an array with only three elements, so Excel fills in the rest of the calling range with #N/A values.

Orienting An Array

If your UDF creates a 1-dimensional array as its result, it can orient the array as either a row vector or a column vector so that is will be properly displayed in the worksheet cells without requiring the user to wrap your UDF result in a TRANSPOSE function. If the function was called from a row vector of cells (e.g., A1:E1), it does not need to be transposed. If the function was called from a column vector of cells (e.g., A1:A5), the array needs to be transposed. The code below looks at Application.Caller.Rows.Count and if this is greater than 1, it tranposes the array before returning it to the caller. Note that this should be done only with single-dimensional arrays and only when the UDF is being called from a worksheet range. Therefore, you should first test Application.Caller with IsObject and then test Application.Caller.Rows.Countand Application.Caller.Columns.Count to test if it is being called from a row or column vector. For example,

    Function Test(NN As Long)
        Dim Result() As Long
        Dim N As Long
        ReDim Result(1 To NN)
        For N = 1 To NN
            Result(N) = N
        Next N
        If Application.Caller.Rows.Count > 1 Then
            Test = Application.Transpose(Result)
        Else
            Test = Result
        End If
    End Function