-1

I am very stuck on this coding project and could really use some help.

I am trying to write code that will calculate the taxes due on a person's income. In this model, my professor has given us a list of tax rates to use and a set of rules that change how taxes are calculated. I have worked on this for hours and can never get it to give me the correct answer.

The function returns a #VALUE error in Excel, and I don't understand why.

'This function calculate the tax liability based on taxable income (AGI)
Function CalculateTax(taxableIncome As Variant, taxRates As Variant, _
           taxThresholds As Variant, standardDeduction As Variant, _
           personalException As Variant, AlternativeTaxSystem As Variant, _
                                             bequest As Variant) As Variant
'Initiate a variable to use to determine the size of taxRates - for some reason doing this directly doesn't work
Dim sizeArray As Variant
sizeArray = taxRates
'Initiate a variable for the alternative tax system
Dim AltTaxRate As Double
AltTaxRate = taxRates(i) - 0.05
'Initiate variable and calculate amount to apply to tax table, leaving out the deductions from the equation
Dim amountToTax As Double
amountToTax = taxableIncome
'Initiate tax amount and set to zero
Dim taxAmount As Double
taxAmount = 0

'loop over tax brackets, adding the incremental tax each time
For i = 1 To UBound(sizeArray, 1)
    'Runs an if statement to check if all three conditions are met
    If AlternativeTaxSystem = "Yes" And bequest = "No" And taxRates(i) >= 0.2 Then
        'Calculate the tax amount per bracket, based on the minimum of the bracket size or total tax minus the bracket threshold, with a true minimum at zero, and subtracting off 0.05 from the tax rates
        taxAmount = taxAmount + Application.Max(Application.Min(taxThresholds(i + 1) - taxThresholds(i), amountToTax - taxThresholds(i)), 0) * AltTaxRate
    ElseIf AlternativeTaxSystem = "Yes" And bequest = "No" And taxRates(i) < 0.2 Then
        taxAmount = taxAmount + Application.Max(Application.Min(taxThresholds(i + 1) - taxThresholds(i), amountToTax - taxThresholds(i)), 0) * taxRates(i)
    ElseIf AlternativeTaxSystem = "Yes" And bequest = "Yes" And taxRates(i) >= 0.2 Then
        amountToTax = taxableIncome - 250000
        taxAmount = taxAmount + Application.Max(Application.Min(taxThresholds(i + 1) - taxThresholds(i), amountToTax - taxThresholds(i)), 0) * AltTaxRate
    ElseIf AlternativeTaxSystem = "Yes" And bequest = "Yes" And taxRates(i) < 0.2 Then
        amountToTax = taxableIncome - 250000
        taxAmount = taxAmount + Application.Max(Application.Min(taxThresholds(i + 1) - taxThresholds(i), amountToTax - taxThresholds(i)), 0) * taxRates(i)
    Else
        amountToTax = taxableIncome - standardDeduction - personalException
        taxAmount = taxAmount + Application.Max(Application.Min(taxThresholds(i + 1) - taxThresholds(i), amountToTax - taxThresholds(i)), 0) * taxRates(i)
    End If
Next i
'output answer to function
CalculateTax = taxAmount

End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Dalton Hoover
  • 13
  • 3
  • Welcome to SO! I've formatted the code and removed a bit of fluff from your post, but unfortunately your question is off-topic as worded. Questions asking "what's wrong with my code?" must **include the desired behavior**, a **specific problem** or error and the shortest code necessary to reproduce it in the question itself ([mcve]). Questions without a clear problem statement are not useful to other readers. – Mathieu Guindon Dec 07 '16 at 03:50
  • Your loop count `For i = 1 To UBound(sizeArray, 1)` assumes a 2D array, but you're only using 1 dimensional indexing: `taxRates(i)` – Comintern Dec 07 '16 at 03:51
  • Thank you for the warm welcome. I did change the title to say that I am needing help with the #VALUE error I am getting in excel when running my function. Is there anything else I need to change with my post? – Dalton Hoover Dec 07 '16 at 03:53
  • @DaltonHoover - Do you get a specific error? Does it highlight a specific line? How are you calling the function? If you're passing ranges, what does the worksheet look like? What do you expect it to return for a specific input? Etc. A good start would be to place a breakpoint at the start of the function and step through it with the debugger. – Comintern Dec 07 '16 at 03:55
  • 2
    The `#VALUE` error is masking the real problem, Excel is swallowing the real error. Try calling the function from the *immediate pane* (Ctrl+G) instead of from an Excel formula. F9 to set/remove a breakpoint, F8 to step through line by line. Once you get a real runtime error and know exactly which instruction is raising that error, *and you still have no idea why*, and Google doesn't turn up anything useful, **then** you have a SO question =) – Mathieu Guindon Dec 07 '16 at 03:58
  • @Comintern - I am expecting it to return a number. So I could have it As Double for the function, instead of As Variant, but thought that that could be a cause of the problem. I am getting the #VALUE error in excel, although no error in VBA and not highlighting a specific line of the code. Also, unsure on what you mean by how I am calling the function. Also, can you provide some more clarity on the 1D vs. 2D array part? I am very new to any kind of coding. – Dalton Hoover Dec 07 '16 at 04:02
  • Right, but what number for what inputs? The return type doesn't make any difference at all - literally ***anything*** can be returned as a `Variant`. By calling the function, I mean something like "I call it with `CalculateTax(3.14, $A$1:$A$10, $B$1:$B$10, 1.42, "Yes", "Yes")`" and "The data looks like this nicely formatted html (or screenshot)". See [Documentation](http://stackoverflow.com/documentation/vba/3064/arrays/17455/multidimensional-arrays#t=201612070407493744662) for a brief explanation of how array dimensions work. – Comintern Dec 07 '16 at 04:08
  • Type `?CalculateTax(3.14, $A$1:$A$10, $B$1:$B$10, 1.42, "Yes", "Yes")` in the *immediate pane* (Ctrl+G - that `?` stands for `Print`, and means if the function runs without error, its result will be "printed" on the next line in the *immediate pane* - you can do `?2+2` to see how it outputs `4`), that will execute your function without Excel interfering with the execution. – Mathieu Guindon Dec 07 '16 at 04:12
  • 1
    There's also a good primer for debugging in the [excel-vba Documentation](http://stackoverflow.com/documentation/excel-vba/861/debugging-and-troubleshooting#t=201612070414486341641). – Comintern Dec 07 '16 at 04:15

1 Answers1

1
AltTaxRate = taxRates(i) - 0.05

There's no i where you have this line.

If you haven't declared i then it will have a default value of zero, and your error is because taxRates(0) does not exist.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125