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.
Macros Randomly Experience Errors
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:
- Stop the macro by clicking “End”
- Search the Microsoft website for information about the error by clicking “Help.” (Unfortunately, this sends you to the Microsoft website, where you’ll find a lengthy list of potential causes and solutions to resolve the “Subscript out of range” issue.)
- Once you have an idea of how to fix it (or think you do), go into Excel’s Visual Basic Editor via the “Debug” button.
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.
Debugging VB Code Demands a Lot
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:
- Close Excel entirely
- Make a backup of the Registry (go to Microsoft’s website for directions)
- Locate the applicable Registry Key on Microsoft’s website
- Create a new DWORD in the Registry
- Name the DWORD
- Open the DWORD editing box
- Change the DWORD value
- Close the registry
- Reopen Excel
- Test to see if the F8 key is working
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.
Conclusion: PROPRICER is Easier and Safer than Excel Using Macros
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.