Calculate Sliding Scale or Bracket Tax. VBA Find Method to Find Dates

WHAT'S NEW AT OZGRID |EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS WHAT'S NEW AT OZGRIDOur consultancy side of our business is now running better than we expected. As such we have taken on some new members to our development team. If you would like to utilize Ozgrid for some development work, please follow this link: Excel Consultancy

Excel Hacks 2
O'Reilly has contacted us again asking if we would like to update the extremely successful Excel Hacks . The feedback we still get for our book is very encouraging and has made our choice of whether to author the new Excel Hacks book or not, a simple one. That is, we wouldn't have it any other way!

O'Reilly intends to publish Excel Hacks 2 around the same time as Microsoft release their next version of Microsoft Office. Which, apparently is due about mid to late 2006. We will keep you all updated on this as we move along.NEW EXCEL NEWSLETTER

There is a new newsletter in Town and focuses on more basic Excel tips than what is generally covered here. It is sent in PDF format and if interested you can join by sending your request to: xlpro-at-visualbasicsolutions.com Tell John Dave from Ozgrid sent you! Be sure to replace -at- with @

If you prefer, these key numbers, can become Named Constants as opposed to Named Ranges . For example, to create the Named Constant: Level1Tax you would go to Insert>Name>Define and type: Level1Tax in the Names in workbook: box, then: =12000 in the Refers to: box, then click Add.

We can go one step further toward simplifying the calculation by using Named Formulas for each tax level calculation. After doing this, we can then use;

=IF(A1>Level4Tax,Level4TaxCalc,IF(A1>Level3Tax,Level3TaxCalc,IF(A1>Level2Tax,Level2TaxCalc,IF(A1>Level1Tax,Level1TaxCalc,0))))

Here are the steps to achieve this. BTW, this method can also be used to overcome the 7 nested IF Function limitation .

1) Create Named Ranges, or Named Constants that will hold the figures needed. See screen shot above.

2) Place you Gross pays in cell A1 down.

3) Select cell B1 and go to Insert>Name>Define.

4) In the Names in workbook: box type: Level1TaxCalc Then, in the Refers to: box type: =SUM((A1-Level1Tax)*Level1TaxRate) Then click Add. **Note how we have referred to cell A1. This now makes the Named Formula (Level1TaxCalc) always look on the same row in the immediate column to the left for the gross pay.

5) Repeat step 4 using the names and formulas shown below;

