Help with UDF

[SSweez]

I am trying to write a UDF that will calculate an employee's estimated
tax (employee paid) by accepting variables such as certain tax rates
and caps as well as a range variable. The range variable represents
the monthly gross salary from January to December (the range has to be
12 cells left to right as this is how I move through it using the
Offset method). The UDF spits out an error and I think it has to do
with how I am working with the range. I populate an array by using the
Offset method. When I copy the code and try and make a proceduer out
of it I also get a type mismatch error for my TaxArray array. I assume
this is also a problem in my UDF. Can anyone help me understnad why
this does not work? Below is the code. Thanks in advance. - Scott

Function PayrollTax(Salary As Range, Month As Variant, SS_Rate As
Variant, SS_Cap As Variant, Medicare_Rate As Variant, Medicare_Cap As
Variant, FUTA_Rate As Variant, FUTA_Cap As Variant, SUTA_Rate As
Variant, SUTA_Cap As Variant)

'Declarations
Dim TaxArray(6, 11)


'Fill array with monthly salary information
For i = 0 To 6
TaxArray(0, i) = Salary.Offset(i, 0)
Next i

'Fill cumulative pay information
For i = 0 To 6
If i = 0 Then
TaxArray(1, i) = TaxArray(0, i) 'Since there is nothing before
the first month
Else
TaxArray(1, i) = TaxArray(0, i) + TaxArray(0, i - 1)
End If
Next i

'////////////////////////
'/////Populate SS Tax////
'////////////////////////
For i = 0 To 11
'First we firgure out the first month taxes then we figure out the
rest of the months
'Test to see if the first month' salary is already past the cap
If i = 0 And TaxArray(0, 0) > SS_Cap Then
TaxArray(2, i) = SS * SS_Cap
End If
'Capture the only other alternative for the first month of salary
If i = 0 And TaxArray(1, 0) < SS_Cap Then
TaxArray(2, 0) = SS * TaxArray(1, 0)
End If

'Now figure out what rest of the month's taxes are
If i > 0 Then
'Tax is zero if the prior two cumulative balances are greater
then the cap
If TaxArray(1, i) > SS_Cap And TaxArray(1, i - 1) > SS_Cap Then
TaxArray(2, i) = 0
'Prorated (most likely) for the last month
ElseIf TaxArray(1, i) > SS_Cap And TaxArray(1, i - 1) < SS_Cap
Then
TaxArray(2, i) = SS * (SS_Cap - TaxArray(2, i - 1))
'For every month put the first and last that we pay tax
Else
TaxArray(2, i) = SS * TaxArray(1, i)
End If
End If
Next i

'//////////////////////////////
'/////Populate Medicare Tax////
'//////////////////////////////
For i = 0 To 11
'First we firgure out the first month taxes then we figure out the
rest of the months
'Test to see if the first month' salary is already past the cap
If i = 0 And TaxArray(0, 0) > Medicare_Cap Then
TaxArray(3, i) = Medicare * Medicare_Cap
End If
'Capture the only other alternative for the first month of salary
If i = 0 And TaxArray(1, 0) < Medicare_Cap Then
TaxArray(3, 0) = Medicare * TaxArray(1, 0)
End If

'Now figure out what rest of the month's taxes are
If i > 0 Then
'Tax is zero if the prior two cumulative balances are greater
then the cap
If TaxArray(1, i) > Medicare_Cap And TaxArray(1, i - 1) >
Medicare_Cap Then
TaxArray(3, i) = 0
'Prorated (most likely) for the last month
ElseIf TaxArray(1, i) > Medicare_Cap And TaxArray(1, i - 1) <
Medicare_Cap Then
TaxArray(3, i) = Medicare * (Medicare_Cap - TaxArray(2, i -
1))
'For every month put the first and last that we pay tax
Else
TaxArray(3, i) = Medicare * TaxArray(1, i)
End If
End If
Next i

'//////////////////////////////
'/////Populate FUTA Tax////////
'//////////////////////////////
For i = 0 To 11
'First we firgure out the first month taxes then we figure out the
rest of the months
'Test to see if the first month' salary is already past the cap
If i = 0 And TaxArray(0, 0) > FUTA_Cap Then
TaxArray(4, i) = FUTA * FUTA_Cap
End If
'Capture the only other alternative for the first month of salary
If i = 0 And TaxArray(1, 0) < FUTA_Cap Then
TaxArray(4, 0) = FUTA * TaxArray(1, 0)
End If

'Now figure out what rest of the month's taxes are
If i > 0 Then
'Tax is zero if the prior two cumulative balances are greater
then the cap
If TaxArray(1, i) > FUTA_Cap And TaxArray(1, i - 1) > FUTA_Cap
Then
TaxArray(4, i) = 0
'Prorated (most likely) for the last month
ElseIf TaxArray(1, i) > FUTA_Cap And TaxArray(1, i - 1) <
FUTA_Cap Then
TaxArray(4, i) = FUTA * (FUTA_Cap - TaxArray(2, i - 1))
'For every month put the first and last that we pay tax
Else
TaxArray(4, i) = FUTA * TaxArray(1, i)
End If
End If
Next i

