Control Structures: Building the Logic of a Program with Excel VBA

As in all other programming languages, VBA has various control structures that allow you to change the order of program execution. If a program has no control structures, statements are executed sequentially from the first to the last. In the simplest cases, this may be sufficient. However, usually it is necessary to change the order of execution under certain conditions, to skip execution of some statements, or, conversely, to repeat them multiple times.

It should be noted that to implement any algorithms it is enough to have two types of control structures: loops and branches. Let us take a closer look at the use of statements in VBA.

Assignment Statement

The assignment statement assigns the value of an expression to a variable, constant, or object property. It always includes the equal sign (=).

<variable> = <expression>

The assignment statement evaluates the <expression> on its right-hand side and assigns the result to the <variable> on its left-hand side.

For example, the following two statements:

x = 2
x = x + 2

will assign the value 4 to the variable x.

Assigning an Object Reference

To assign an object reference to a variable, the keyword Set is used in the assignment statement.

For example, the following code assigns to variable r a reference to cell A1, and through this variable, the value 3 is entered into cell A1:

Dim r As Range
Set r = Range("A1")
r.Value = 3

In general, the Set statement has the following syntax:

Set <variable> = {[New] <expression> | Nothing}

• The keyword New is used when creating a new instance of a class.
• The keyword Nothing is used to release all system and memory resources allocated to the object being referenced (in simple terms, it removes the object from memory).

The With Statement

The With statement frees the programmer from the tedious task of repeatedly writing the same object name when working with its properties and methods. In addition, it structures the code, making it clearer:

With Range("A1")
    .Value = 3
    .Font.Italic = True
End With

Nested With statements are also allowed:

With Range("A1")
    .Value = 3
    With .Font
        .Italic = True
        .Size = 12
        .Bold = True
        .Color = RGB(255, 30, 255)
    End With
End With