Replacing an entry with a calculated value

The Starting Point

Tom Urtis, at Atlas Programming Management Inc., recently published a VBA hint for changing the list/sales price of an entry into a plus-tax amount.  

The worksheet is quite simple.  It has a title, a spot for the sales tax percentage, and an area for adding items and their prices.
The code is obviously professional and tested.  It turns EnableEvents off and on at the right times, checks for errors in inputs, uses Intersect, etc.  If nothing else, it's instructive, compact, (although plenty of comments to help the reader through), and more than a small step up from some of the examples I've seen elsewhere.

As I've said elsewhere, my favorite tips are those that make me think of the next step(s) I might take with what is offered.

First Step:  Use Names

As may be obvious by the number of times I mention using Names on worksheets and in workbooks, the first thing I noticed that range and cell addresses were hard-coded.  Thinking about it a little more, I think many authors use hard-coded addresses because all Excel users are familiar with them and it removes the burden of explaining Names with each post.

So, I named the cell with the sales tax rate "Tax_Rate"  and the range where the user might enter the sale price of an item "Price_entered_Plus_tax".

Second Step:  .Formula instead of .Value

After checking to make certain the new entry was in the right range, a sequence of cells in column B, and was numeric, the code calculates the out-of-pocket expense for an item of the price entered.

• On Worksheet_Change Event...
• Exit the sub if the new change is not in the expected range
• Clear the cell, display a message directing the user to enter a numeric value, then exit the sub if the value in the cell is not numeric.
• Once all error checking is complete, create and populate variables for the TaxRate and the original value
• Calculate the price-plus-taxes and put it into the cell

This portion of the code looks like this    Dim dblFactor#, NewVal#    dblFactor = Range("B2").Value    NewVal = .Value

    Application.EnableEvents = False

    .Value = (1 + dblFactor) * NewVal

    Application.EnableEvents = True

NOTE: The only time I declare variables in the middle of a sub or function is if I am using them for test purposes.  I understand the philosophy of declaring variable just before use, it's just not something I practice.

I have found I can simplify the code and come out with a more informative answer.

    .Formula = "= (1 + Tax_Rate ) *" & .Value

The result is the same as before, but now I can see the original value in the formula and the sheet will recalculate if I change the value in Tax_Rate.

The downside is more slowing of recalculation as each of these cells wil now have a formula instead of a value.

Other Applications

House Buying

As I was looking this over it reminded me of house buying.  The amount we had for a down payment was fixed.  The number of payments was the same for each choice, and the interest rate was the same.  If we could have entered the sales price into the spreadsheet and had a simple column of meaningful numbers to compare, I believe sorting through would have been simpler:  It's much easier to feel the impact of another $100/month than another $10-20,000 dollars.

Car Buying

This calculation may go backwards from house-buying.  Interest rates and lease v purchase muddy the water too much.  Put in the monthly payment and get the Net Present Value based on assumptions of a relationship between payment and sustained value at end of lease.  This may take more work, because the monthly payment is still a factor to consider.  Hmmmm...hmm...hmmmmm?