Excel 2016 Power Programming with VBA

5 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 VBA Function is a procedure that performs calculations and returns a value. You can use these functions in your Visual Basic for Applications (VBA) code or in worksheet 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 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. The only exception is that you can’t use a VBA function ...