The Evolution of Excel in Engineering
Modeling complex engineering tasks has evolved over the years. I recall both how impressed I was when my old boss used a slide rule, and his disdain when I would reach for a calculator. But as time passed, we junior engineers shifted from paper (and calculators) to writing computer code to compute loads, quantities required, the amount of stress-bearing, and the like.
Many of the engineers in my early career used Fortran and C. These tended to be small, one-off, solutions that might have calculated a load, density or stress point on, say a beam. A senior engineer would supply the parameters, and then we juniors would get time to run the computation.
All of this would be pasted back into specifications and platts.
Ironically, we were also using a new spreadsheet software that we got from the accounting department. They had pestered us to enter data into Lotus 123 or SuperCalc. And from there they could provide invoices, payment schedules, or manage accounts payable.
Spreadsheets though are also useful for many engineering-related tasks, more so, as the sheets themselves are flexible, offering a quick and easy way to manage changing data collection requirements. With a product like Excel we could:
- rearrange data on sheets and in workbooks;
- link sheets that allowed 1 sheet to summarise part level inventory on another;
- plan out the days when components would be needed;
- write formulae to project demand and forecast when to reorder; and,
- provide up-to-date reports on the progress of an engineering project.
But the best aspect was, built into Excel, was that we could write our own functions. Doing such allowed us to:
- trace the way that we calculated something;
- create test beds so that we could determine if we had thought of every possibility;
- re-use multi-step computations elsewhere; and,
- share solutions.
With this programming capability, we could replace the former, one-at-a-time, compute this for a spec task with models that allowed us to plug in a lot of data and then report tensile strengths, stress factors, and the like. But more so, we could also run what-ifs scenarios. And this meant we could find more lucrative solutions through alternative materials.
Over time, several companies sprang up to offer off-the-shelf solutions. I recall in the late 2000s, XyoTech was both training other engineers, and also supplying models and tools that one could incorporate into one’s own work.
This was in the early days of the internet; it was difficult then, still to find experts in the field. More companies were looking to sell their own, bespoke CASE (Computer-Aided Software Engineering) solutions which were both costly, and required months of training and experience to master. With the VBA routines provided by external vendors, others, such as the VPI Technology Group could more rapidly deliver solutions to their clients, cutting costs.
Today, you can find Excel and VBA used throughout the engineering domain:
- Specification Development – collecting the requirements, quantities, price points, etc. that a project might require;
- Engineering and Design – creating the charts for reporting, computing loads, strengths, and other model estimates;
- Prototyping – allowing one to calibrate models with real-world data, to improve the overall utility of the underlying VBA programming;
- Certification and Testing – Providing uniform data, formatted as required by the ratings agencies; and,
- Manufacturing – providing the formulae to track material requirements, duration of the process, the quality achieved, etc.
However, in the past decade, the microcosm of VBA has come under threat. Cybercriminals have discovered that they can easily encapsulate viruses in Excel VBA code. By either intercepting and modifying a spreadsheet, or posing as a trusted source, malevolent actors can trick the unsuspecting engineer to infect his or her own computer (and possibly then, the entire company).
To combat this danger, the software industry has evolved security measures: Emails themselves were checked for suspect code; when opening a spreadsheet sent from the internet, it would be tagged as potentially suspect; authenticity certificates were added to the Excel spreadsheets, and so more.
However, criminals still manage to get through. Over time, this (along with its age) has brought VBA into disrepute. Security has unfortunately been elevated to become a core feature of programming.
VBA, is also no longer, current and many see it as a dying programming language. More engineers today turn to a more modern language such as C# or Python and either create an Add-In or process the Excel document.
For these languages, there are burgeoning eco-systems providing more tools that can be freely downloaded from the equivalent of the AppStore: GitHub, Nuget or PyPI. And like the AppStore, this has simplified the early challenge of finding a tool to solve a problem that in the past required several junior engineers to solve.
However, the underlying utility of VBA is to quickly prototype a solution right next to the source data. One can, with just a Shift-F11, switch between the sheet and the programming, stepping through the calculations and seeing the ultimate result. Until one finds a way to offer the same with Python, I expect the close relationship between Engineers, Excel and VBA to carry on.
