Part VI: Automating Excel
This section is an introduction to VBA programming, which is essential if you want to write
(rather than record) VBA macros. It isn’t intended to be a complete instructional guide. Our
book Excel 2019 Power Programming with VBA (Wiley, 2018) covers all aspects of VBA and
advanced spreadsheet application development.
The basics: entering and editing code
Before you can enter code, you must insert a VBA module into the workbook. If the work-
book already has a VBA module, you can use the existing module sheet for your new code.
Follow these steps to insert a new VBA module:
- Press Alt+F11 to activate the VBE window. The Project window displays a list of
all open workbooks and add-ins.
- In the Project window, locate and select the workbook you’re working in.
- Choose Insert ➪ Module. VBA inserts a new (empty) module into the workbook and
displays it in the Code window.
A VBA module, which is displayed in a separate window, works like a text editor. You can
move through the sheet, select text, insert text, copy, cut, paste, and so on.
VBA Coding Tips
When you enter code into a module sheet, you’re free to use indenting and blank lines to make the
code more readable. In fact, this is an excellent habit.
After you enter a line of code (by pressing Enter), it’s evaluated for syntax errors. If none is found, the
line of code is reformatted, and colors are added to keywords and identifiers. This automatic reformat-
ting adds consistent spaces (before and after an equal sign, for example) and removes extra spaces
that aren’t needed. If a syntax error is found, you receive a pop-up message, and the line is displayed
in a different color (red, by default). You need to correct your error before you can execute the macro.
A single statement can be as long as you need. However, you may want to break the statement into two
or more lines. To do so, insert a space followed by an underscore (_). The following code, although
written as two lines, is actually a single VBA statement:
Sheets("Sheet1").Range("B1").Value = _
Sheets("Sheet1").Range("A1").Value
You can insert comments freely into your VBA code. The comment indicator is an apostrophe or single
quote character ('). Any text that follows a single quote on that line is ignored. A comment can be a
line by itself, or it can be inserted after a statement. The following examples show two comments:
' Assign the values to the variables
Rate = .085 'Rate as of November 16
