Custom Functions

One of the easiest ways to take advantage of VBA in Excel is to write custom functions.  Excel has a large number of built-in functions that you can use in spreadsheet formulas.  Examples include:

=Average("A4:A20")	Returns the average value in a range of cells
=Sum("A4:A20")	Returns the sum of a range of cells
=Cos(0.34)	Returns the cosine of a number

In general, a function takes one or more objects (values, ranges, etc.) as input and returns a single object (typically a value).  The things that are sent to a function as input are called arguments and the thing that is returned by a function is often called the return value.

In some cases, we may encounter situations where we need a function to do something but the function is not provided by Excel.  We can easily fix this problem by creating a custom function in VBA.

Format

The basic format for a custom function is as follows:

[Public] Function function_name(args) As Type

...

    function_name = ...

...

End Function

The Public statement is optional.  This means that the function can be called by VB code outside the module where the function is declared and from Excel formulas in cells.  If you omit the Public statement, the function is public by default (as opposed to Private).

The function_name is the name you provide to the function.  This can be any name you want, as long as you follow the same rules we use for defining VB variable names.

The args are the arguments to the function.  You can have any number of arguments.  The arguments are listed in the same way you declare variables, except that you omit the Dim part.  The args list serves two purposes: 1) it defines how many arguments are used, and 2) it defines the type for each argument.  The following are some sample argument lists:

(x As Double, n As Double)

(r As Range)

(str1 As String, str2 As String, num As Integer)

The Type part defines the type of object returned by the function.  Typical examples are Double, Integer, String, and Boolean.

Somewhere in the code, you must have line where you set the function name equal to a value.  You should think of the function name as a variable.  You must store the value returned by the function in the variable at some point before you hit the End Function statement.

There is one more important point, whenever you create a function that you want to use in an Excel formula, it should always be placed in a module under the Modules folder in the VBE.

Examples

Now let's look at some examples.  The following function takes two numbers as arguments and returns the minimum of the two numbers.  This basically duplicates the Min function provided by Excel, but it serves as a useful example:

Function my_min(a As Double, b As Double) As Double

    If a < b Then
        my_min = a
    Else
        my_min = b
    End If

End Function

Once this function is created, you can then use it in one of your Excel formulas as follows:

=my_min(A5, B7)

=my_min(Sum(C3:C10), 0)

Now let's look at something a little more complicated.  In many cases, we want our function to use a cell range as one of the arguments.  The following function returns the number of negative values in a range:

Function num_neg(r As Range) As Integer

    Dim c As Variant

For Each c In r

If c.Value < 0 Then
    num_neg = num_neg + 1
End If

Next c

End Function

The function could then be called from an Excel formula as follows:

=num_neg(A5:B7)

The next function takes two arguments: a range and an integer n.  It computes the sum of values in the range minus the lowest n values.  This function takes advantage of the standard Excel functions Sum and Small.  The Small function returns the n^th lowest value in a range.

Function dropsum(r As Range, n As Integer) As Double

Dim i As Integer

dropsum = Application.WorksheetFunction.Sum(r)

For i = 1 To n

    dropsum = dropsum - Application.WorksheetFunction.Small(r, i)

Next i

End Function

This function could then be used in an Excel formula as follows:

=dropsum(A5:B7, C10)

=dropsum(A5:B7, 5)

Functions in VB

Finally, it should be noted that you can call custom functions from other places in your VB code as well as from Excel formulas.  For example, you could use the my_min function defined above as follows:

Dim x As Double
Dim y As Double
Dim z As Double

x = ...
y = ...
...
z = my_min(x, y)
...