VBA controlling code execution (1)

VBA's If...Then...Else statement

In this module:

1. a review of the Excel IF function
2. an example of the Excel 2016 IFS function, and
3. VBA's If...Then...Else statement

This section provides an review of the Excel IF function. The main examples are based on calculation of the amount of tax payable for an Australian resident individual. The taxation rates are shown in table 1.

Table 1: Individual income tax rates - Australian residents - Tax rates 2015-16. Source: https://www.ato.gov.au/Rates/Individual-income-tax-rates/ accessed 10 September 2015

1. A review of Excel's IF function

The Australian Taxation Office table of tax levels is applied to a series of nested IF functions - shown in the formula bar of the worksheet in figure 1.

Using Excel IF

About figure 1:

• The active cell is displayed in edit mode (by selecting the cell and then clicking in the formula bar)
• The tax table (table 1) has 5 levels of taxation rate
• There are 4 nested IF functions, plus the return value for the FALSE argument in the last IF statement. This is equivalent to the Else condition in the If...Then...Else statement, discussed below
• Nested IF functions are executed from left to right
• The IF sequence exits when the first TRUE logical test is found. Any remaining logical tests are ignored
• All IF functions in figure 1 refer to a range named TaxInc. The use of this name, in relative reference form, is designed to enhance readability of the formula
• Range name with relative referencing: The TaxInc name uses a relative reference, rather than the conventional absolute address of a defined name
• For example, when the active cell is cell H5, the TaxInc range name Refers to: =Sheet1!G5. This is a relative reference to G5. In other words, the TaxInc name is one cell to the left of the active cell, or one cell to the left of any cell where it is a reference . Using A1 style allows the reference in the formula to appear in color, and the referenced cell(s) to have a corresponding colored outline
• The R1C1 reference style is more understandable, but does not provide reference and cell color formatting in edit mode
• The R1C1 reference style version Refers to: =INDIRECT("RC[-1]",FALSE), where a FALSE value as the second argument means that ref_text is interpreted as an R1C1-style reference
• The nested IF structure can also be presented over multiple lines in the formula bar - see figure 2

About figures 2 and 3:

• Formula with line breaks: - figure 2 - to add a line-break, select the position of the break whilst in Edit mode, then press Alt+Enter
• Line breaks and appropriate white space, using the space bar, can enhance the readability of the formula
• Formula with comments: - figure 3 - to add a comment, use the N function. Syntax: N(value)
• The structure in figure 3 is similar to that of a VBA If...Then...Else statement discussed in section 2

Including Alt+Enter in VBA

Sub Demo_AltEnter()

    [B2].Formula = "=1+2" & Chr(10) & "+3"
    Range("D2").FormulaR1C1 = "=RC[-2]" & Chr(10) & "/ 5" & Chr(10) & "+ 0.8"

End Sub										

Syntax IF and IFS

2. The VBA If...Then...Else statement

The block version of the statement is contained within the If...End If key words. Highlighted lines 8 and 14.

'' ========================================
'' If...Then...Else Statement
''
'' Conditionally executes a group of statements, depending on the value of an expression.
''
'' SYNTAX - block - multiple line
''
'' If condition [ Then ]
''    [ statements ]
'' [ ElseIf elseifcondition [ Then ]
''    [ elseifstatements ] ]
'' [ Else
''    [ elsestatements ] ]
'' End If
''
'' - or - single line format
'' If Condition Then [ statements ] [ Else [ elsestatements ] ]
'' ========================================												

Optional parameters or arguments are shown by the square brackets []

Using VBA If...Then...Else statement

Using a sub procedure allows execution of the procedure with the F5 key. Output is sent to a Message Box

Sub IfThenTax()
'' declare variables
Dim TaxInc As Long
Dim Tax As Double

'' assign a value 
TaxInc = 37001  '' <-- Edit value

'' start of If...Then...Else block
    If TaxInc <= 18200 Then
        Tax = TaxInc * 0
    ElseIf TaxInc <= 37000 Then
        Tax = (TaxInc - 18200) * 0.19
    ElseIf TaxInc <= 80000 Then
        Tax = 3572 + (TaxInc - 37000) * 0.325
    ElseIf TaxInc <= 180000 Then
        Tax = 17547 + (TaxInc - 80000) * 0.37
    Else
        Tax = 54547 + (TaxInc - 180000) * 0.45
    End If
'' end of If...Then...Else block

MsgBox "A Taxable Income of: " & Format(TaxInc, "Currency") & " incurs a tax liability of " & Format(Tax, "Currency")

End Sub										

Code 1 by line number:

1. Line 1: Procedure declaration: a Sub procedure named IfThenTax. No arguments - the parentheses () are empty
2. Line 2: A comment line - commences with an apostrophe
3. Line 3: Declare a variable named TaxInc as type Long. Its default value is 0. The maximum value of a VBA 7 long is 2,147,483,647; ie. 2^30 with base zero. Taxable income is in whole dollars.
4. Line 4: Declare a variable named Tax as type Double. Its default value is 0. A double precision floating point number
5. Line 6: A comment line
6. Line 7: Assign the value 37,001 to the variable named TaxInc
7. Line 9: A comment line
8. Line 10: The first logical test, will return false, execution skips to line 12
9. Line 12: The second logical test, will return false, execution skips to line 14
10. Line 14: The third logical test, will return true, execution continues at line 15
11. Line 15: Assigns the value of 3572 + (37001 - 37000) * 0.325 = 3572.33 to the Tax variable. The structure is complete, so the next line is line 20
12. Line 20: The last line of the block
13. Line 23: The MsgBox statement displays a message box in Excel - see figure 4 for formatted output. MsgBox uses the concatenation operator &, and the VBA Format function

If...Then...Else as a function

A function procedure to return the amount of tax payable. The decision statement uses an If...Then...Else construct

Function TaxP2016(TaxInc As Long) As Double
Dim Tax As Double
    If TaxInc <= 18200 Then
        Tax = TaxInc * 0
    ElseIf TaxInc <= 37000 Then
        Tax = (TaxInc - 18200) * 0.19
    ElseIf TaxInc <= 80000 Then
        Tax = 3572 + (TaxInc - 37000) * 0.325
    ElseIf TaxInc <= 180000 Then
        Tax = 17547 + (TaxInc - 80000) * 0.37
    Else
        Tax = 54547 + (TaxInc - 180000) * 0.45
    End If
TaxP2016 = Tax
End Function								

Note: Naming the procedure in code 2 as Tax2016 will return a #REF! error in Excel, because Tax2016 is a valid cell address.

• This example was developed in Excel 2013 and excel 2016 :: VBA 7.1
• The VBA Chr function is equivalent to Excel's CHAR function.
• Download the VBA code for this module: xlf-if-then-else-code.txt [2 KB]
• Download the xlsx file for this module: xlf-tax-if-ifs.xlsx [11 KB]
• Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Time (AET)]

Copyright © 2011 – 2026 ♦ Ian O'Connor, Central Gippsland   |   Privacy policy

Comment & Reply policy