Custom function with small array and for loop -- really slow, keyboard input hangs?

[Dan_234609]

I'm relatively new to writing VBA, but have been lurking on the site for most of that time. There are quite a few helpful threads on here -- so to start off, I'd like to say thank you to all of the contributors!

I've been working on a custom function to calculate income tax. I have two different implementations, both providing correct and identical results. The first is a "cleaner" implementation using a two-dimensional array and a for loop. The second implementation defines each variable explicitly, and moves through a series of If statements -- no array, and no looping.

What I've noticed is that when I started using the first function in my workbook, everything seemed to slow down, and at times the keyboard input would "hang" -- the mouse was still responsive, but the keyboard input wasn't going into the workbook. I tried switching windows into a different program, and the keyboard worked fine there.

I have not noticed these issues when using the second, more manually written out function.

Are using arrays and for loops really processor intensive? Could that be the explanation as to why things are getting mucked up? Or is there some other (probably obvious) error in my code that I'm overlooking?

Here is the first implementation, using the array and the for loop:

Function CalTax_slow(Income As Double)
'Last updated with 2013 tax brackets

Dim Brk(0 To 6, 1 To 2) As Double

'Define tax percentages
Brk(0, 1) = 0
Brk(1, 1) = 0.01
Brk(2, 1) = 0.02
Brk(3, 1) = 0.04
Brk(4, 1) = 0.06
Brk(5, 1) = 0.08
Brk(6, 1) = 0.093

'Define tax bracket cutoffs
Brk(0, 2) = 0
Brk(1, 2) = 15498
Brk(2, 2) = 36742
Brk(3, 2) = 57990
Brk(4, 2) = 80500
Brk(5, 2) = 101738
Brk(6, 2) = 519688

'Initialize the tax to zero
Tax = 0

'Check to make sure the income doesn't exceed the brakets that I programmed
If Income > Brk(6, 2) Then
    MsgBox "ERROR: Your income exceeds the tax brakets programmed into this tool"
    Exit Function
End If
    
'Loop through the tax brackets
For i = 1 To 6
    'If you max out a braket, add the max amount for that bracket to the tax
    If Income > Brk(i, 2) Then
        Tax = Tax + (Brk(i, 2) - Brk(i - 1, 2)) * Brk(i, 1)
    Else
        'If you haven't maxed out the bracket, add the culated tax and exit the for loop -- don't do any higher brackets
        Tax = Tax + (Income - Brk(i - 1, 2)) * Brk(i, 1)
        Exit For
    End If
Next i

'Return the total tax value
CalTax_slow = Tax

End Function

Here is the second, more "manual" implementation that has not been causing issues:

Function CalTax(Income)


Dim Brk1 As Double
Dim Brk2 As Double
Dim Brk3 As Double
Dim Brk4 As Double
Dim Brk5 As Double


Dim Tax1 As Double
Dim Tax2 As Double
Dim Tax3 As Double
Dim Tax4 As Double
Dim Tax5 As Double
Dim Tax6 As Double


Brk1 = 15498
Brk2 = 36742
Brk3 = 57990
Brk4 = 80500
Brk5 = 101738
Brk6 = 519688


Tax1 = 0.01
Tax2 = 0.02
Tax3 = 0.04
Tax4 = 0.06
Tax5 = 0.08
Tax6 = 0.093


'Initialize the tax to zero
Tax = 0


If Income > Brk1 Then
    Tax = Tax + Brk1 * Tax1
Else
    Tax = Tax + Income * Tax1
    GoTo Final
End If


If Income > Brk2 Then
    Tax = Tax + (Brk2 - Brk1) * Tax2
Else
    Tax = Tax + (Income - Brk1) * Tax2
    GoTo Final
End If


If Income > Brk3 Then
    Tax = Tax + (Brk3 - Brk2) * Tax3
Else
    Tax = Tax + (Income - Brk2) * Tax3
    GoTo Final
End If


If Income > Brk4 Then
    Tax = Tax + (Brk4 - Brk3) * Tax4
Else
    Tax = Tax + (Income - Brk3) * Tax4
    GoTo Final
End If


If Income > Brk5 Then
    Tax = Tax + (Brk5 - Brk4) * Tax5
Else
    Tax = Tax + (Income - Brk4) * Tax5
    GoTo Final
End If


If Income > Brk6 Then
    Tax = Tax + (Brk6 - Brk5) * Tax6
Else
    Tax = Tax + (Income - Brk5) * Tax6
    GoTo Final
    End If


Final:
CalTax = Tax


End Function

Any observations would be greatly appreciated. Thanks!

 

[tyger0951]

Could it be running when you don't want it to be? I'd test by adding a

msgbox("Done")

right before the end and see if it continiously pops up while the spreadsheet is open.