Writing Your Own Functions In VBA

This page describes how to write your own worksheet functions in VBA.

While Excel provides a plethora of built-in functions, especially so if you include functions in the Analysis Took Pack (in Excel 2007, the functions that used to be in the ATP are now native Excel functions) you may find it useful to create your own custom function for things that Excel cannot (easily) do with the built-in functions. While it takes longer for Excel to calculate a VBA function than it does to calculate a worksheet formula, all else being equal, the flexibility of VBA often makes a VBA function the better choice. The rest of this page assumes that you are familiar with the basics of VBA programming.

Simple User Defined Functions

A User Defined Function (or UDF) is a Function procedure that typically (but not necessarily) accepts some inputs and returns a result. A UDF can only return a value to the cell(s) whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell. In Excel 97 and 2000, a UDF cannot use the Find method of a Range object, even though that method does not change anything in Excel. This was fixed with Excel 2002.
The following is an example of a simple UDF that calculates the area of a rectangle:

Function RectangleArea(Height As Double, Width As Double) As Double
    RectangleArea = Height * Width
End Function

This function takes as inputs two Double type variables, Height and Width, and returns a Double as its result. Once you have defined the UDF in a code module, you can call it from a worksheet cell with a formula like:

=RectangleArea(A1,B1)

where A1 and B1 contain the Height and Width of the rectangle.

Because functions take inputs and return a value, they are not displayed in the list of procedures in the Macros dialog.

Where To Put The Code

The code for a UDF should be placed in a standard code module, not one of the Sheet modules and not in the ThisWorkbook module. In the VBA editor, go to the Insert menu and choose Module. This will insert a new code module into the project. A module can contain any number functions, so you can put many functions into a single code module. You can change the name of a module from Module1 to something more meaningful by pressing the F4 key to display the Properties window and changing the Name property to whatever you want.

You can call a function from the same workbook by using just the function name. For example:

=RectangleArea(12,34)

It is possible, but strongly recommended against, to have two functions with the same name is two separate code modules within the same workbook. You would call them using the module name from cells with formulas like:

=Module1.MyFunction(123)
=Module2.MyFunction(123)


Doing this will lead only to confusion, so just because it is possible doesn't mean you should do it. Don't do it.

Do not give the same name to both a module and a function (regardless of whether that module contains that function). Doing so will cause an untrappable error.

You can call a UDF that is contained in another (open) workbook by using the workbook name in the formula. For example,

='MyBook.xls'!RectangleArea(A1,A2)

will call the function RectangleArea defined in the workbook MyBook.xls. If a function is defined in an Add-In (either an XLA or an Automation Add-In; see this page for information about writing Automation Add-Ins in VB6), you don't need to include the name of the Add-In file. The function name alone is sufficient for calling a function in an Add-In.

CAUTION: Excel does not handle well the case when a workbook contains a function with the same name as a function in an Add-In. Suppose both Book1.xls and MyAddIn.xla have a function named Test defined as:

Function Test() As String
    Test = ThisWorkbook.Name
End Function

The function Test in each workbook simply returns the name of the workbook in which the code resides, so the function Test defined in Book1.xls returns the string "Book1.xls" and the function Test defined in MyAddIn.xla returns the string "MyAddIn.xla". In Book1.xls, enter the formula =Test() in cell A1 and enter the formula =MyAddin.xla!Test() in cell A2. The functions will work properly when you first enter the formulas, but if you edit the formula in A2 (e.g., select the cell, then press the F2 key followed by the ENTER key), the name Test is recognized as a function in Book1.xls so Excel will change the function call in cell A2 from =MyAddIn.xla!Test() to simply =Test(), and this will call the function Test from Book1.xls not MyAddIn.xla. This will almost certainly return an incorrect result. This problem occurs only when the workbook and an Add-In both have a function with the same name. It does not occurs if two workbooks have functions with the same name. This has not been fixed in Excel 2007.

UDFs And Calcuations

As a general rule, you should pass into the function all the values it needs to properly calculate the result. That means that your UDF should not make explicit refences to other cells. If you reference other cells directly from within the function, Excel may not recalculate the function when that cell is changed. For example, a poorly written UDF is as follows:

