VBA Chapter 10 of 24: VBA Code General Tips

You have learned how to create VBA procedures (macros) in a previous section. In this section you will find many small ones illustrating the different objects, methods and properties that you can use to make things happen in VBA for Excel. You will also discover statements, functions and variables.

Let's start by a few general tips.

- 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 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:
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.

No need for any VBA to generate bar codes. A simple font does it in Excel, Word or any other program. In bar code www.excel-vba.com looks like this www.excel-vba.com.

Find this specaial font "0Switchboard.xls"

Discover Even More in 50 Excel spreadsheets