Replace Those Megaformulas
In the
Sample Code section there is a
function which extracts a numerically formatted date from a text string of any
length. It’s a fine example of functionality not already in Excel, but custom
functions can also be used to ‘package’ existing Excel functionality. You can use VBA functions to replace MEGAFORMULAS.
Can you figure out what
the following formula does? Below is the spreadsheet using it.
=IF(B2="CA",ROUND(C2*0.0825,2),IF(B2="NV",ROUND(C2*0.0735,2),0))
It calculates the sales tax rounded to two decimal places on a purchase made
in any of three western states. Three states? Well, the individual who wrote the
formula only sells in CA, NV, and OR. Since Oregon has no state sales tax, they
just left a zero in the final IF clause. That’s a problem when they start
selling in Washington or any other state.
If you look at the spreadsheet, you might notice another little problem. Why
does the purchase in Truckee show no sales tax and the one in Bend show sales
tax? If you’d like to try to find out,
download the
file.
The formula in this example isn’t even a megaformula, it’s more like a
kiloformula and we already have problems creeping up. This is the essential
problem with megaformulas. They’re great when they work, but they’re just
horrible when they go wrong.
You can use VBA to create a custom function for your spreadsheet. The
function below can be used in the spreadsheet by simply typing
=Tax(B2, C2) into cell D2. Anyone looking at the
cell will know that sales tax is calculated in the cell, and when rates change
or new states and counties are added, the custom function can be updated in one
place rather than in a series of cells.
For more on using VBA, ask about our
custom training
classes.
Function Tax(strState As String, PPrice As Double)
As Double
'I Get It! Development does not actually know what
sales tax is
'from state to state and county to county. Do not use these
'figures for any actual calculations!
Select Case strState
Case Is = "CA"
Tax = PPrice * 0.0825
Case Is = "NV"
Tax = PPrice * 0.0735
Case Is = "OR"
Tax = 0
Case Else
Tax = PPrice * 100 'This will make unmatched states STAND OUT
End Select
End Function