- Basic knowledge of vba
- Use of tables/named ranges
When working with workbooks on and off, sometimes months between revisits to the workbook it can be hard to remember the data structure and formula set up.
This is how I work around this problem and ensure that I within a few minutes will have an overview of the calculations done.
In this example I will show how a formula for calculating bonuses can be simplified.
Formula appearance without table or UDF
The logic in this formula can be hard to extract at first glance.
Formula appearance with table and no UDF
=IF(SUM([@[Yearly sales]]/[@Salary])<=1;0;IF(SUM([@[Yearly sales]]/[@Salary])>=3;SUM(([@[Yearly sales]]-[@Salary])*0,03);IF(AND(SUM([@[Yearly sales]]/[@Salary])>1;SUM([@[Yearly sales]]/[@Salary])<3)=TRUE;SUM(([@[Yearly sales]]-[@Salary])*0,02);1)))
With tables it just got a bit easier to read as you are informed of the cells used in the calculation
Formula appearance with table and UDF
User defined function inserted in a module:
' I will rather have long and descriptive function titles than short and undescriptive titles
Function calculate_salary_to_sale_ratio_and_return_bonus(yearlySales As Double, salary As Double) As Double
Dim salary_to_sale_ratio As Double
Dim bonus_factor As Double
Dim return_bonus As Double
salary_to_sale_ratio = yearlySales / salary
Select Case salary_to_sale_ratio
Case 1 To 3
bonus_factor = 0.02
Case Is > 3
bonus_factor = 0.03
bonus_factor = 0#
return_bonus = (yearlySales - salary) * bonus_factor
calculate_salary_to_sale_ratio_and_return_bonus = return_bonus
Use of the function
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.