How to change a global variable name in an Excel VBA project?

Question

If someone have to carry on working on an Excel VBA project/module after the previous developer left the company, how do they safely change a global variable name?

These global variable names can be problematic if they are misleading, misspelled, look like another variable, don't follow the chosen convention, etc.

Changing them with a Search/Replace is a problem because sometimes it's a word that appears in comments.

Changing them by Copy/Pasting is a problem because it's long and you can miss some, especially if there are a lot of occurrences or if you change it to something similar.

Is there a way to do this safely via the Excel "IDE", or via another tool?

Answer 1

What you need to do here is called a refactoring - you need to make a possibly dangerous change to the code, without affecting its behavior. Do it wrong and the code breaks!

Renaming an identifier that's used in one or more places, is a rename refactoring.

Most modern IDE's have such a feature (and several other refactorings). However the VBE was at the height of its glory well before Visual Studio was the full-featured tool it has become since then - heck, the VBE was Visual Studio (6.0) in 1998!

So you really have two options:

• Do the refactoring manually - the IDE's search & replace functionality (Ctrl+H) can be dangerous here, because it treats code as simple text, without semantic understanding: you need to review every single occurrence individually, or risk renaming an identifier that was not referring to the variable you're trying to rename.
• Use a 3rd-party tool - I don't know any VBIDE add-ins that understand the code deeply enough to allow safely refactoring VBA code, other than the open-source Rubberduck project, which I've managed since October 2014). This add-in parses your entire project, builds a symbol table, and lets you navigate and, yes, refactor/rename any identifier, automatically updating all call sites.

Note that Rubberduck is a very active open-source project, constantly improving. Parsing VBA is hard, and getting the VBE functionally on par with modern-day IDEs isn't a small undertaking, nor is it easy... but it's fun, and yep, it works.