Automated Sales Tax Calculator done in Microsoft Excel 2010

This hub will provide an easy to use sales tax calculator. All you need to do is follow instructions to build it. Then it can be used by simply opening up the spreadsheet we edit and save, then hit keystroke “Ctrl + a”. What we will do is build a short macro in Excel using VBA (Visual Basic for Applications). VBA is included in Excel. In order to use VBA, you will need the Developer tab found within Excel. Here is how to display it. Open up Excel and do as follows, when the new spreadsheet “Book1" comes up:

1. File --> Options (click)

2. Customize Ribbon (click)

3. Check the “Developer” box and click “OK” at bottom.

4. You will now see “Developer” tab. Click it.

5. Section dealing with macros and VBA (Visual Basic) is now visible.

Now we will save the open workbook as an “.xlsm” file, which allows us to work in VBA. File --> save As, and a window will open up. Make file name = “taxcalc” and Save As Type = “Excel Macro Enabled Workbook”.

Now, as a check, close “taxcalc.xlsm” and re-open it. You may see the following warning (see picture area #1):

If it shows, we want macros enabled, so click on “enable content”.

Now we can write the macro which gives us quick and easy cost calculator including sales tax.

Click on “Developer” Tab. Over on far left, see the macro section. Do as follows:

1. Click on the “Record Macro” icon.

2. This window will come up (see picture area #2). Leave the name as is, and place an “a” where shown.

3. Hit “OK” and immediately click the “stop recording” tab (see picture area #3):

4. Click the “Macros” tab, and the macro window will come up again. Hit “Edit” and the VBA window will come up. You will see this on right (see picture area #4):

5. This is a blank macro we just created. Blank because there is no code (no program yet). See it has the keyboard shortcut “Ctrl+a” we set up. Once we copy/paste in the code I will provide, the program will run when we press the Ctrl and “a” keys at the same time. In order to make room for the code, place the mouse cursor to left of “End Sub” line, and hit “Enter” key 5 times to move it down. Then arrow key the cursor up 3 times and it should look like this (see picture area #5):

6. Now we can paste in code at the cursor. Copy and paste this code in:

On Error GoTo errorhandler

Dim bpstr As String, trstr As String, tstr As String

Dim bp As Single, tr As Single, t As Single, finddot As Single

bpstr = InputBox("enter the base price; i.e $10.50 enter as 10.50")

bp = Val(bpstr)

If bp <= 0 Then GoTo errorhandler

trstr = InputBox("enter the sales tax; i.e 7% enter as 7")

tr = Val(trstr) / 100

If tr <= 0 Then GoTo errorhandler

t = bp + tr * bp

t = Math.Round(t, 2)

tstr = Str(t)

tstr = Trim(tstr)

tstr = "$ " & tstr

tstr = Trim(tstr)

finddot = InStr(tstr, ".")

If (Len(tstr) - finddot = 1) Then tstr = tstr & "0"

MsgBox ("total price with tax = " & tstr)

Exit Sub

errorhandler:

MsgBox ("must have been a data entry error, try again.")

7. Once you do that, close the VBA window and re-save the Excel workbook. We need to save it to include all the changes we made.

8. Now press “Ctrl” and “a” keys at the same time, and the program will run.