LinkedIn facebook


VBA - Functions* 31/01/2002

Creating function is useful as complicated formulas can be made easier in code than in a spread sheet. Formulas can be protected so that users cannot see or modify them. The example I use will calculate tax using the Select Case Statement. Here's the scenario.

First $2500 is tax free.
Next $2500 is taxable at 5%.
Anything above $5000 is taxable at 10%.
In cell A1 type Income and in cell B1 type in your income in numbers say $20000.
In cell A2 type Tax payable and in cell B2 type =tax(B1).
Put the following code in a module. The tax payable here would be $1625.

Public Function tax(income As Single)
Select Case income
Case Is <= 2500
tax = 0
Case Is <= 5000
tax = (income - 2500) * 0.05
Case Else
tax = (income - 5000) * 0.1 + 125
End Select
End Function