If-Then construct, For-Next loops, With-End With construct, Select Case construct in Excel

VBA uses many constructs that are found in most other programming languages. These constructs are used to control the flow of execution. In this article, we shall introduce you to a few common VBA constructs.

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide

Table of Contents

The If-Then construct
For-Next loops
- How this code works:
The With-End With construct
The Select Case construct
Related Articles

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 &amp;amp;lt; 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 &amp;amp;lt; 0 Then
      ActiveCell.Font.Color = vbRed
    Else
      ActiveCell.Font.Color = vbGreen
    End If
End Sub

For-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 Sub

The following figure shows the SumOfSquaredNumber macro code and resultant pop-up dialog box showing the sum of 1 to 10 squared numbers.

If-Then construct, For-Next loops, With-End With construct, Select Case construct in Excel

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.

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 the 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 the assigned value is less than the limiting value, the 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 the 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 the assigned value is less than the limiting value, the 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, the 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 Sub

The 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 &amp;amp;lt; 0
     ActiveCell.Font.Color = vbRed
  Case 0
     ActiveCell.Font.Color = vbBlue
  Case Is &amp;amp;gt; 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 🙂

Kawser

Hello!
Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!