Level2TaxCalc
=SUM((A1-Level2Tax)*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

Level3TaxCalc
=SUM((A1-Level3Tax)*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

Level4TaxCalc
=SUM((A1-Level4Tax)*Level4TaxRate,Level3TaxAmount*Level3TaxRate,Level2TaxAmount*Level2TaxRate,Level1TaxAmount*Level1TaxRate)

It is important to know that Level*TaxCalc will ALWAYS look on the same row, but left column for the gross pay figure.

Download Working Example

EXCEL VBA TIPS AND TRICKS

The codes below are for Custom Excel Functions that have been written to calculate tax based on a sliding scale, or bracket tax. The first one is based entirely on the built-in method that can be used to calculate sliding scale tax and requires named ranges , or constants. The second is more self contained and requires no named ranges, or constants. Download Working Example

To use either codes, go to Tools>Macro>Visual Basic Editor (Alt+F11) and then Insert>Module and paste the code near the bottom of this page.

The first would be used in any cell like: =TaxPayable(A2) where A2 has the gross amount.

The second would be used in any cell like: =Tax_Payable(A2,12000,22%,13000,25000,30%,7000,32000,38%,13000,45000,45%) where A2 has the gross amount.

To make this easier to read, I have placed the cell names next to their named cell. In the first code below, only the grey cells are being used. The last column (Amount of Tax Payable on) is the result of subtracting the Level*Tax (one row down) from the Level*Tax on the same row. For example, $13,000.00 (Level1TaxAmount) is derived by subtracting Level2Tax (25000) from Level1Tax (12000). That is:

=Level2Tax-Level1Tax

Function TaxPayable(Amount As Currency) As CurrencySelect Case Amount   Case Is > Range("Level4Tax")        TaxPayable = ((Amount - Range("Level4Tax")) * Range("Level4TaxRate")) + _                            Range("Level3TaxAmount") * Range("Level3TaxRate") + _                            Range("Level2TaxAmount") * Range("Level2TaxRate") + _                            Range("Level1TaxAmount") * Range("Level1TaxRate")                               Case Is > Range("Level3Tax")        TaxPayable = ((Amount - Range("Level3Tax")) * Range("Level3TaxRate")) + _                            Range("Level2TaxAmount") * Range("Level2TaxRate") + _                            Range("Level1TaxAmount") * Range("Level1TaxRate")                                                           Case Is > Range("Level2Tax")        TaxPayable = ((Amount - Range("Level2Tax")) * Range("Level2TaxRate")) + _                            Range("Level1TaxAmount") * Range("Level1TaxRate")                               Case Is > Range("LowTax")        TaxPayable = ((Amount - Range("Level1Tax")) * Range("Level1TaxRate"))                               Case Else        TaxPayable = 0  End Select  End Function

=TaxPayable(A2) where A2 has the gross amount

Function Tax_Payable(Amount As Currency, L1_Tax As Currency, L1_Tax_Rate As Currency, _L1_Taxable_Amount As Currency, Optional L2_Tax As Currency, Optional L2_Tax_Rate As Currency, _Optional L2_Taxable_Amount As Currency, Optional L3_Tax As Currency, Optional L3_Tax_Rate As Currency, _Optional L3_Taxable_Amount As Currency, Optional L4_Tax As Currency, Optional L4_Tax_Rate As Currency) As CurrencySelect Case Amount   Case Is > L4_Tax        Tax_Payable = (Amount - L4_Tax) * L4_Tax_Rate + L3_Taxable_Amount * L3_Tax_Rate + _                            L2_Taxable_Amount * L2_Tax_Rate + L1_Taxable_Amount * L1_Tax_Rate                               Case Is > L3_Tax        Tax_Payable = (Amount - L3_Tax) * L3_Tax_Rate + L2_Taxable_Amount * L2_Tax_Rate + _                            L1_Taxable_Amount * L1_Tax_Rate                                                           Case Is > L2_Tax            Tax_Payable = (Amount - L2_Tax) * L2_Tax_Rate + L1_Taxable_Amount * L1_Tax_Rate                                     Case Is > L1_Tax           Tax_Payable = (Amount - L1_Tax) * L1_Tax_Rate                                       Case Else        Tax_Payable = 0  End Select  End Function

=Tax_Payable(A2,12000,22%,13000,25000,30%,7000,32000,38%,13000,45000,45%) where A2 has the gross amount.Excel VBA Find Method to Find Dates. Find Excel Dates in Excel

The Find Method is an excellent method to use in Excel VBA macro code. It is many hundreds of times faster than a VBA loop , which is often erroronously used to locate data. However, things become somewhat tricky when using the Find Method to Find Dates. Those that use a US date format do not have the problems that us outside the US encounter when using Find to locate dates. In other words, if your local date format is NOT mm/dd/yyy (set in Windows) you can often run into problems.

Even recording a macro using Find to find a date will often bug out when it is played back. Normally the debug message is: "run time error '91': Object variable or With block variable not set." Yet you know the exact date exists on the Worksheet.

The trick is to ensure you use a true Excel date and format it the same as your default Excel date format, set in Windows. Unless changed, this will be "Short Date" or "Long Date". Most often the former. Don't pay too much attention to how Excel displays the date on your Worksheet as it is only a displayed value. What we need is the true underlying value of date that Excel is using to store the date. To see this, select any cell that houses a date and then look in the Formula bar. This is what Excel stores the date as.

The Excel macro code below can be used to locate a date on the current worksheet and should show you how one can use the Find Method in Excel VBA to find dates. Note also the use of CDate Conversion Function .

Sub FindDate()Dim strdate As StringDim rCell As RangeDim lReply As Long    strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _                Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)    'Cancelled    If strdate = "False" Then Exit Sub    strdate = Format(strdate, "Short Date")    On Error Resume Next        Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)    On Error GoTo 0    If rCell Is Nothing Then        lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)        If lReply = vbYes Then Run "FindDate":    End IfEnd Sub

OzGrid Excel and VBA Newsletter

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS