Program by Levels

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple

VBA Lesson 2-1: VBA Code General Tips
(Levels: Intermediate)

There are more than 1,000,000 words in English and you use a few thousands daily. To become an advanced user and consultant in VBA for Excel you need to learn about 150 VBA words. Add 50 to work with userforms and the rest will be written for you by the Macro Recorder.

- All VBA procedures (macros) start with Sub with a set of parentheses at the end
Sub proTest()
In the parentheses you will submit variables that you carry from one procedure to the other. I always use the prefix "pro" at the beginning of a procedure and I use upper case letters at the beginning of a new word in the name of the procedure like in proTest above or like in proAddData

A VBA procedure always end with End Sub but you can always exit a procedure at any point with:
Exit Sub

- All VBA sentences must be on a single line. When you need to write long sentences of code and you want to force a line break to make it easier to read you must add a space and an underscore at the end of each line. Here is an example of a single sentence broken into 3 lines:

Range("A1:E9").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

- Do not hesitate to use the macro recorder to avoid typos.

- Write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't.... check your spelling.

- Note:Quotes within quotes must be doubled. For example:
MsgBox "My name is Peter" is OK and will result in: My name is Peter

But if you want the name Peter to be between quotes you cannot write:
MsgBox "My name is "Peter" "
you must double the quotes before and after Peter:
MsgBox "My name is "" Peter"""

- At a certain point you will realize how useful variables are in programming. When you start using variables always activate the "Option Explicit" . Although, you are forced to declare variables, there are many advantages. If you have the wrong spelling for your variable, VBE will tell you. You are always sure that your variables are considered by VBE.

Declare all your variables (Dim) at the beginning of the procedure, it will simplify the testing of your code.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple