Organizations that perform estimating and pricing on a regular basis are familiar with the pain of Excel Macros. In this post, we highlight several reasons to illustrate why it’s time to end your reliance on Excel.
Macros are miniature programs embedded in Excel to automate repetitive tasks. For example, an estimator might embed a macro to pull hourly rates from a database when estimating personnel and their cost burden for a project.
Keep this in mind as you read this blog: you launched a macro to work on an estimate.
Microsoft products often produce random performance lapses, and it’s not unusual for project estimators to contend with the pop-up box saying, “Run-time error ‘9’” and “Subscript out of range" when running macros.
To contend with this error (which you can expect from time to time), taking one of these three steps will probably derail your plans for the rest of the day:
To be clear, clicking “Debug” does NOT debug your code problem – it only lets you into the VB editor, where you’ll need your ‘code programmer’ hat.
Believe it or not, the simplest solution is to change the naming conventions within the VB editor. But if your project involves multiple spreadsheets, doing so would likely affect more than you anticipated. Furthermore, if the macro error rears its ugly head again for someone else, that person might also change the naming conventions and throw everything into disarray.
Most non-technical personnel debug typical macro errors using the F8 key (the ‘Step Into’ command) and sift through the code, line by line, to find error(s). Unfortunately, true to Microsoft form, sometimes that randomly stops working and opens another Excel file. In addition, it can skip the next line of code and jump to the next breakpoint or end of the procedure.
Now you have another problem to fix, and it won’t be fixed quickly. To fix it, you have to make a change in the Registry. Yes, the Registry. That’s a big risk, and you should never mess around in the Registry without first making a backup.
Here’s the rather painful process:
Remember, this was not your original goal. Your original goal was to create an estimate. Unfortunately, you tried to do so in Excel using a macro, which led to the need to debug a macro error using the F8 key which led to the need to back up and change the Registry. You did an awful lot of work … and yet haven’t achieved your original goal.
Since the VB script used by Excel is simple for programmers to work with, it’s easy for wrongdoers to develop macro malware that leverages Excel’s scripting mechanisms. While Microsoft has responded by disabling macros as a default setting, most Excel users re-enable them in order to work faster.
Estimating and pricing teams need speed, accuracy, and security when developing estimates and pricing, especially when doing so for government clients. The agony of working with Excel macros and the malware/ransomware threats they bring are causing a growing number of companies to switch to PROPRICER. Our industry-leading estimating and pricing software eliminates the agony of macros, and does so in a secure environment, helping you do more, faster, with fewer resources.
For more information about ending the agony of Excel macros, contact us today.