Excel 2019 Bible

Part VI: Automating Excel

FIGURE 42.5

A simple VBA procedure

A workbook that contains this macro is available on this book’s website at http://www.wiley.com/go/

excel2019bible. 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 list of arguments inside of parentheses. The parentheses

are required even if the procedure doesn’t use arguments, like this example. The End Sub^

statement signals the end of the procedure. The lines in between Sub and End Sub make

up the procedure’s code.

The CurrentDate macro also includes a comment. Comments are simply notes to yourself,

and VBA ignores them. A comment line begins with an apostrophe. You can also put a com-

ment in the same line as a statement. In other words, when VBA encounters an apostrophe,

it ignores the rest of the text in the line.

You execute a VBA Sub procedure in any of the following ways:

■ Choose Developer ➪ Code ➪ Macros (or press Alt+F8) to display the Macro dia-

log box. Select the procedure name from the list and then click Run.

■ Assign the macro to a control in the Quick Access toolbar or to a control in the

Ribbon.

■ Press the procedure’s shortcut key combination (if it has one).

■ (^) Click a button or other shape that has a macro assigned to it.
■ If the VBE is active, move the cursor anywhere within the code and press F5.
■ (^) Execute the procedure by calling it from another VBA procedure.
■ Enter the procedure name in the Immediate window in the VBE.
VBA functions
The second type of VBA procedure is a function. A function returns a single value (just as
a worksheet function always returns a single value). A VBA function can be executed by