'//////////////////////////////
'/////Populate SUTA Tax////////
'//////////////////////////////
For i = 0 To 11
'First we firgure out the first month taxes then we figure out the
rest of the months
'Test to see if the first month' salary is already past the cap
If i = 0 And TaxArray(0, 0) > SUTA_Cap Then
TaxArray(5, i) = SUTA * SUTA_Cap
End If
'Capture the only other alternative for the first month of salary
If i = 0 And TaxArray(1, 0) < SUTA_Cap Then
TaxArray(5, 0) = SUTA * TaxArray(1, 0)
End If

'Now figure out what rest of the month's taxes are
If i > 0 Then
'Tax is zero if the prior two cumulative balances are greater
then the cap
If TaxArray(1, i) > SUTA_Cap And TaxArray(1, i - 1) > SUTA_Cap
Then
TaxArray(5, i) = 0
'Prorated (most likely) for the last month
ElseIf TaxArray(1, i) > SUTA_Cap And TaxArray(1, i - 1) <
SUTA_Cap Then
TaxArray(5, i) = SUTA * (SUTA_Cap - TaxArray(2, i - 1))
'For every month put the first and last that we pay tax
Else
TaxArray(5, i) = SUTA * TaxArray(1, i)
End If
End If
Next i

'Calculate monthly tax balances
For i = 0 To 11
TaxArray(6, i) = TaxArray(2, i) + TaxArray(3, i) + TaxArray(4, i) +
TaxArray(5, i)
Next i

'Return the tax for the requested month
PayrollTax = TaxArray(6, Month - 1)
End Function

 

[merjet]

Preliminary problems I found:

1. The type of every argument should be Range, not Variant.
2. SS_Rate is an argument, but SS is used in the calculations;
same for the other rates.

HTH,
Merjet

 

[JMB]

Come to think of it, you could also try this variation - which should accept
a variable number of tax Rate/Cap arguments (up to 15 different tax rates and
their respective caps). Since the method to compute each tax at a specified
month is the same, it does not matter (for either macro really) what order
you pass the tax rates/caps. Just be sure to pass them as

=PayrollTax2(SalaryRange, Month, TaxRate1, TaxCap1, TaxRate2, TaxCap2, etc)


Function PayrollTax2(Salary As Range, _
month As Long, ParamArray varTax() As Variant) As Double
Dim lngMonthSal As Long
Dim lngSubtotal As Long
Dim dblTemp As Double
Dim i As Long

If (Salary.Rows.Count > 1 And _
Salary.Columns.Count > 1) Then _
Exit Function

lngMonthSal = Salary.Cells(month).Value
lngSubtotal = Application.Sum(Range(Salary.Cells(1), _
Salary.Cells(month)))

For i = LBound(varTax) To UBound(varTax) - 1 Step 2
dblTemp = dblTemp + Application.Median((varTax(i + 1) - _
lngSubtotal + lngMonthSal), 0, lngMonthSal) * varTax(i)
Next i

PayrollTax2 = Round(dblTemp, 2)

End Function

 

[SSweez]

Wow, thank you so much!!! I am going to go through it in detail now
and find out how you were able to cut the code down so much. I like
that. For my own education what was I doing wrong in my original code
that generated an error?

-Scott

 

[JMB]

After discussing the issue you had w/your function - that reminds me that
cell referencing continues down the worksheet. So my function will misbehave
if you give it a month that is greater than the number of cells in the salary
range (say the salary is in A1:A12 and month = 14, Salary.Cells(month) will
refer to A14 and the function will happily continue until it generates an
error or (worse) returns data that is not right).

More on that here:
http://www.cpearson.com/excel/cells.htm


I would suggest making some small changes to ensure the month number does
not exceed the number of cells in the Salary range.


Function PayrollTax2(Salary As Range, _
Month As Long, ParamArray varTax() As Variant) As Variant '<<CHANGE
Dim lngMonthSal As Long
Dim lngSubtotal As Long
Dim dblTemp As Double
Dim i As Long

If (Salary.Rows.Count > 1 And _ '<<<CHANGE
Salary.Columns.Count > 1) Or _
Salary.Cells.Count < Month Then
PayrollTax2 = CVErr(xlErrValue)
Exit Function
End If

lngMonthSal = Salary.Cells(Month).Value
lngSubtotal = Application.Sum(Range(Salary.Cells(1), _
Salary.Cells(Month)))

For i = LBound(varTax) To UBound(varTax) - 1 Step 2
dblTemp = dblTemp + Application.Median((varTax(i + 1) - _
lngSubtotal + lngMonthSal), 0, lngMonthSal) * varTax(i)
Next i

PayrollTax2 = Round(dblTemp, 2)

End Function