This Page: www.cpearson.com/excel/writingfunctionsinvba.aspx

Last Updated: 06-May-2018

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 29-Apr-2018

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

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

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