Creating User-Defined Functions with Excel VBA

In addition to the Sub procedure, VBA also provides the Function procedure (or simply, function).

[Public | Private | Friend] [Static] Function <FunctionName> _
    [(<arguments>)] [As Type]
    <instructions>
    <FunctionName> = expression
    [Exit Function]
    <instructions>
    <FunctionName> = expression
End Function

The syntax of a Function procedure contains the same elements as a Sub procedure. The instruction Exit Function causes an immediate exit from the function procedure.

Like a Sub, a function is an independent procedure that can accept parameter values, execute a sequence of instructions, and modify its parameter values. However, unlike a Sub, when it is necessary to use the value returned by a function, a Function procedure can be used on the right-hand side of an expression, just like any built-in function (for example, Cos).

A function procedure is called in an expression by its name, followed by a list of arguments enclosed in parentheses. To return a value from a function, assign a value to the function name. Any number of such assignment instructions may appear anywhere in the procedure.

In the code an example of a function F is shown, which calculates the sum of two values.

Example of a function

Sub DemoFun()
    MsgBox F(1, 3)
End Sub

Function F(x As Double, y As Double) As Double
    F = x + y
End Function

In Example of a function the value of the function

function1 = x^2 + sin(x + z)

is calculated for the given x and z, which can be entered into worksheet cells.

Listing 2.26. Example of a function

Function function1(x As Double, z As Double) As Double
    function1 = x ^ 2 + Sin(x + z)
End Function

Note that the user-defined functions you create become available in the list of functions when entering formulas into an Excel worksheet cell.

To use a function you have created, go to the Formulas tab on the Ribbon, and in the Function Library group, click the Insert Function button. In the Insert Function dialog box , select User Defined from the Category list, and then, in the Select a function field, specify the function you created.

Insert Function dialog box