VBA controlling code execution (2)


VBA's Select Case statement



The statement structure is contained within the Select Case...End Select key words. Highlighted lines 8 and 17 of the syntax window.


SYNTAX: Select Case statement 
'' ========================================
'' Select Case statement
''
'' Executes one of several groups of statements, depending on the value of an expression.
''
'' Syntax
''
'' Select Case testexpression
'' [Case expressionlist-n
'' [statements-n]]
''
'' ...
''
'' [Case Else
'' [elsestatements]]
''
'' End Select
'' ========================================

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


Using VBA Select Case 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 SelectCaseTax 
Sub SelectCaseTax()
Dim TaxInc As Long
Dim Tax As Double

TaxInc = 37001

    Select Case TaxInc
        Case Is <= 18200
            Tax = TaxInc * 0
        Case Is <= 37000
            Tax = 0 + (TaxInc - 18200) * 0.19
        Case Is <= 80000
            Tax = 3572 + (TaxInc - 37000) * 0.325
        Case Is <= 180000
            Tax = 17547 + (TaxInc - 80000) * 0.37
        Case Else
            Tax = 54547 + (TaxInc - 180000) * 0.45
    End Select

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 SelectCaseTax. No arguments - the parentheses () are empty
  2. Line 2: Declare a variable named TaxInc as type Long
  3. Line 3: Declare a variable named Tax as type Double
  4. Line 5: Assign the value 37,001 to the variable named TaxInc
  5. Line 8: The first logical test, will return false, execution skips to line 10
  6. Line 10: The second logical test, will return false, execution skips to line 12
  7. Line 12: The third logical test, will return true, execution continues at line 13
  8. Line 13: 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 18
  9. Line 18: The last line of the block
  10. Line 20: The MsgBox statement displays a message box in Excel. MsgBox uses the concatenation operator &, and the VBA Format function