Using Custom Functions

I've covered VBA functions and Excel worksheet functions. The third category of functions you can use in your VBA procedures is custom functions. A custom function is one you develop yourself using (what else?) VBA. To use a custom function, you must define it in the workbook in which you use it.

Here's an example of defining a simple Function procedure and then using it in a VBA Sub procedure:

Function MultiplyTwo(num1, num2)

MultiplyTwo = num1 * num2 End Function

Sub ShowResult()

Dim n1 As Double, n2 As Double Dim Result As Double n1 = 123 n2 = 544

Result = MultiplyTwo(n1, n2) MsgBox Result End Sub

The custom function MultiplyTwo has two arguments. The ShowResult Sub procedure uses this Function procedure by passing two arguments to it (in parentheses). The ShowResult procedure then displays a message box showing the value returned by the MultiplyTwo function.

The MultiplyTwo function is fairly useless. It's much more efficient to perform the multiplication in the ShowResult Sub procedure. I include it simply to give you an idea of how a Sub procedure can make use of a custom function.

You can use custom functions also in your worksheet formulas. For example, if MultiplyTwo is defined in your workbook, you can write a formula such as this one:

=MultiplyTwo(A1,A2)

This formula returns the product of the values in cells A1 and A2.

Custom worksheet functions is an important (and very useful) topic. So important (and useful) that I devote an entire chapter to it. See Chapter 21.

0 0

Post a comment