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


xlf-if-function-tax
Fig 1: Tax payable calculator - using a series of nested IF functions

About figure 1:


xlf-if-function-multi-line
Fig 2: Tax payable calculator - showing the IF function formula with multi line presentation

About figures 2 and 3:

xlf tipPress 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										


xlf-alt-enter
Fig 3: Alt+Enter - code ...


xlf-if-function-comment
Fig 3: Tax payable calculator - showing the formula with a comment added with the N function

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.

xlf-ifs-function-multi-line
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
xlf-if-then-else-msgbox-tax
Fig 4: ForNext1 - message box with text strings and Currency format

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.