Part VI: Programming Excel with VBA
812
The basics: Entering and editing code
Before you can enter code, you must insert a VBA module into the workbook. If the workbook
already has a VBA module, you can use the existing module sheet for your new code.Use the following steps to insert a new VBA module:- Press Alt+F11 to activate the VB Editor 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, copy, cut, paste, and so on.How VBA works
VBA is by far the most complex feature in Excel, and you can easily get overwhelmed. To set the
stage for the details of VBA, here is a concise summary of how VBA works:When you enter code in 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 are 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”).ValueYou can insert comments freely into your VBA code. The comment indicator is an apostrophe single
quote character (‘). Any text that follows a single quote 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 16VBA Coding Tips