VBA uses many constructs that are found in most other programming languages. These constructs are used to control the flow of execution. In our this article, we shall introduce you with a few common VBA constructs.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide
Read More: How to Use the For Each Next Loop in Excel VBA
Table of Contents
The If-Then construct
If-Then is one of the most important control structures in VBA. With this construct, VBA applications can decide which statements to execute. The basic syntax of the If-Then structure is:
If condition Then statements [Else elsestatements]
In simple language, we can say, 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 condition 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 CheckActiveCell()
If ActiveCell.Value < 0 Then ActiveCell.Font.Color = vbRed
End Sub
Here’s another multi-line version of this procedure that uses an Else clause. Because it uses multiple lines, you must include an End If statement. This procedure colors the active cell text red if it’s a negative value, and green otherwise.
Sub CheckActiveCell()
If ActiveCell.Value < 0 Then
ActiveCell.Font.Color = vbRed
Else
ActiveCell.Font.Color = vbGreen
End If
End SubFor-Next loops
We use a For-Next loop to execute one or more statements for more than one time. The following is an example of a For-Next loop:
Sub SumOfSquaredNumber()
Total = 0
For Num = 1 To 10
Total = Total + (Num ^ 2)
Next Num
MsgBox Total
End SubThe following figure shows the SumOfSquaredNumber macro code and resultant pop-up dialog box showing the sum of 1 to 10 squared numbers.
The Dialog box is showing the sum of 1 to 10 squared numbers.
This macro code has one statement between the For statement and the Next statement. The statement is: Total = Total + (Num ^ 2). This single statement is executed ten times.
Read More: How to Use Do Until Loop in Excel VBA
How this code works:
We have discussed every line of the above macro to make you understand how this code works. This idea is very important. You will use this For-Next loop many times in your code.
- Sub SumOfSquaredNumber(): This statement starts the macro subprocedure.
- Total = 0: Total is a variable. We assign value 0 in this variable with this statement.
- For Num = 1 To 10: Num is also a variable and it is assigned now value 1. Excel checks Num’s assigned value (now, 1) with Num’s limiting value (here, 10). If Num’s assigned value is less than or equal to Num’s limiting value, then the statement between For and Next will execute.
- Total = Total + (Num ^ 2): Num variable’s value (right now 1) will be squared here at first. Excel then sums the square value of Num (1) and the value of Total (0). The result of the sum will be assigned to Total again. Finally Total variable now holds: 1 (0+1=1).
- Next Num: This statement increases the value of Num variable by 1. So Num’s value is now 1+1=2. Excel now checks again Num’s assigned value (2) with Num’s limiting value (10). As assigned value is less than limiting value, statement Total = Total + (Num ^ 2) executes again.
- Total = Total + (Num ^ 2): This statement now executes again. Num variable’s value (right now 2) will be squared here at first. Excel then sums the square value of Num (4) and the value of Total (1). The result of the sum will be assigned to Total again. Finally Total variable now holds 5 (4+1=5).
- Next Num: This statement increases the value of Num variable by 1. So Num’s value is now 2+1=3. Excel now checks again Num’s assigned value (3) with Num’s limiting value (10). As assigned value is less than limiting value, statement Total = Total + (Num ^ 2) executes again.
- Total = Total + (Num ^ 2): This statement now executes again. Num variable’s value (right now 3) will be squared here at first. Excel then sums the square value of Num (9) and the value of Total (5). The result of the sum will be assigned to Total again. Finally Total variable now holds 14 (9+5=14).
- In this way, statement Total = Total + (Num ^ 2) will be executed until Num’s value is equal to 10.
- MsgBox Total: This statement shows a pop-up dialog box with the value of Total (385).
- End Sub: This statement ends the macro subprocedure.
Read More: For-Next loops in Excel
The With-End With construct
This construct will help you to make short your code. Say you want to work with an object’s several properties or methods in your macro. With this construct, writing the object’s name just for once, you can mention more than one property or method of that object. The following example describes this idea:
Sub AlignCells()
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = xlHorizontal
End With
End SubThe following macro performs exactly the same operations but doesn’t use the With-End With construct:
Sub AlignCells() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.WrapText = False Selection.Orientation = xlHorizontal End Sub
The Select Case construct
The Select Case construct is useful for choosing among two or more options. The following example demonstrates the use of a Select Case construct. In this example, the active cell is checked. If its value is less than 0, it’s colored red. If it’s equal to 0, it’s colored blue. If the value is greater than 0, it’s colored black.
Sub CheckActiveCell()
Sub CheckActiveCell()
Select Case ActiveCell.Value
Case Is < 0
ActiveCell.Font.Color = vbRed
Case 0
ActiveCell.Font.Color = vbBlue
Case Is > 0
ActiveCell.Font.Color = vbGreen
End Select
End Sub
Any number of statements can go below each Case statement, and they all get executed if the case is true.
Happy Excelling 🙂
