Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Going Through the Visual Basics – Part 12

9 November 2018

We thought we’d run an elementary series going through the rudiments of Visual Basic for Applications (VBA) as a springboard for newer users.  This blog takes a look at sequential control structures.

 

In a programming, a control structure determines the order in which statements are executed.  Sequential execution is where each statement in the source code will be executed one by one in a sequential order.  This is the default mode of execution.  

Here are a couple of prime examples.

 

IF…THEN…ELSE
This is the most basic sequential control structure.  Identical to the IF function in Excel, this calls on the most basic decision path – execute steps if a condition is met, otherwise execute other steps.
 

If expression Then

 <statements>

Else

 <statements>

End If 

Here’s an example: 

Option Explicit

Sub IFStatement()

    Dim myNumber As Integer

    myNumber = 3

    If myNumber > 0 Then

        Debug.Print "Number is Positive!"

    Else

        Debug.Print "Number is Negative!"

    End If

End Sub 

Note however that the ELSE statement is not mandatory, which is the same as the IF function in Excel.  If no statements need to be executed should the condition prove false, then the ELSE section can be omitted entirely.

 

ELSE IF

Sometimes users are required to test for more than one condition. Quite often in Excel, one comes across multiple IF statements chained together in one formula. 

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

Often this is referred to as "nested IFs".  The idea of nesting comes from embedding or "nesting" one IF function inside another.  In VBA, this can be similarly achieved by using the ELSEIF statement.

The above example would be written in VBA as: 

IF condition1 THEN

   value_if_true1

ELSEIF condition2 THEN

   value_if_true2

ELSE

   value_if_false2

END IF 

Here’s a working VBA code sample: 

Option Explicit

Sub IFStatement()

    Dim myNumber As Integer

    myNumber = 3

    If myNumber < 10 Then

        Debug.Print "Number is single digit!"

    Else If myNumber < 100

        Debug.Print "Number is double digit!"

    Else

        Debug.Print "Number is very large!"

    End If

End Sub

Newsletter