Excel® 2007 Power Programming with VBA

Chapter 10. Creating Function Procedures

In This Chapter

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

• The difference between Sub procedures and Function procedures

• How to create custom functions

• About Function procedures and function arguments

• How to create a function that emulates Excel’s SUM function

• How to debug functions, deal with the Insert Function dialog box, and use add-ins to store custom functions

• How to call the Windows Application Programming Interface (API) to perform otherwise impossible feats

VBA enables you to create Sub procedures and Function procedures. I cover Sub procedures in the preceding chapter, and in this chapter I discuss Function procedures.

Cross-Reference

Chapter 11 has many useful and practical examples of Function procedures. You can incorporate many of these techniques into your work.

Sub Procedures versus Function Procedures

You can think of a Sub procedure as a command that can be executed either by the user or by another procedure. 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 can be used in two situations:

• As part ...