Public Function BadRectangleArea(Height As Double) As Double
    BadRectangleArea = Height * Range("A1").Value
End Function

In this function, the Width is assumed to be in cell A1. The problem here is that Excel doesn't know that this function depends on cell A1 and therefore will not recalculate the formula when A1 is changed. Thus, the cell calling the function call will not contain the correct result when cell A1 is changed. You can force Excel to recalculate a UDF whenever any calculation is made by adding the line

Application.Volatile True

as the first line in the function. For example,

Function BadRectangleArea(Height As Double) As Double
    Application.Volatile True
    BadRectangleArea = Height * Range("A1").Value
End Function

This has the drawback, however, that the function is recalculated even if it doesn't need to be recalculated, which can cause a performance problem. In general, you shouldn't use Application.Volatile but instead design your UDF to accept as inputs everything it needs to properly caclulate the result.

Returning Arrays From Functions

See the Returning Arrays From User Defined Functions page for information about returning arrays as the result of your User Defined Function.

Returning Errors From Functions

You can return an error value from a UDF if an incorrect input parameter is passed in. To do this, the function must return a Variant data type and use the CVErr function to create an error-type Variant result. For example, the function Divide below will return a #DIV/0 error if the divisor is 0.

Function Divide(A As Double, B As Double) As Variant
    If B = 0 Then
        Divide = CVErr(xlErrDiv0)
    Else
        Divide = A / B
    End If
End Function

You can use any of the following error constants with the CVErr function to return an error to Excel:

• xlErrDiv0 for a #DIV/0 error
  
• xlErrNA for a #N/A error
  
• xlErrName for a #NAME? error
  
• xlErrNull for a #NULL error
  
• xlErrNum for a #NUM error
  
• xlErrRef for a #REF error
  
• xlErrValue for a #VALUE error
  

If any other value is passed to CVErr, Excel will treat it as a #VALUE error. It is generally good practice to validate the input parameters and return an error value with CVErr rather than letting the VBA code error out with #VALUE errors. If a run-time error occurs in your code, or you attempt to change anything in Excel, such other cells, VBA terminates the function and returns a #VALUE error to Excel.

Determining The Range From Which Your UDF Was Called

Under nearly all circumstances, it is not necessary to know the actual address of the range from which your UDF was called. Indeed, you should avoid have the need for such information. Your function should work the same regardless of where it was called from. However, you may well need to know the size of the range from which your UDF was called if it was array entered into a range of cells. The Application.Caller object will return a reference to the range from which your function was called, regardless of whether that range is a single cell or a range of cells.

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 containing Application.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 IsObject function 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.ThisCell was introduced in the first place.

You can get the properties of Application.Caller with code like the following:

    Function Test()
        Dim CallerRows As Long
        Dim CallerCols As Long
        Dim CallerAddr As String
        With Application.Caller
            CallerRows = .Rows.Count
            CallerCols = .Columns.Count
            CallerAddr = .Address
        End With
        Test = 1234
    End Function

Using A Variable Number Of Parameters

You can define a function to accept a variable number of parameters in one of two somewhat different ways. You can use a specified number of optional parameters, or you can allow the function to accept any number of parameters, including none at all, using a ParamArray Variant parameter. The two methods are mutually exclusive. You cannot use both optional parameters and a ParamArray in the same function.

Optional Variant Parameters

You can define one or more parameters as Optional Variant types. For example:

Function OptParam(D As Double, Optional B As Variant) As Variant
    If IsMissing(B) = True Then 
        OptParam = D
    Else
        If IsNumeric(B) = True Then 
            OptParam = D + B
        Else
            OptParam = CVErr(xlErrNum)
        End If
    End If
End Function

This function defines the parameter B as an optional Variant and uses the IsMissing function to determine whether the parameter was passed. The IsMissing function can be used only with Variant type parameters. If IsMissing is used with any other data type (e.g., a Long), it will return False. More than one parameter may be Optional, but those parameters must be the last parameters accepted by the function. That is, once one parameter is specified as Optional, all the parameters that follow it must also be optional. You cannot have a required parameter following an optional parameter. If a parameter is declared as Optional but is not a Variant (e.g, it is a String or a Long) and that parameter is omitted, the IsMissing function will return False and the default value for that data type (0 or empty string) will be used. You can specify a default value for an optional parameter that should be used if the parameter is omitted. For example, the parameter B in the function below is optional with a default value of 2.

