We constructed spreadsheet based tools which enable designers to use engineering correlations for both rating and design. Spreadsheets are valuable because of the very familiar user interface. Our experience and focus will speed-up your project.
We use structured spreadsheets and Visual Basic for Applications (VBA) to develop robust design tools. Design tools require changes and updating over time. Therefore, a structured approach is needed to understand the background and basis. It is easy to develop a spreadsheet for a calculation, validating the calculations and documentation requires a higher level of skill.
Everyone uses spreadsheets, but building a re-usable tool requires a different mindset
Characteristics of a great spreadsheet tool includes
1. Easy to use (intuitive) user interface – few words- more information in tool- hyperlinks
2. VBA user defined functions to document logic of calculations and complex calculations
3. Build in user conversion to enable input of many units
4. Limit input to specific ranges- data validation
5. Flags or warnings for out of range values
Why use VBA user defined functions?
- The syntax for procedural programming is easy to learn for anyone that has programmed in nearly any language. (C, Basic, Fortran, Python)
- Functions can be exported from a spreadsheet allowing use in other sheets. The BAS files can be used as a library for future work.
- Easy to comment a procedure which allows others to easily understand the basis of the calculation. For example, the source of an equation used in a calculation. Any code that is used over time will be modified at some point. Comments can greatly reduce the effort for these modifications. Best practice is to write comments as the function is being developed.
- Can do some operations such as iterative calculations that are cumbersome or nearly impossible in cell formulas.