xlf QandA series
Arrays, and sub-arrays, with application to tax rates
The QUESTION
Using the VBE:
- Tax rates array: Write a Private procedure to assign a set to tax rates to a VBA array. The rates apply to an Australian resident individual, and are for tax years 2010 to 2016 inclusive (excluding the Medicare and other levies). The array is static, with dimensions (2010 to 2016, 1 to 5, 1 to 3). In the first dimension, 2010 refers to tax rates for the tax year 2009-10, and so on. Use assignment statements to populate the array
- Sub array: Write a Private function procedure to return the second and third dimension (ie. a two dimensional sub array) for a specific tax year, from the array developed in part a. Assume that this function is only called from VBA, and not from a worksheet
- Tax: Write a Public function named Tax, categorized in the WS Financial group, that returns the tax values, based on an amount of taxable income, and a date within a specific tax year. For example, Tax(54000, DATEVALUE("31-Dec-2010")) will apply the Tax Schedule for 2010-11 to the 54,000 income amount. The function will return a #VALUE error if the date is out of range
Preliminary
A set of tax rates are required. These are available from the Australian Taxation Office (ATO) web site and are included here for your reference.
View: Prior tax rates 2014-15 to 1983-84
View: Tax rates 2015-16 to 2014-15
Source: Australian Taxation Office
As a preliminary exercise it is suggested that the tax rates for one year are entered into a 5 row by 3 column array in a worksheet. The Excel range should be suitable for use with the Excel VLOOKUP function. As an example, the 2015-16 tax rates (this is the first dimension, 2016 index developed later in VBA), is shown in the yellow section of Table 1 in figure 1. The table "information" needs its appropriate numeric values to be entered into the 15 available cells in the area shown by the selection on the worksheet "Sheet1". You can then use VLOOKUP (in Excel) to return the amount of tax payable. Once you understand the logic, you can then assign the tax values to the elements of the 3-D array in VBA.
Code structure: the public Tax function in part c, calls the 2-D array from the private function in part b, which in turn uses the 3-D array from part a.


