| As I mentioned at the start of this chapter, recording macros is limiting because there are plenty of macro features that you can't access with mouse or keyboard actions or by selecting menu options. In Excel, for example, VBA has a couple dozen information macro functions that return data about cells , worksheets, workspaces, and more. Also, the VBA control functions enable you to add true programming structures, such as looping, branching, and decision-making (see Chapter 6, "Controlling Your VBA Code").  | Although this section tells you how to create VBA macros, I realize there's an inherent paradox here: How can you write your own macros when you haven't learned anything about them yet? Making you familiar with VBA's statements and functions is the job of the next four chapters of the book. This section will get you started, and you can use this knowledge as a base on which to build your VBA skills in the chapters that follow. |
To access these macro elements, you need to write your own VBA routines from scratch. This is easier than it sounds because all you really need to do is enter a series of statements in a module. With a module window open and active, follow these steps to write your own command macro: -
Place the insertion point where you want to start the macro. (Make sure the insertion point isn't inside an existing macro.) -
If you want to begin your macro with a few notescalled comments in programming parlancethat describe what the macro does, type an apostrophe ( ' ) at the beginning of each comment line. tip  | To make your code easier to read, you should indent each statement by pressing the Tab key at the beginning of the line. (Don't do this for the Sub and End Sub lines, just the statements that go between them.) Conveniently, VBA preserves the indentation on subsequent lines, so you only have to indent the first line. | -
To start the macro, type Sub , followed by a space and the name of the macro. The name should consist of only letters , numbers , or the underscore character (_). Don't use spaces or any other characters in the name. -
Press Enter. VBA automatically adds a pair of parentheses at the end of the macro name. It also tacks on an End Sub line to mark the end of the procedure. -
Between the Sub and End Sub lines, type the VBA statements you want to include in the macro. Each time you press Enter to start a new line, VBA analyzes the line you just entered and performs three chores: -
It formats the color of each word in the line: by default, VBA keywords are blue, comments are green, errors are red, and all other text is black. -
VBA keywords are converted to their proper case. For example, if you type end sub , VBA converts this to End Sub when you press Enter. -
It checks for syntax errors , which are errors that occur when a word is misspelled , a function is entered incorrectly, and so on. VBA signifies a syntax error either by displaying a dialog box to let you know what the problem is or by not converting a word to its proper case or color. tip | | By always entering VBA keywords in lowercase letters, you'll be able to catch typing errors by looking for those keywords that VBA doesn't recognize (in other words, the ones that remain in lowercase). |
Let's run through a simple example: -
Type sub Hello_World and press Enter. -
Press Tab, type msgbox "Hello VBA World!" , and press Enter. The macro should appear as shown in Figure 2.3. The code you entered in Step 2 is an example of a VBA statement : an instruction that gives VBA a specific task. In this case, the statement contains VBA's MsgBox function, which is used to display a simple dialog box to the user. (See Chapter 12, "Interacting with the User ," for details.) Figure 2.3. The example macro, ready for execution.  |
