Meet the Excel Visual Basic Macro Editor
The macros you recorded in the previous post are stored in an Excel file. To edit it you use what looks like a separate program, the Visual Basic Editor. Open it with the Developer ribbon (Code group) > Visual Basic Editor (Alt+F11).
You will find newly-recorded macros under Modules in the explorer on the left (Ctrl+R). Double-click the Module to open the Code window on the right.
The example shows the two macros you recorded in the exercise in the previous post. Both set Bold, Italics and Single Underline for the current cell, and then move one cell right. The top one was recorded Absolute, the lower, Relative.
For an explanation of Absolute and Relative, see this previous post.
1. Modifying Excel Visual Basic Code
The macro code is in the Visual Basic language. You will thus need to know something about Visual Basic if you wish to change the code in your Macros.
We will talk a lot more about Visual Basic in later posts. However, there is a shortcut…
The best way to learn how Excel does things is to record the action, and then examine the recorded code. For example, if Selection.Font.Bold = True sets Bold ON, what do you think sets it OFF?
How about italics?
Underline is more complicated. Remember that, on the Home ribbon, in the Font group, you have two choices of underline: Single and double. Here it is using xlUnderlineStyleSingle. Any guesses on how you would change it to do double underlining?
Select any word you want to learn more about, then press F1 for Visual Basic Help. For example, select Underline in the above example, and press F1. You should get online Help on the Font.Underline property. Look in the XlUnderlineStyle constants for the various underline styles available.
Comments, which start with a single quote (‘), shown in green, are for your benefit. The computer. ignores them. Comment your code copiously so that you will be able to understand it later.
Your code will probably not have the line Option Explicit at the top. This is an important statement to help make your code more bulletproof. In the next post, we will explain how it works and how to get it to appear automatically. For now, just add it manually.
Congratulations! You have edited your first Visual Basic code.
Now for a deeper look at the Details of The Excel Visual Basic Editor.