Hello all and welcome back to the Excel Tip of the Week. This week, we have a Developer post in which we’re looking at a VBA coding option for situations where we have multiple paths to go down – Select Case. We’re going to be comparing and contrasting this with the more common If-Then-Else structure, which we covered back in TOTW #141. We’ll use the same example we did back then: stamp duty land tax.
Just as before, however, it’s important to note that we’re just using this as an example of a tiered tax calculation – actual SDLT is more complex than this and you shouldn’t use a formula you found in an Excel blog to make your investment decisions!
Select Case and what it does
Select Case is a VBA command we can use to have Excel choose a next step based on the value of a variable. It’s most commonly used with ranges of numbers but does also accept text.
The general syntax for it is as follows:
Select Case [variable name]
Case [lower bound 1] to [upper bound 1]
[code 1]
Case [lower bound 2] to [upper bound 2]
[code 2]
…
Case Else
[else code]
End Select
Note the use of “Case Else” to mop up anything not covered in the previous ranges. This can also be used for error handling if the input value isn’t supposed to fall outside of the given ranges.
You can also provide more complex ranges. Here’s a more concrete example:
Select Case DayNumber
Case 1, 21, 31
DayName = DayNumber & “st”
Case 2, 22
DayName = DayNumber & “nd”
Case 3, 23
DayName = DayNumber & “rd”
Case Else
DayName = DayNumber & “st”
End Select
These could all also be done with layers of If-Then-Else statements, but Select Case is usually easier to read, and also has the advantage of easier and more explicit definitions of range.
Applying Select Case to stamp duty land tax
We are going to make a custom function that computes basic stamp duty land tax when given an input consideration. We’re using the rates from HMRC for basic purchases made after 1 October 2021:
We are going to approach this by taking the price of the property as our input, and then calculating for each slice. The mathematical approach for the higher slices is to calculate the total SDLT due on all previous slices, plus the marginal amount from the current slice.
Here’s what that looks like in practice:
Public Function SDLT(Price As Range) As Long
Select Case Price
Case 0 To 125000
SDLT = 0
Case 125001 To 250000
SDLT = (Price - 125000) * 2 / 100
Case 250001 To 925000
SDLT = 2500 + (Price - 250000) * 5 / 100
Case 925001 To 1500000
SDLT = 36250 + (Price - 925000) * 10 / 100
Case Else
SDLT = 93750 + (Price - 1500000) * 12 / 100End Select
End Function
And here’s what it looks like in practice:
You can test out the function for yourself, or take a look at the VBA code behind it, by downloading the accompanying file.