Syntax Versus Grammar

Now that you know the basic elements of VBA (objects, properties, and methods), it's time to start using them. But how do you combine objects, properties, and methods into correct language structures? Every language has grammar rules that people follow in order to make themselves understood. Whether you speak English, Spanish, French, or any other language, you apply certain rules to your writing and speech. In programming, we use the term "syntax" to specify language rules. You can look up the syntax of each object, property, or method in the online help or in the Object Browser window.

Listed below are a few general Visual Basic rules you can't go without. To make Excel always understand what you mean, just stick to the following rules:

■ Rule #1: Referring to the property of an object

If the property does not have arguments, the syntax is as follows: Object.Property

Object is a placeholder. It is where you should place the name of the actual object that you are trying to access. Property is also a placeholder. Here you place the name of the object's characteristics. For example, to refer to the value entered in cell A4 on your worksheet, write the following instruction:

Object Property


Notice the period between the name of the object and its property. When you need to access the property of an object that is contained within several other objects, you must include the names of all objects in turn, separated by the dot operator, as shown below:


This example references the Weight property of the Line object and refers to the second object in the collection of Shapes located in the active worksheet.

Some properties require one or more arguments. For example, using the popular Offset property, you can select a cell relative to the active cell. The Offset property requires two arguments. The first argument indicates the row number (rowOffset), and the second one determines the column number (columnOffset).




ActiveCell.Offset(3, 2)

ActiveCell.Offset(3, 2)

In the example above, assuming the active cell is A1, Offset(3, 2) will reference the cell located three rows down and two columns to the right of cell A1. In other words, cell C4 is referenced.

Because the arguments placed between parentheses are often difficult to understand, it's common practice to precede the value of the argument with its name, as in the following example:

ActiveCell.Offset(rowOffset:=3, columnOffset:=2)

Notice that a colon and an equal sign always follow the named arguments (:=). When you use the named arguments, you can list them in any order. The above instruction can also be written as follows:

ActiveCell.Offset(columnOffset:=2, rowOffset:=3)

The revised instruction does not change the meaning; you are still referencing cell C4. However, if you transpose the arguments in ActiveCell.Offset(3, 2), you will end up referencing D3 instead of C4. ■ Rule #2: Changing the property of an object

Object.Property = Value

Value is a new value that you want to assign to the property of the object. The value can be: • A number

The above instruction enters the number 25 in cell A4 of the selected worksheet.

• Text entered in quotes

ActiveCell.Font.Name = "Times New Roman"

The above instruction changes the font of the active cell to Times New Roman.

ActiveCell.Font.Bold = True

The above instruction applies bold formatting to the active cell. ■ Rule #3: Returning the current value of the object property

Variable = Object.Property

Variable is the name of the storage location where Visual Basic is going to store the property setting. You will learn about variables in Chapter 3.

CellValue = Range("A4").Value

CellValue = Range("A4").Value

The above instruction saves the current value of cell A4 in the variable named CellValue.

■ Rule #4: Referring to the object's method

If the method does not have arguments, the syntax is as follows: Object.Method

Object is a placeholder. It is where you should place the name of the actual object that you are trying to access. Method is also a placeholder. Here you place the name of the action you want to perform on the object. For example, to clear the formatting in cell A4, use the following instruction:




If the method can take arguments, the syntax is as follows:

Object.Method (argumentl, argument2, ... argumentN)

For example, using the GoTo method, you can quickly select any range in a workbook. The syntax of the GoTo method is:

Object.GoTo(Reference, Scroll)

The Reference argument is the destination cell or range. The Scroll argument can be set to True to scroll through the window or to False to not scroll through the window.

For example, the following VBA statement selects cell P100 in Sheetl and scrolls through the window:

Application.GoTo _

Reference:=Worksheets("Sheet1").Range("P100"), _ Scroll:=True

The above instruction did not fit on one line, so it was broken into sections using the special line continuation character (the underscore), as described in the next section.

0 0

Post a comment