Excel 2010 Bible

Chapter 39: Introducing Visual Basic for Applications

799

Two Types of VBA Macros

Before getting into the details of creating macros, you need to understand a key distinction. A VBA

macro (also known as a procedure) can be one of two types: a Sub or a Function. The next two

sections discuss the difference.

VBA Sub procedures

You can think of a Sub procedure as a new command that either the user or another macro can

execute. You can have any number of Sub procedures in an Excel workbook. Figure 39.5 shows a

simple VBA Sub procedure. When this code is executed, VBA inserts the current date into the

active cell, applies a number format, makes the cell bold, and then adjusts the column width.

FIGURE 39.5

A simple VBA procedure.

On the CD
A workbook that contains this macro is available on the companion CD-ROM. It also includes a button that
makes it easy to execute the macro. The file is named current date.xlsm.

Sub procedures always start with the keyword Sub, the macro’s name (every macro must have a

unique name), and then a pair of parentheses. (The parentheses are required; they’re empty unless

the procedure uses one or more arguments.) The End Sub statement signals the end of the proce-

dure. The lines in between comprise the procedure’s code.

In a word, nothing. Beginning with Excel 2007, Microsoft made many changes to Excel. However, the

VB Editor has remained untouched. The VBA language has been updated to accommodate the new

Excel features, but the VB Editor has no new features, and the toolbars and menus work exactly like

they always have.

What’s New in the Visual Basic Editor?