Function FF(A As Long, Optional B As Long = 2) As Variant
    If B = 0 Then
        FF = CVErr(xlErrDiv0)
    Else
        FF = A / B
    End If
End Function

In this code, the value 2 is used for the default value of B if B is omitted. When using a default value for a parameter, you don't call the IsMissing function. Your code should be written to use either a passed in parameter value or the default value of the parameter. With the code above, the following two worksheet functions are equivalent:

    =FF(1,2)
    =FF(1)

Variant ParamArray

The second method for working with optional parameters is to use a ParamArray Variant parameter. A ParamArray allows any number of parameters, including none at all, to be passed to the function. You can have one or more required parameters before the ParamArray, but you cannot have any optional parameters if you have a ParamArray. Moreover, the ParamArray variable must be the last parameter declared for a function. The ParamArray variables must be Variant types. You cannot have a ParamArray of other types, such as Long integers. If necessary, you should validate the values passed in the ParamArray, such as to ensure they are all numeric. If your function requires one or more inputs followed by a variable number of parameters, declare the required parameters explicitly and use a ParamArray only for the optional parameters. For example, the function SumOf below accepts any number of inputs and simply adds them up:

Function SumOf(ParamArray Nums() As Variant) As Variant
''''''''''''''''''''''''''''''''''
' Add up the numbers in Nums
''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim D As Double
    For N = LBound(Nums) To UBound(Nums)
        If IsNumeric(Nums(N)) = True Then
            D = D + Nums(N)
        Else
            SumOf = CVErr(xlErrNum)
            Exit Function
        End If
    Next N
    SumOf = D
End Function

In your function code, you can use:

Dim NumParams As Long
NumParams = UBound(Nums) - LBound(Nums) + 1

to determine how many parameters were passed in the ParamArray variable Nums. This will be 0 if no parameters were passed as the ParamArray. Of course, the code above counts the number of parameters within the ParamArray, not the total number of parameters to the function.

See Optional Paramateres To Procedures for a more in depth discussion of Optional parameters and ParamArray parameter type.

Returning Arrays From Functions

Your function can return an array of values so that it can be entered as an array formula, either entered into an array of cells or to return an array to be aggregated by a function like SUM. (See this page for a discussion of Array Formulas.) The NumsUpTo function below returns an array of the integers from 1 to the input parameter L. For simplicity, L must be between 1 and 5. The function also requires that if the function is array entered, it must be in either a single row or a single column. A range with more than one row and more than one column will result in a #REF error. This restriction applies to this example only; it is not a limitation on UDF array functions in general. See the next section for example code that return values to a two dimensional range of cells.

In a UDF, Application.Caller returns a Range type object that references the cell(s) from which the formula was called. Using this, we can test whether we need a row array or a column array. If the function is called from a column of cells (e.g., array entered into A1:A5), the VBA array must be transposed before returning it to Excel. Note that there is also an object named Application.ThisCell that references the cell from which a function is called. In functions called from a single cell, Application.Caller and Application.ThisCell work the same. However, they differ when a function is called as an array formula. You should use Application.Caller, not Application.ThisCell.

Function NumsUpTo(L As Long) As Variant
''''''''''''''''''''''''''''''''''''
' Add up the integers from 1 To L.
''''''''''''''''''''''''''''''''''''

Dim V() As Long
Dim ArraySize As Long
Dim N As Long
Dim ResultAsColumn As Boolean
''''''''''''''''''''''''''''''''''''
' Allow inputs only between 0 and 5.
''''''''''''''''''''''''''''''''''''
If (L > 5) Or (L < 1) Then
    NumsUpTo = CVErr(xlErrValue)
    Exit Function
End If

''''''''''''''''''''''''''''''''''''
' Allow only one columns or one row.
''''''''''''''''''''''''''''''''''''
If Application.Caller.Rows.Count > 1 And _
    Application.Caller.Columns.Count > 1 Then
        NumsUpTo = CVErr(xlErrRef)
        Exit Function
End If

'''''''''''''''''''''''''''''''''''''''
' Test whether the result should be
' returned as a columns or row array.
'''''''''''''''''''''''''''''''''''''''
If Application.Caller.Rows.Count > 1 Then
    ResultAsColumn = True
Else
    ResultAsColumn = False
End If

''''''''''''''''''''''''''''''
' ReDim the array to hold L elements.
''''''''''''''''''''''''''''''
ReDim V(1 To L)

''''''''''''''''''''''''''''''
' Fill up the array
''''''''''''''''''''''''''''''
For N = 1 To UBound(V)
    V(N) = N
Next N
'''''''''''''''''''''''''''''''''
' Return the result, transposing
' if necessary.
'''''''''''''''''''''''''''''''''
If ResultAsColumn = True Then
    NumsUpTo = Application.Transpose(V)
Else
    NumsUpTo = V
End If

End Function

If the SumUpTo function is called from a range that has more than one row, the array must be transposed before it is returned, using the Application.Transpose function. The result of the function is an array of L integers from 1 to L. If the range from which the function is called has N cells, and N is less than L (the size of the result array), elements at the end of array are discarded and only the firt N elements are sent to the cells. If L is less than N (the function is entered into an array of cells larger than L), #N/A errors fill out the ending elements of the range on the worksheet. Since the result of NumsUpTo is an array, it can be used in an array formula, such as

=SUM(NumsUpTo(5))

which returns 15, the sum of the numbers from 1 to 5.

Returning Arrays With Two Dimensions

To return an array to a range that contains more than one row and more than one column, create a two dimensional array with the first dimension equal to the number of rows in the range and the second dimension equal to the number of columns in the range. Then load that array, looping through the rows and columns and then return the array as the result.

The function AcrossThenDown below loads the calling cells with sequential integers, moving across each row and then moving down to the next row. The function DownThenAcross below loads the calling cells with sequential integers, moving down each column then moving right to the next column. The difference between the two function is in the For loops, whether the outer loop is for Rows or Columns. As noted before, use Application.Caller not Application.ThisCell to get a reference to the range of cells calling the function.

Function AcrossThenDown() As Variant
    Dim NumCols As Long
    Dim NumRows As Long
    Dim RowNdx As Long
    Dim ColNdx As Long
    Dim Result() As Variant
    Dim N As Long
    ''''''''''''''''''''''''''''''''''''''''''''
    ' Get the number of rows and columns in the 
    ' range that is calling this function.
    ''''''''''''''''''''''''''''''''''''''''''''
    NumCols = Application.Caller.Columns.Count
    NumRows = Application.Caller.Rows.Count

    ''''''''''''''''''''''''''''''''''''''''''''
    ' ReDim the Result array to the number 
    ' of rows and columns in the calling range.
    ''''''''''''''''''''''''''''''''''''''''''''
    ReDim Result(1 To NumRows, 1 To NumCols)
    
    For RowNdx = 1 To NumRows
        For ColNdx = 1 To NumCols
            N = N + 1
            Result(RowNdx, ColNdx) = N
        Next ColNdx
    Next RowNdx
    AcrossThenDown = Result
End Function


Function DownThenAcross() As Variant
    Dim NumCols As Long
    Dim NumRows As Long
    Dim RowNdx As Long
    Dim ColNdx As Long
    Dim Result() As Variant
    Dim N As Long
    
    ''''''''''''''''''''''''''''''''''''''''''''
    ' Get the number of rows and columns in the 
    ' range that is calling this function.
    ''''''''''''''''''''''''''''''''''''''''''''
    NumCols = Application.Caller.Columns.Count
    NumRows = Application.Caller.Rows.Count

    ''''''''''''''''''''''''''''''''''''''''''''
    ' ReDim the Result array to the number 
    ' of rows and columns in the calling range.
    ''''''''''''''''''''''''''''''''''''''''''''
    ReDim Result(1 To NumRows, 1 To NumCols)
    
    For ColNdx = 1 To NumCols
        For RowNdx = 1 To NumRows
            N = N + 1
            Result(RowNdx, ColNdx) = N
        Next RowNdx
    Next ColNdx
    DownThenAcross = Result
End Function

This page last updated: 1-Sept-2007