If...Then...Else statement

# 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.

Taxable Income Tax on this income
0 - $18,200 Nil$18,201 - $37,000 19c for each$1 over $18,200$37,001 - $80,000$3,572 plus 32.5c for each $1 over$37,000
$80,001 -$180,000 $17,547 plus 37c for each$1 over $80,000$180,001 and over $54,547 plus 45c for each$1 over \$180,000

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

• 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

• 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 Press Alt+Enter to insert a line break in an Excel formula

### Including Alt+Enter in VBA

Code 1
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

Excel function properties Description
IF(Logical, [Value if True,] [Value if False])Returns a value from a logical comparison, and return a value if TRUE or FALSE. IF and other functions can be nested to a maximum of 64 levels
IFS(Logical1, Value if True1 [,Logical2, Value if True2],…[Logical127, Value if True127])Returns a value that corresponds to the first TRUE (logical) condition. Fig 4: Tax payable calculator - showing the Excel 2016 IFS formula with multi line presentation

## 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.

SYNTAX: If...Then...Else statement
'' ========================================
'' 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

Code 1: Sub procedure IfThenTax
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

Code 2: Function procedure TaxP2016
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.