Re: VBA
I once wrote a finite capacity system in Excel 6 for a pie factory. It had a forecast on the front and spat out a machine/process loading - ingredients, prep, make, bake, wrap and dispatch and delivery on the end. It took the into depot values from the forecast and ran the plant backwards, allowing for recipes, cooling times and quite a lot else. You input desired machine efficiencies - usually 80-95% except at Easter and Christmas when 110% was often needed followed by maint. There were several other desired parameters. Once the plan had run backwards, then the model would spit out what will not work. You change the constraints and run it again until it works. I had some ideas for making that automatic but a skilled planner is quite good at fixing the issues.
Two days work in Planning turned into an hour or so. Now you can run the Plan daily instead of weekly. It also spat out a labour plan and a forecast for the next month so that Manpower and co had advanced warning.
Each sheet of data always has the word "End" at the bottom of each column, in white text on a red background, 8 point font. My routines would always look for all those attributes when deciding when to stop running down a column. If it made sense, then all tables of numbers would be summed horizontally and vertically and compared. I had routines that I suppose would be called unit tests nowadays but I was a simple civil engineering grad doing IT in a pie factory in the mid '90s.
I'll never forget giving the factory manager a smart new labour plan. We ran old and new side by side for a few months and compared what worked. Generally both systems worked but mine took minutes to an hour or so to run instead of days. When a machine blew out or whatever a new plan was available within an hour.
This comment is grossly over simplified but should give you a feel for what can be done. Nowadays I run an IT company. I've seen some proper horrors in spreadsheet form.
Don't blame the tool, its the f'ing tool using the tool that cocks it up or makes it work efficiently.