Explanatory Formulas

[Steffen Thomsen]

Requirements:

- 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

Please Login or Register  to view this content.

The logic in this formula can be hard to extract at first glance.

Formula appearance with table and no UDF

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Use of the function

Please Login or Register  to view this content.

This tells you exactly what is happening at a first glance, and you can allways go deeper into the code to read the specifics.

Attached is a file demonstrating all three scenarios

[romperstomper]

And then when the bonus methodology changes, your function name makes no sense.
IMO it would be better to have a function called simply GetBonus, and comment it to explain the calculation.

[Steffen Thomsen]

@romperstomper

Valid point though IMO if the function methodology changes, so should the function name, im a strong supporter of selfexplanatory function names.

The post though was more directed at the concept of using UDF's and tables to give an overview than the specific function.

[romperstomper]

im a strong supporter of selfexplanatory function names.

As am I, but not to that extent! I think the name should describe what it does, not how.

[Steffen Thomsen]

Actually im inclined to agree with you that the function name in this example is a bit over the edge, though it underlines the purpose of using this method.