Note: set the category lower band at 18201, 37001, ... to clearly indicate that the 18,201st dollar is taxable at 19%, and the 37,001st dollar is taxable at 32.5%, ...
Download the xlsx file: q-tax-rate-table-15to16.xlsx [11 KB]
QUESTION a Tax rates: Write a Private procedure to assign a set to tax rates to a VBA array. The rates apply to an Australian resident individual, and are for tax years 2010 to 2016 inclusive (excluding the Medicare levy). The array is static, with dimensions (2010 to 2016, 1 to 5, 1 to 3). In the first dimension, 2010 refers to tax rates for the tax year 2009-10, and so on. Use assignment statements to populate the array.
- Assign a set of tax rates to a 7 x 5 x 3 array in VBA. A 3-D array can be thought of as comprising rows x columns x depth
- Make the array values available to the sub array extraction procedure in code 2
Code 1: Part (a) - Module level declaration, and TaxRates sub procedure
Option Explicit Dim TaxR(2010 To 2016, 1 To 5, 1 To 3) As Double Private Sub TaxRates() '' 2015 to 2016 TaxR(2016, 1, 1) = 0 TaxR(2016, 1, 2) = 0 TaxR(2016, 1, 3) = 0 TaxR(2016, 2, 1) = 18201 TaxR(2016, 2, 2) = 0 TaxR(2016, 2, 3) = 0.19 TaxR(2016, 3, 1) = 37001 TaxR(2016, 3, 2) = 3572 TaxR(2016, 3, 3) = 0.325 TaxR(2016, 4, 1) = 80001 TaxR(2016, 4, 2) = 17547 TaxR(2016, 4, 3) = 0.37 TaxR(2016, 5, 1) = 180001 TaxR(2016, 5, 2) = 54547 TaxR(2016, 5, 3) = 0.45 '' 2014 to 2015 TaxR(2015, 1, 1) = 0 TaxR(2015, 1, 2) = 0 TaxR(2015, 1, 3) = 0 TaxR(2015, 2, 1) = 18201 TaxR(2015, 2, 2) = 0 TaxR(2015, 2, 3) = 0.19 TaxR(2015, 3, 1) = 37001 TaxR(2015, 3, 2) = 3572 TaxR(2015, 3, 3) = 0.325 TaxR(2015, 4, 1) = 80001 TaxR(2015, 4, 2) = 17547 TaxR(2015, 4, 3) = 0.37 TaxR(2015, 5, 1) = 180001 TaxR(2015, 5, 2) = 54547 TaxR(2015, 5, 3) = 0.45 '' 2013 to 2014 TaxR(2014, 1, 1) = 0 TaxR(2014, 1, 2) = 0 TaxR(2014, 1, 3) = 0 TaxR(2014, 2, 1) = 18201 TaxR(2014, 2, 2) = 0 TaxR(2014, 2, 3) = 0.19 TaxR(2014, 3, 1) = 37001 TaxR(2014, 3, 2) = 3572 TaxR(2014, 3, 3) = 0.325 TaxR(2014, 4, 1) = 80001 TaxR(2014, 4, 2) = 17547 TaxR(2014, 4, 3) = 0.37 TaxR(2014, 5, 1) = 180001 TaxR(2014, 5, 2) = 54547 TaxR(2014, 5, 3) = 0.45 '' 2012 to 2013 TaxR(2013, 1, 1) = 0 TaxR(2013, 1, 2) = 0 TaxR(2013, 1, 3) = 0 TaxR(2013, 2, 1) = 18201 TaxR(2013, 2, 2) = 0 TaxR(2013, 2, 3) = 0.19 TaxR(2013, 3, 1) = 37001 TaxR(2013, 3, 2) = 3572 TaxR(2013, 3, 3) = 0.325 TaxR(2013, 4, 1) = 80001 TaxR(2013, 4, 2) = 17547 TaxR(2013, 4, 3) = 0.37 TaxR(2013, 5, 1) = 180001 TaxR(2013, 5, 2) = 54547 TaxR(2013, 5, 3) = 0.45 '' 2011 to 2012 TaxR(2012, 1, 1) = 0 TaxR(2012, 1, 2) = 0 TaxR(2012, 1, 3) = 0 TaxR(2012, 2, 1) = 6001 TaxR(2012, 2, 2) = 0 TaxR(2012, 2, 3) = 0.15 TaxR(2012, 3, 1) = 37001 TaxR(2012, 3, 2) = 4650 TaxR(2012, 3, 3) = 0.3 TaxR(2012, 4, 1) = 80001 TaxR(2012, 4, 2) = 17550 TaxR(2012, 4, 3) = 0.37 TaxR(2012, 5, 1) = 180001 TaxR(2012, 5, 2) = 54550 TaxR(2012, 5, 3) = 0.45 '' 2010 to 2011 TaxR(2011, 1, 1) = 0 TaxR(2011, 1, 2) = 0 TaxR(2011, 1, 3) = 0 TaxR(2011, 2, 1) = 6001 TaxR(2011, 2, 2) = 0 TaxR(2011, 2, 3) = 0.15 TaxR(2011, 3, 1) = 37001 TaxR(2011, 3, 2) = 4650 TaxR(2011, 3, 3) = 0.3 TaxR(2011, 4, 1) = 80001 TaxR(2011, 4, 2) = 17550 TaxR(2011, 4, 3) = 0.37 TaxR(2011, 5, 1) = 180001 TaxR(2011, 5, 2) = 54550 TaxR(2011, 5, 3) = 0.45 '' 2009 to 2010 TaxR(2010, 1, 1) = 0 TaxR(2010, 1, 2) = 0 TaxR(2010, 1, 3) = 0 TaxR(2010, 2, 1) = 6001 TaxR(2010, 2, 2) = 0 TaxR(2010, 2, 3) = 0.15 TaxR(2010, 3, 1) = 37001 TaxR(2010, 3, 2) = 4650 TaxR(2010, 3, 3) = 0.3 TaxR(2010, 4, 1) = 80001 TaxR(2010, 4, 2) = 17580 TaxR(2010, 4, 3) = 0.38 TaxR(2010, 5, 1) = 180001 TaxR(2010, 5, 2) = 55850 TaxR(2010, 5, 3) = 0.45 End Sub
About code 1
- Line 2: Declare an array named TaxR at the Module level (Declarations area). The array is static, this means its dimensions are specified in the declaration. First dimension index (2010 to 2016), second dimension index (1 to 5), and third dimension index (1 to 3). This array is initialized with default values when any procedure in the Module is run. Once populated by the TaxRates procedure, the values are retained during the current session and thus available to code 2
- Line 4: Private Sub - does not appear in the Macro list in Excel. Can be tested by setting a Break Point (or inserting a Stop statement), run by pressing F5, then viewing the array in the Locals window
- Lines 6 to 8: Assign values to the first column, depth 1, 2, and 3
- Lines 10 to 12: Assign values to the second column, depth 1, 2, and 3. Then continue the assignment process ...
QUESTION b Sub array: Write a Private function procedure to return the second and third dimension (ie. a two dimensional sub array) for a specific tax year, from the array developed in part a. Assume that this function is only called from VBA, and not from a worksheet.
- Create a function procedure with one argument, the tax year as a 4 digit integer, of type Integer
- The function returns a 5 x 3 array. Use type Variant to accommodate a multi value return item
- The sub array will be dynamic to provide flexibility, however, a static array is equally acceptable as it is 5 rows x 3 columns in all cases provided here
Code 2: Part (b) - TaxSub function procedure. One argument TaxYear as type Integer
Private Function TaxSub(TaxYear As Integer) As Variant Dim NoRows As Integer Dim NoCols As Integer Dim i As Integer, j As Integer Dim TaxSubArr() As Double If TaxR(2016, 5, 3) = 0 Then TaxRates NoRows = UBound(TaxR, 2) NoCols = UBound(TaxR, 3) ReDim TaxSubArr(1 To NoRows, 1 To NoCols) For i = 1 To NoRows For j = 1 To NoCols TaxSubArr(i, j) = TaxR(TaxYear, i, j) Next j Next i TaxSub = TaxSubArr End Function
About code 2
- Line 160: Procedure declaration. Name: TaxSub. One argument TaxYear, and return type Variant to accommodate an array
- Lines 161 to 163: Variable declarations
- Line164: Declare an dynamic array named TaxSubArr. The array is dynamic because the dimension(s) are left blank
- Line166: Check if the module level array TaxR has zero elements. If yes then call the TaxRates procedure (code 1) to assign the tax rates to the 3-D array
- Lines 168: Get the index number of the upper element in the second dimension of the array named TaxR and assign to the NoRows variable. Returns 5
- Lines 169: Get the index number of the upper element in the third dimension of the array named TaxR and assign to the NoCols variable. Returns 3
- • UBound (Upper Bound) - Syntax: UBound(arrayname [,dimension])
- Lines 170: Now that the dimensions are retrieved, the values can be used to Redim the dynamic array TaxSubArr
- Lines 172 to 176: A double For...Next loop - process elements in rows and columns
- Line 174: Right hand side - for a particular year specified by the TaxYear argument, return values from the second and third dimension of the 3-D array. Assign each element to the first and second dimensions of the 2-D named TaxSubArr
- Line178: Assign the TaxSubArr array values to the function return value
Code 3: Part (b) - TextTaxSub sub procedure. A test procedure to call the TaxSub function
Sub TestTaxSub() Dim Ans() As Double Ans = TaxSub(2016) Stop End Sub
About code 3
- Line 186: Declare a dynamic array of type Double. In this instance the array is not Redim'ed because it will inherit the dimensions of the assigned array in line 187
- Line 187: Right hand side - call the TaxSub function with TaxYear argument set to 2016. TaxSub returns a Variant array, that is assigned to Ans array
- Line 188: The Stop statement causes code execution to halt. Yellow background and arrow in gutter. Use the Locals window to view the array (figure 2)

