Does anybody use any tools to make the production and checking process better?
Yeah, I created a number of macros that use cell highlighting to identify which cells have hardcoded constants in formulas, constant in cells, cells that have conditional formatting, cells that have data validation, cells that have array formulas, non-blank cells, etc. All run from an add-in on the ribbon, you can toggle the highlighting on and off at will. Very useful to me at least in dissecting others spreadsheets and identifying fopars such as hardcoding variable constants in formulas.
Using named ranges in excel can make cell formulas much easier to read and backcheck.
If you're not doing this, you're not using Excel right....
I find the easiest is to develop a function for your equation in the VBA interface as this makes it easier to troubleshoot...
Definitely, the other thing this helps with is if you did make an error, you can basically update it in the VBA and it will obviously reflect through to any cell that uses the function. Rather than playing wack a mole trying to find the error in numerous cells with similar equations.
Don't underestimate the power of some beautifully written comments documenting the code.
The re-useability aspect and maintainability aspect of doing it this way is also a no-brainer to me.
Other tips from me....
Make use of dynamic array formulas for repetitive calcs. Then you're only updating one equation (as opposed to dragging formulas around to repeat the calculation.
I have a personal setup where each spreadsheet in a template directory has an MD5 hash, so we can tell if someone inadvertently altered the original template. I also implement version control, having a master spreadsheet that is queried each time someone opens a spreadsheet to ensure you know when you are using an older version.
Similarly, I use GitHub to store all the spreadsheet templates with the VBA code automatically extracted via some code I wrote see
here and
here for the code.
Additionally, we lock the VBA down so no one can access it to make unauthorised changes, the spreadsheets are also locked down to our own network. Open one off network and certain things just won't work, and in some cases the file will be killed.
I find the person who wrote the spreadsheet is generally the best person to check it, check as they go. Produce some independent calcs to cover all outcomes, you check these, spreadsheet follows these. Usually this highlights either an error in the spreadsheet or the hand calcs and you investigate and correct. This serves as a permanent verification record, and also provides examples people can learn/understand what is being calculated. It also helps if you need to modify a spreadsheet at a later date in some instances.
Don't underestimate the usefulness of the built in
spreadsheet compare program that is sitting in the excel install directory. It's super useful for checking changes between two spreadsheets.
Make use of the trace dependants and precedents tools, they are super useful for tracing how the calculated cells have been pulled together.