VBA controlling code execution (1)
VBA's If...Then...Else statement
In this module:
- a review of the Excel IF function
- an example of the Excel 2016 IFS function, and
- 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
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 theElse
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
, theTaxInc
range name Refers to:=Sheet1!G5
. This is a relative reference toG5
. In other words, theTaxInc
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 aFALSE
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
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. |
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:
- Line 1: Procedure declaration: a
Sub
procedure named IfThenTax. No arguments - the parentheses () are empty - Line 2: A comment line - commences with an apostrophe
- 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. - Line 4: Declare a variable named Tax as type
Double
. Its default value is 0. A double precision floating point number - Line 6: A comment line
- Line 7: Assign the value 37,001 to the variable named TaxInc
- Line 9: A comment line
- Line 10: The first logical test, will return false, execution skips to line 12
- Line 12: The second logical test, will return false, execution skips to line 14
- Line 14: The third logical test, will return true, execution continues at line 15
- 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 - Line 20: The last line of the block
- 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.
- 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)]