I have an invoice template that has a formula to compute tax, namely
If I put a ‘T’ in a certain column, this will compute tax on that amount. I used a named constant, cnsStateTaxRate, for some reason that escapes me now. I probably thought that I was some kind of decent developer and that decent developers used named constants instead of literal values in their formulas.
There probably are some advantages to it. It makes the formula easier to read, for one. If, instead of cnsStateTaxRate, it said .055, it may not be abundantly clear that it’s a calculation of the state taxes. If I were to use that same named constant elsewhere in the sheet, I could change the rate in one place rather than everywhere it occurs. This particular constant is only used here and since the cell next to it says “State Tax:”, it’s pretty obvious what it does.
Recently, I had to add other taxing authorities to this template, which is why I’m suddenly interested in my motives for creating the constant. It’s not hurting anything, but now I feel like I’ve forced myself to go down a road, down which I may not want to go. When the taxing authority is different, do I change the named constant? That’s what I did.
The tax information is stored in an external database. When the user selects a Job ID, the Tax Group is retrieved from the Job record. Then the city and state information are retrieved from the Tax Group record. Once the proper information is retrieved, I change the defined constants’ values to the correct information. I started by creating a user defined type in a standard module.
sStateName As String
sCityName As String
dStateRate As Double
dCityRate As Double
End Type
This will hold the information from the external database until I can put it in the defined constants. I use the Worksheet_Change event to determine if the Job ID has been entered or changed.
Dim sTaxIdFromJob As String
Dim udtTxGrp As TaxGroup
sTaxIdFromJob = GetTaxIdFromJob(Target.Value) ‘function that accesses Job record
udtTxGrp = GetTaxesFromAR(sTaxIdFromJob) ‘function that accesses Tax Group record
Me.Parent.Names(“cnsCityName”).Value = _
“=” & Chr$(34) & udtTxGrp.sCityName & Chr$(34)
Me.Parent.Names(“cnsCityTaxRate”).Value = _
“=” & udtTxGrp.dCityRate & “/100*” & StateFactor(sTaxIdFromJob, udtTxGrp)
Me.Parent.Names(“cnsStateName”).Value = _
“=” & Chr$(34) & udtTxGrp.sStateName & Chr$(34)
Me.Parent.Names(“cnsStateTaxRate”).Value = _
“=” & udtTxGrp.dStateRate & “/100*” & StateFactor(sTaxIdFromJob, udtTxGrp)
End If
After I wrote this, I questioned if this was the right thing to do. I had to create cnsCityName and cnsStateName because those strings were simply hard-coded onto the spreadsheet. They never changed, even if the rates could. My other options include changing the formula/cell directly, and storing the current tax information on a separate sheet and referring to that range in the formula, rather than a constant (that’s not really constant, is it?). What do you think? How would you approach it?
I think I’d just show the state tax rate on the invoice in a celll with a lookup to another sheet with the various rates.
I would suggest having a table in the workbook and use lookups which would be based on the province / country if that is required.
More common situation for me at least is with respect to currencies and cross rates to NZD against AUD, GBP, EUR, USD, JPY, RMB etc. Sometimes a client will come back and say that, for example, transactions with China will use USD rather than RMB which means I just change the rates for that country.
HTH,
Alan.
I would use a lookup table as well, with one column for StateRate and another for CityRate. The formula could sum the two rates to calculate the applicable rate.
I’m no Excel guru. I’m just a tax lawyer. I always put my tax rates in lookup tables because the rates change all the time. (I was going to say that the rates change “constantly”, but that seemed oxymoronic. Or something.)
Are you guys saying that I should manually key in the tax rates in a separate table? I already have the tax rates in an external table (accounting system). If I have them separately in Excel, then I’ll have to change them in two places, and that ain’t happening.
I could create external data tables (2) on other sheets and use lookups, but I don’t know if that’s better than what I have. When do I refresh the external data tables? On Open? If I open an invoice from a year ago, and the rates have changed, then the invoice won’t be right. Now, the rate will only change if I change the job id. How many jobs should I include in the external data table? All of them? Even the ones that have been closed for five years?
Dick,
It sounds like you only use each constant once in a sheet. If that’s true, I think it would be more direct to just put the values from your external file into named ranges in the sheet like
Me.Range(“StateTaxRate”).Value = udtTxGrp.dStateRate/100*StateFactor(sTaxIdFromJob, udtTxGrp)
Or you could do a lookup and then hard-code the cell value like
Me.Range(“StateTaxRate”).Value = Me.Range(“StateTaxRate”).Value
Is that any better?
On second thought, I guess the second one won’t work with what you describe, because of the issue of regenerating the invoice at a later date, after a possible rate change.
Dick,
You may need to set up a “finalize” or “close out” procedure to activate after the invoice has been completed. I assume the invoices are saved as separate files. If so, a procedure that converts the formulas to hard numbers would prevent you unwanted updating problem. Be sure to include a safe stop that prevents the original master file from being “finalized.