Chapter 10: Creating Function Procedures


Understanding the difference between Sub procedures and Function procedures

Creating custom functions

Looking at Function procedures and function arguments

Creating a function that emulates Excel's SUM function

Using functions that enable you to work with pre-1900 dates in your worksheets

Debugging functions, dealing with the Insert Function dialog box, and using add-ins to store custom functions

Calling the Windows Application Programming Interface (API) to perform otherwise impossible feats

Sub Procedures versus Function Procedures

A function is a VBA procedure that performs calculations and returns a value. You can use these functions in your Visual Basic for Applications (VBA) code or in formulas.

VBA enables you to create Sub procedures and Function procedures. You can think of a Sub procedure as a command that either the user or another procedure can execute. Function procedures, on the other hand, usually return a single value (or an array), just like Excel worksheet functions and VBA built-in functions. As with built-in functions, your Function procedures can use arguments.

Function procedures are quite versatile, and you can use them in two situations:

As part of an expression in a VBA procedure

In formulas that you create in a worksheet

In fact, you can use a Function procedure anywhere that you can use an Excel worksheet function or a VBA built-in function. As far as I know, the only exception ...

Get Excel® 2010 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.