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
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)
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?