19.7 State Withholding User-Defined Function (UDF)

In this section, we will write a custom function for the state tax withholding calculation we did in the Megaformulas lesson. This file will use the Annual Payroll macro-enabled workbook below.

[Annual Payroll macro-enabled workbook]

For this example, we will use the semimonthly withholding calculation worksheet you’ve seen previously. As a reminder, here is what the calculation looks like for single and married employees. Remember, there are two differences for single versus married individuals: the base allowance and the allowance used in Line 4.

If you switch over to the VBA editor, you will see that a function has already been set up that we can use. The function will require the user to pass the pay and whether the individual is single or married. S means single, and M means married in the data.

Function StateWH(Pay As Double, MS As String) As Double
End Function

Next, we will need to declare some variables to use. Let’s scan through the calculations and see which variable names we might use. The pay is already passed, so we won’t need to declare a variable for the first step. It looks like we will need to have variables for each of the lines. We could probably reuse some of these, but it might make it easier to maintain if we just use a new variable for each calculation. The only exception will be the last line, Line 7, because we can just use the name of the function, StateWH, to save the values that will be returned from the function.

     Dim Line2, Line3, Line4, Line5, Line6

The calculation for Line 2 is straightforward. We simply take the pay that was passed into the function and multiply it by 4.85%.

     'Line 2
     Line2 = Pay * 0.0485

On Line 3, we have a difference depending on whether the individuals are single or married. Our underlying data uses S for single and M for married. We can use an IF statement to check if the individuals are single and then set the appropriate allowance amount. Otherwise, we will set the higher amount for married individuals. You’ll notice that I use the UCase function to convert the data that is entered into uppercase so that it doesn’t matter whether the individuals enter S or s; both will match.

     'Line 3
     If UCase(MS) = "S" Then
          Line3 = 16
     Else
          Line3 = 33
     End If

Line 4 is another place where allowance amounts are different if the employee is single versus if the employee is married. Notice the additional rule that the calculation for Line 4 should not be negative. We can use an IF statement to handle that condition.

     'Line 4
     If UCase(MS) = "S" Then
          Line4 = Pay - 324
     Else
          Line4 = Pay - 648
     End If
     If Line4 < 0 Then Line4 = 0

Line 5 is a simple calculation using the results of Line 4.

     'Line 5
     Line5 = Line4 * 0.013

Line 6 is a simple calculation also. Here, we will also make sure that the amount isn’t negative.

     'Line 6
     Line6 = Line3 - Line5
     If Line6 < 0 Then Line6 = 0

For Line 7, we will use the name of the function to store our final calculation, even though we will need to do a few extra steps. The initial calculation is simple, and again, we will ensure that the amount isn’t negative. Before we return the result, we should round our result. Now, VBA has a built-in round function. However, its calculations are slightly different than the worksheet version. Here, we will use the Round function that we have used in the Megaformulas lesson to ensure that we get the same results.

     'Line 7
     StateWH = Line2 - Line6
     If StateWH < 0 Then StateWH = 0
     StateWH = WorksheetFunction.Round(StateWH, 2)

So, if we put it all together, the StateWH custom function looks like the following.

Function StateWH(Pay As Double, MS As String) As Double
     Dim Line2, Line3, Line4, Line5, Line6
    
     'Line 2
     Line2 = Pay * 0.0485
    
     'Line 3
     If UCase(MS) = "S" Then
          Line3 = 16
     Else
          Line3 = 33
     End If
    
     'Line 4
     If UCase(MS) = "S" Then
          Line4 = Pay - 324
     Else
          Line4 = Pay - 648
     End If
     If Line4 < 0 Then Line4 = 0
    
     'Line 5
     Line5 = Line4 * 0.013
    
     'Line 6
     Line6 = Line3 - Line5
     If Line6 < 0 Then Line6 = 0
    
     'Line 7
     StateWH = Line2 - Line6
     If StateWH < 0 Then StateWH = 0
     StateWH = WorksheetFunction.Round(StateWH, 2)
End Function

After implementing the function, go ahead and try it out. You should get the same results as the Megaformula that we wrote in an earlier lesson. If you don’t get the same answer, try troubleshooting your function to see where you may have gone wrong.

What are the advantages of creating functions compared to Megaformulas? When we wrote our Megaformula, remember that we had to make the input columns we were pulling from absolute. In other words, the data had to be stored in the same columns. With a UDF, the parameters can be anywhere. They don’t even have to be on the same line or relative to a certain cell. Frankly, since they are like other functions that users are already familiar with, this StateWH is probably more intuitive and easier to use. Also, consider if we had to update the function based on changes in another tax year. Making those changes would be much easier in VBA.

UDFs are super powerful. Spend some time thinking about the types of functions you could now write that would be useful in your field. The possibilities really are endless, and you can make calculations more consistent and easier to implement and maintain. What a valuable skill!