VBA Programming Series...9

Subs versus Functions

The VBA code that you write in the Visual Basic Editor is known as a proce-dure. The two most common types of procedures are Sub and Function.

✓ A Sub procedure is a group of VBA statements that performs an action (or actions) with Excel.

✓ A Function procedure is a group of VBA statements that performs a cal-culation and returns a single value.

Most of the macros you write in VBA are Sub procedures. You can think of a Sub procedure as being like a command: Execute the Sub procedure and something happens. (Of course, exactly what happens depends on the Sub procedure’s VBA code.)

A Function is also a procedure, but it’s quite different from a Sub. You’re already familiar with the concept of a function. Excel includes many work-sheet functions that you use every day (well, at least every weekday). Examples include SUM, PMT, and VLOOKUP. You use these worksheet func-tions in formulas. Each function takes one or more arguments (although a few functions don’t use any arguments). The function does some behind-the-scenes calculations using those arguments, and then it returns a single value. The same goes for Function procedures that you develop with VBA.

Looking at Sub procedures

Every Sub procedure starts with the keyword Sub and ends with an End Sub statement. Here’s an example:

Sub ShowMessage()

MsgBox “That’s all folks!”

End Sub

This example shows a procedure named ShowMessage. A set of parentheses follows the procedure’s name. In most cases, these parentheses are empty. However, you may pass arguments to Sub procedures from other proce-dures. If your Sub uses arguments, list them between the parentheses.

When you record a macro with the Excel macro recorder, the result is always a Sub procedure.

As you see later in this article, Excel provides quite a few ways to execute a VBA Sub procedure.

Looking at Function procedures

Every Function procedure starts with the keyword Function and ends with an

End Function statement. Here’s a simple example:

Function CubeRoot(number)

CubeRoot = number ^ (1 / 3)

End Function

This function, named CubeRoot, takes one argument (named number), which is enclosed in parentheses. Functions can have any number of arguments or none at all. When you execute the function, it returns a single value — the cube root of the argument passed to the function.

VBA allows you to specify what type of information (also known as data type) is returned by a Function procedure.

You can execute a Function procedure in only two ways. You can execute it from another procedure (a Sub or another Function procedure) or use it in a worksheet formula.

No matter how hard you try, you can’t use the Excel macro recorder to record a Function procedure. You must manually enter every Function procedure that you create.

Naming Subs and Functions

Like humans, pets, and hurricanes, every Sub and Function procedure must have a name. Although it is perfectly acceptable to name your dog Hairball Harris, it’s usually not a good idea to use such a freewheeling attitude when naming procedures. When naming procedures, you must follow a few rules:

✓ You can use letters, numbers, and some punctuation characters, but the first character must be a letter.

✓ You can’t use any spaces or periods in the name.

✓ VBA does not distinguish between uppercase and lowercase letters.

✓ You can’t embed any of the following characters in a procedure name: #, $, %, &, @, ^, *, or !.

✓ If you write a Function procedure for use in a formula, don’t use a name that looks like a cell address (for example, AK47). Actually, Excel allows such function names, but why make things more confusing than they are already?

✓ Procedure names can be no longer than 255 characters. (Of course, you would never make a procedure name this long.)

Ideally, a procedure’s name describes the routine’s purpose. A good prac-tice is to create a name by combining a verb and a noun — for example, ProcessData, PrintReport, Sort_Array, or CheckFilename.

Some programmers prefer using sentence-like names that provide a complete description of the procedure. Some examples include WriteReportToTextFile and Get_Print_Options_and_Print_Report. The use of such lengthy names has pros and cons. On the one hand, such names are descriptive and usually unambiguous. On the other hand, they take longer to type. Everyone devel-ops a naming style, but the main objectives are to make the names descrip-tive and to avoid meaningless names such as DoIt, Update, Fix, and Macro1.

Executing Sub procedures

Although you may not know much about developing Sub procedures at this point, I’m going to jump ahead a bit and discuss how to execute these pro-cedures. This is important because a Sub procedure is worthless unless you know how to execute it.

By the way, executing a Sub procedure means the same thing as running or calling a Sub procedure. You can use whatever terminology you like.

How do I run you? Let me count the ways. You can execute a VBA Sub in many ways — that’s one reason you can do so many useful things with Sub procedures. Here’s an exhaustive list of the ways (well, at least all the ways I could think of) to execute a Sub procedure:

✓ With the Run➪Run Sub/UserForm command (in the VBE). Excel executes the Sub procedure in which the cursor is located. This menu command has two alternatives: the F5 key and the Run Sub/UserForm button on the Standard toolbar in the VBE. These methods don’t work if the proce-dure requires one or more arguments.

✓ From Excel’s Macro dialog box. You open this box by choosing Developer➪Code➪Macros or by choosing View➪Macros➪Macros. Or bypass the Ribbon and just press the Alt+F8 shortcut key. When the Macro dialog box appears, select the Sub procedure you want and click Run. This dialog box lists only the procedures that don’t require an argument.

✓ Using the Ctrl+key shortcut assigned to the Sub procedure (assuming you assigned one).

✓ Clicking a button or a shape on a worksheet. The button or shape must have a Sub procedure assigned to it.

✓ From another Sub procedure that you write.

✓ From a button that you’ve added to the Quick Access toolbar. 

✓ From a custom item on the ribbon you develop.

✓ Automatically, when you open or close a workbook.

✓ When an event occurs. As I explain in , these events include saving the workbook, making a change to a cell, activating a sheet, and other things.

✓ From the Immediate window in the VBE. Just type the name of the Sub procedure and press Enter.

I demonstrate some of these techniques in the following sections. Before I can do that, you need to enter a Sub procedure into a VBA module.

1. Start with a new workbook.

2. Press Alt+F11 to activate the VBE.

3. Select the workbook in the Project window.

4. Choose Insert➪Module to insert a new module.

5. Enter the following into the module:

Sub CubeRoot()

Num = InputBox(“Enter a positive number”)

MsgBox Num ^ (1/3) & “ is the cube root.”

End Sub

This procedure asks the user for a number and then displays that number’s cube root in a message box. Figures-1 and 2 show what happens when you execute this procedure.

By the way, CubeRoot is not an example of a good macro. It doesn’t check for errors, so it fails easily. To see what I mean, try clicking the Cancel button in the input box or entering a negative number.