Excel® 2010 Power Programming with VBA

Chapter 10: Creating Function Procedures

IN THIS CHAPTER

• 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 ...