MS-Excel / General Formatting

---

Entering VBA Statements

Entering VBA statements is, on the surface, a straightforward matter: You type the code and then press Enter after each line. I also recommend that when you're beginning a command macro, press Tab before starting the first line. This indents your code, which makes it easier to read. (Don't do this for the Public Sub and End Sub lines, just the statements that go between them.) Conveniently, VBA preserves the indentation on subsequent lines, so you have to indent only the first line.

A comment is a special type of VBA statement that you use to describe something about your procedure. For example, many people add a few lines of comments before a procedure to describe what the procedure does. Most programmers (and all good programmers) augment their code with comments throughout the procedure to describe what statements are doing, the logical flow of the procedure, and so on. VBA does not execute comments; instead, you add them for your own or other people's benefit to clarify or make it possible to follow what a procedure does. Figure shows below a simple example of a commented procedure. The comments are the statements that begin with an apostrophe (').

At this early stage of your VBA programming career, I'd like to impress upon you the advantages to taking a neat, orderly approach to your programming. Humans can and do thrive in messy environments, but we're many times smarter and infinitely more intuitive than any macro. Procedures live in a world of strict and unyielding logic, and programming is always much easier if you supplement that logic with a sense of order. Fortunately, there are only two things you need to do to achieve most of the order you need to be a successful programmer: Indent your code and don't skimp on the comments.The latter is particularly important.Any procedure will be much easier to read (especially if you haven't looked at the code for a few months) if it's sprinkled liberally with comments throughout the code. Also, adding comments as you go is a great way of getting a grip on your own thoughts and logical leaps as you go.

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 msgbox "My Hero!", VBA converts this to MsgBox "My Hero!" when you press Enter.
• It checks for syntax errors, which are errors 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.

If you always enter 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).