QUESTION c Tax: Tax: Write a Public function named Tax, categorized in the WS Financial group, that returns the tax values, based on an amount of taxable income, and a date within a specific tax year. For example, Tax(54000, DATEVALUE("31-Dec-2010")) will apply the Tax Schedule for 2010-11 to the 54,000 income amount. The function will return a #VALUE error if the date is out of range.
- Three suggested solutions are provided - other combinations are also possible
- The procedures for the 3-D array and the sub array are used as a data source for the tax rates
- This part of the example incorporates two logical processes:
- Convert the date to a tax year pointer for the appropriate tax array
- Construction a code segment to return the tax payable from that particular tax table
- Code 4: 1. Select...Case with individual tax years, and 2. the Excel VLOOKUP function with a 2-D array
- Code 5: 1. Simplified Select...Case with VBA Year, and Month function, and 2. the Excel VLOOKUP function with a 2-D array
- Code 6: 1. Simplified If...Then...ElseIf with VBA Year, and Month function, and 2. If...Then...ElseIf and 2-D array elements
Code 4: Part (c) - Tax function procedure (version 1)
Public Function Tax(Income As Long, Date_ As Date) As Variant Dim i As Long Dim Year As Integer Dim T() As Double On Error GoTo ErrHandler Select Case Date_ Case DateValue("1 July 2015") To DateValue("30 June 2016") Year = 2016 Case DateValue("1 July 2014") To DateValue("30 June 2015") Year = 2015 Case DateValue("1 July 2013") To DateValue("30 June 2014") Year = 2014 Case DateValue("1 July 2012") To DateValue("30 June 2013") Year = 2013 Case DateValue("1 July 2011") To DateValue("30 June 2012") Year = 2012 Case DateValue("1 July 2010") To DateValue("30 June 2011") Year = 2011 Case DateValue("1 July 2009") To DateValue("30 June 2010") Year = 2010 End Select T = TaxSub(Year) i = Income With Application Tax = .Vlookup(i, T, 2) + (i - (.Vlookup(i, T, 1) + 1)) * .Vlookup(i, T, 3) End With Exit Function ErrHandler: Tax = CVErr(xlErrValue) End Function
About code 4
- Lines 212 to 227: tax year pointer Select...Case with start and end dates for tax year. 16 code lines
- Line 229: call the TaxSub function with argument Year (a 4 digit Integer) from the tax year pointer segment
- Line 233: tax table lookup using Excel's VLOOKUP function. 1 code line
Code 5: Part (c) - Tax function procedure (version 2)
Public Function Tax2_(Income As Long, Date_ As Date) As Variant Dim i As Long Dim Mth As Integer, Yr As Integer Dim T() As Double On Error GoTo ErrHandler Mth = Month(Date_) Select Case Mth Case 1 To 6: Yr = Year(Date_) Case 7 To 12: Yr = Year(Date_) + 1 End Select T = TaxSub(Yr) i = Income With Application Tax2_ = .Vlookup(i, T, 2) + (i - (.Vlookup(i, T, 1) + 1)) * .Vlookup(i, T, 3) End With Exit Function ErrHandler: Tax2_ = CVErr(xlErrValue) End Function
About code 5
- Lines 252 to 256: tax year pointer 5 code lines
- Lines 254 and 255: call the TaxSub function with argument Year (a 4 digit Integer) from the tax year pointer segment
- Line 262: tax table lookup using Excel's VLOOKUP function. The same statement as code 4
Code 6: Part (c) - Tax function procedure (version 3)
Public Function Tax3_(Income As Long, Date_ As Date) As Variant Dim i As Long Dim Mth As Integer Dim Yr As Integer Dim T() As Double On Error GoTo ErrHandler Mth = Month(Date_) If Mth >= 1 Or Mth <= 6 Then Yr = Year(Date_) ElseIf Mth >= 7 Or Mth <= 12 Then Yr = Year(Date_) + 1 End If T = TaxSub(Yr) i = Income If i < T(2, 1) Then Tax3_ = T(1, 2) + (i - (T(1, 1) + 1)) * T(1, 3) ElseIf i < T(3, 1) Then Tax3_ = T(2, 2) + (i - (T(2, 1) + 1)) * T(2, 3) ElseIf i < T(4, 1) Then Tax3_ = T(3, 2) + (i - (T(3, 1) + 1)) * T(3, 3) ElseIf i < T(5, 1) Then Tax3_ = T(4, 2) + (i - (T(4, 1) + 1)) * T(4, 3) Else Tax3_ = T(5, 2) + (i - (T(5, 1) + 1)) * T(5, 3) End If Exit Function ErrHandler: Tax3_ = CVErr(xlErrValue) End Function
About code 6
- Lines 278 to 283: tax year pointer 5 code lines
- Line 285: call the TaxSub function with argument Year (a 4 digit Integer) from the tax year pointer segment
- Line 288 to 298: tax table values returned by direct reference to elements
Code 7: Part (c) - TestTax sub procedure
Sub TestTax() Dim Ans As Double, Ans2_ As Double, Ans3_ As Double Ans = Tax(180001, DateValue("21 December 2009")) Ans2_ = Tax2_(180001, DateValue("21 December 2009")) Ans3_ = Tax3_(180001, DateValue("21 December 2009")) Stop End Sub
About code 7
- Lines 313 to 315: returns values for each of Tax, Tax2_, and Tax3_. View the values in the Locals window
Code 8: - categorized in the worksheet Financial group
Private Sub AddTaxToFinancialCategory() Dim FuncName As String Dim FuncDesc As String Dim FuncCategory As String '' Dim FuncArgDesc(1 To 2) As String On Error GoTo ErrHandler FuncName = "Tax" FuncDesc = "Returns amount of tax payable" FuncCategory = 1 ' Financial category '' FuncArgDesc(1) = "the amount of taxable income" '' FuncArgDesc(2) = "the serial date value in the tax year" Application.MacroOptions _ Macro:=FuncName, _ Description:=FuncDesc, _ Category:=FuncCategory '' ArgumentDescriptions:=FuncArgDesc Exit Sub ErrHandler: End Sub
About code 8
- Lines 344 to 347: - one code statement - uses the Application.MacroOptions method with values for arguments: FuncName, FuncDesc, and FuncCategory
- The result is shown in figure 3, with the Tax function, including a Description, in the Financial category

- This example was developed in Excel 2013 Pro 64 bit.
- Found an error? Send an email: ioconnor@excelatfinance.com
- Published: 18 November 2016
- Revised: Saturday 25th of February 2023 - 09:39 AM, [Australian Eastern Standard Time (EST)]