The Elements of VBA Language in Excel

Comments
A comment is descriptive text embedded in your code and ignored by VBA. It is a good idea to use comments generously to describe what you are doing. Any text following an apostrophe (‘) in the program until the end of the line is ignored by the compiler and is a comment.
You can use an entire line for your comment, or insert a comment after a statement on the same line.

The following example shows a VBA procedure with two comments:

Sub DemoCommentaires()
  ' This is my first comment
  ValeurF = 12 ' This is my second comment
  MsgBox ValeurF
End Sub

NOTE
Excel VBA colors the line in green to indicate that it is a comment.

Although the apostrophe is the preferred comment indicator, you can also use the keyword Rem to mark a line as a comment. The keyword Rem (short for Remark) is essentially a legacy from older BASIC versions and is included in VBA for compatibility reasons. Unlike the apostrophe, Rem can only be written at the beginning of a line, not on the same line as another statement.

Some general tips for using comments effectively:

• Use comments to briefly describe the purpose of each procedure you write.
• Use comments to describe changes you make to a procedure.
• Use comments to indicate that you are using functions or designs in an unusual or non-standard way.
• Use comments to describe the purpose of variables so that you and others can decipher otherwise cryptic names.
• Write comments while you code, rather than after.

Line Break
To split a single statement into multiple lines, use the line continuation character, which is an underscore (_) at the point where you want the line to break. The underscore must be immediately preceded by a space. In short, to insert a VBA line break, you can follow these steps:

• First, click at the character where you want to break the line.
• Then, type a space.
• After that, type an underscore (_).
• Finally, press Enter to break the line.

To create a line break, the following conditions must be met:

• You cannot break keywords, properties, or methods.
• The maximum number of allowed line breaks is seven.

Here is a code snippet without a line break:

ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Value = Somme

Here is the same code with a line break:

ActiveCell.Offset(rowoffset:=0, _
columnoffset:=1).Value = Somme

You can use the line continuation character before or after:

• Operators (e.g., &, +, Like, NOT, AND)
• A comma
• An equal sign
• An assignment operator (:=)

You cannot use the line break between a colon and an equal sign. For example, the following usage is not recognized by Visual Basic:

Range("B1").Cut Destination: _
=Range("D1")

Additionally, you cannot use the line continuation character inside text enclosed in quotes. For example, the following is not valid:

MsgBox "To continue a long statement, use the line _ continuation character."

Instead, split it as follows:

MsgBox "To continue a long statement, use the " & _
"line continuation character."

Placing Multiple Statements on One Line
Using the colon (:) character allows you to place multiple statements on the same line. Thus, the two following constructions are equivalent:

maVariable = 5 ' maVariable contains 5
maVariable = maVariable + 5 ' maVariable now contains 10

' or

maVariable = 5: maVariable = maVariable + 5

The With / End With Statement
For each property we define in Excel, we need to reference the object to which it will be applied, even when the object is the same for multiple properties. The following example clearly shows the need to reference the object for each property:

Sub FormatCell()
  Range("B3").Value = 58.7
  Range("B3").Font.ColorIndex = 4
  Range("B3").NumberFormat = "0.00"
  Range("B3").Font.Bold = True
  Range("B3").Interior.ColorIndex = 6
End Sub

Comments

• The value 58.7 is set in cell B3 (.Value = 58.7)
• The font color is set to green (.Font.ColorIndex = 4)
• The number format is changed (.NumberFormat = "0.00")
• The content is bolded (.Font.Bold = True)
• The background color is set to yellow (.Interior.ColorIndex = 6)

To avoid repeating the same object, we can use With and End With. The referenced object must be placed right after the With statement. Syntax:

With Object
   Statement
End With

Example using With:

Sub FormatCell()
  With Range("B3")
    .Value = 58.7
    .Font.ColorIndex = 4
    .NumberFormat = "0.00"
    .Font.Bold = True
    .Interior.ColorIndex = 6
  End With
End Sub

Nested With statements are also possible:

Sub AlterSheet()
  With Sheets("Feuil2")
    With Range("B6")
      .Value = 12
      .Font.Bold = True
      .Font.ColorIndex = 4
    End With
    .Name = "mafeuille"
    .Move Before:=Sheets("Plan1")
    .Visible = True
  End With
End Sub

NOTE
Always consider proper indentation: the first and last lines of each block should align to the left. To refer to properties or methods in a hierarchy, start each statement with a period (.) to indicate continuation.

Avoid the Select Method When Possible
Accessing individual objects should be as direct as possible, without many intermediate steps. In many VBA applications, objects are accessed as follows:

• A workbook is activated.
• A worksheet is activated.
• A range or cell is selected.
• Properties/methods are applied to the selected item.

This approach is easy to understand but slower and more error-prone.

Example:

Sub ActivationSelection()
  ThisWorkbook.Activate
  Worksheets("Feuil1").Activate
  Range("A1:A10").Select
  Selection.Interior.Color = vbYellow
End Sub

Better approach with direct referencing:

Sub ActivationSelection()
  ThisWorkbook.Worksheets("Feuil1"). _
  Range("A1:A10").Interior.Color = vbYellow
End Sub

Another example (copy B1 to D1):

With Select:

Sub MethodeSelect()
  Range("B1").Select
  Selection.Cut
  Range("D1").Select
  ActiveSheet.Paste
End Sub

Better referencing:

Sub MethodeSelect()
  ThisWorkbook.Worksheets("Feuil1"). _
  Range("B1").Cut Destination:=ThisWorkbook. _
  Worksheets("Feuil1").Range("D1")
End Sub

Assignment Operator
This is the first operator you’ll use in any programming language. The assignment operator assigns the value of an expression to a variable, constant, or property of an object. It always includes an equal sign (=):

variable = expression

It acts like a bridge; the value on the right is transferred to the element on the left.

Example:

Sub AssignmentOperator()
  a = 10
  Range("A1").Value = a
End Sub

Comments

• The value 10 is assigned to variable a.
• Then, a is assigned to cell A1.

RGB() and QBColor() Functions
Color codes in VBA are often given as hexadecimal numbers. Instead of specifying a color directly using hexadecimal, it’s more practical to use RGB() or QBColor().

RGB(red, green, blue)

• Red: integer from 0 to 255
• Green: integer from 0 to 255
• Blue: integer from 0 to 255

QBColor(color)
Returns one of 16 standard colors based on a numeric argument between 0 and 15: