Excel includes a wealth of worksheet functions and analysis tools that can be combined to perform very complex analysis.
However, these features can quickly get out of hand. Worksheet functions work as an analysis coding language which does not scale very well.
In particular,
- The spreadsheet logic is hidden in cells and not visible by default.
- The analysis logic is distributed over very many cells.
- Variables names are typically not used, thus obscuring the semantic meaning of cells.
- The flow of the computational logic can be hard to determine.
- Changing or extending analytic logic often involves major modifications to the functions in the cells.
- Complex function nesting in cells is common and confusing.
- Certain computational structures such as looping and recursion are almost impossible.
These problems make large, complex Excel models very difficult to maintain and expand.
Excel functions may be useful for typical tasks such as ledgers and for experimental computations, but for complex
analytic tasks, solutions should be moved to VBA. This provides a structured, object oriented and procedural
solution that lends itself to standard development techniques.
Think Tank Enterprises, Inc. has ironed out numerous spread sheet projects and has developed very many
VBA solutions for a wide array of analysis requirements. Final products typically use Excel worksheets
for data input and output, including interactive graphical displays. VBA is used to do the heavy computational lifting.
The results are
- Clearer computational logic.
- Capacity for more voluminous and complex data
- Solutions that are easier to expand.
VBA-based solutions also open up a host of additional features as described elsewhere on this site. Look around!
|
