Here are some of the most common VBA/macro coding problems I find in Excel pricing tools:
direct cell references (e.g., cell "E5") that will error out if cell E5 is ever moved to F7. Make E5 a named range like "Project_ID" instead.
lack of error checks. If the user changed the "Budget Grid" tab name to "XYZ Budget Grid", what will help them more- a bizarre Microsoft error code, or a custom message box letting them know what happened?
the macro is doing things that should instead be done in the file itself. E.g., filtering logic should be done in the file itself, so that the macro just reads the "x's"; macros should rarely be applying much formatting, tabs should already be arranged in the order you want them to be in the export, etc.
"Event" macros exist in tabs that may be exported, which then requires you to write code that removes other code, which should typically be avoided at all costs.
Redundant references- keep your code DRY (don't repeat yourself). If your code references the "Input" tab multiple times, create a variable that references that tab name once so that if you later change the tab name in your template, you only need to change it in one place in your code.
Lack of comments- long macros are coded out without comments explaining what different parts are doing. Be nice to those who in the future have to figure out what you programmed.
