Excel 2019 Bible

Chapter 42: Introducing Visual Basic for Applications

42

The Range object has both an Item property and a Value property. In some contexts, the

Item property is the default, while in others, it’s the Value property. The good news is

that VBA does a pretty good job of picking the right one.

Variables

Like all programming languages, VBA enables you to work with variables. In VBA (unlike in

some languages), you don’t need to declare variables explicitly before you use them in your

code (although doing so is definitely a good practice).

If your VBA module contains an Option Explicit statement at the top of the module, you must declare all vari-
ables in the module. Undeclared variables will result in a compile error, and your procedures will not run.

In the following example, the value in cell A1 on Sheet1 is assigned to a variable named

Rate:

Rate = Worksheets("Sheet1").Range("A1").Value

After the statement is executed, you can work with the variable Rate in other parts of

your VBA code.

Controlling execution

VBA uses many constructs that are found in most other programming languages. These

constructs are used to control the flow of execution. This section introduces a few of the

more common programming constructs.

The If-Then construct

One of the most important control structures in VBA is the If-Then construct, which gives

your applications decision-making capability. The basic syntax of the If-Then structure is

as follows:

If condition Then statements [Else elsestatements]

In plain English, if a condition is true, then a group of statements will be executed. If you

include the Else clause, then another group of statements will be executed if the condi-

tion is not true.

The following is an example (which doesn’t use the optional Else clause). This procedure

checks the active cell. If it contains a negative value, the cell’s font color is changed to red.

Otherwise, nothing happens:

Sub CheckCell()

If ActiveCell.Value < 0 Then ActiveCell.Font.Color = vbRed

End Sub