{% set baseFontFamily = "" %} /* Add the font family you wish to use. You may need to import it above. */

{% set headerFontFamily = "Open Sans" %} /* This affects only headers on the site. Add the font family you wish to use. You may need to import it above. */

{% set textColor = "#565656" %} /* This sets the universal color of dark text on the site */

{% set pageCenter = "1200px" %} /* This sets the width of the website */

{% set headerType = "static" %} /* To make this a fixed header, change the value to "fixed" - otherwise, set it to "static" */

{% set lightGreyColor = "#f7f7f7" %} /* This affects all grey background sections */

{% set baseFontWeight = "" %} /* More than likely, you will use one of these values (higher = bolder): 300, 400, 700, 900 */

{% set headerFontWeight = "" %} /* For Headers; More than likely, you will use one of these values (higher = bolder): 300, 400, 700, 900 */

{% set buttonRadius = '' %} /* "0" for square edges, "10px" for rounded edges, "40px" for pill shape; This will change all buttons */

After you have updated your stylesheet, make sure you turn this module off

PROPRICER Insights Blog

Stop Going Crazy with Excel Macros!

Pencil Posted by PROPRICER Team

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.

General Atomics Customer Case Study

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.

macros error.jpg

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 Lotexcel macros

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. 

Macros Pose Evil Threats

Those aren’t the only problems you’ll face when you rely on macros – recent years have seen a resurgence of macro-based attacks focused on spreading malware and ransomware. According to Symantec's 2017 Internet Security Threat Report,  the most common attack vector of 2016 had attackers duping victims into downloading either a JavaScript or Microsoft Office file with macros.

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.

Topics: Microsoft Excel

Share this: