VBA Programming Series...38

Entering worksheet functions

You can’t use the Excel Paste Function dialog box to insert a worksheet func-tion into a VBA module. Instead, enter such functions the old-fashioned way: by hand. However, you can use the Paste Function dialog box to identify the function you want to use and find out about its arguments.

You can also take advantage of the VBE’s Auto List Members option, which displays a drop-down list of all worksheet functions. Just type Application. WorksheetFunction, followed by a period. Then you’ll see a list of the function you can use.

If this feature isn’t working, choose the VBE’s Tools➪Options command, click the Editor tab, and place a check mark next to Auto List Members.

More about Using Worksheet Functions

Newcomers to VBA often confuse VBA’s built-in functions and Excel’s work-book functions. A good rule to remember is that VBA doesn’t try to reinvent the wheel. For the most part, VBA doesn’t duplicate Excel worksheet functions.

For most worksheet functions that are unavailable as methods of the WorksheetFunction object, you can use an equivalent VBA built-in operator or function. For example, the MOD worksheet function is not available in the WorksheetFunction object because VBA has an equivalent, its built-in Mod operator.

Bottom line? If you need to use a function, first determine whether VBA has something that meets your needs. If not, check out the worksheet functions. If all else fails, you may be able to write a custom function by using VBA.

Using Custom Functions

I’ve covered VBA functions and Excel worksheet functions. The third cat-egory of functions you can use in your VBA procedures is custom functions. A custom function (also known as User Defined Function, UDF) is one you develop yourself by 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) As Double

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 show-ing the value returned by the MultiplyTwo function.

I probably don’t have to tell you that the MultiplyTwo function is fairly use-less. 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 also use custom functions 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.