VBA: fix “compile error: expected variable or procedure, not module”

I recently had to edit somebody’s VBA macro in Excel for a recurrent data cleaning task. The code was a mess because large chunks of it were created using Excel’s macro recorded. While structuring the code in subroutines and modules, I ran into an error. In this blog post I would like to elaborate on it.

The code that I inherited contained a long list of poorly named modules with even poorer subroutines and functions inside it. I started restructuring the code base in something more understandable. Once I ran the macro, I was presented the following error.

Compile Error: Expected variable or procedure, not module

Apparently, the compiler expects a variable or procedure, but it received a module. Apparently, the solution is extremely simple: it is not exactly smart to give subroutines the same name as your modules.

VBA allows subroutines and function to have the same names, as long as they are declared in different modules. By preceding a subroutine in a call with its module, one can specify which subroutine needs to be evaluated.

Let’s say we have a module editValue and it contains a subroutine editValue.

Call editValue 'Produces an error
Call editValue.editValue 'Works

The first line of code will fail because editValue is first evaluated as the module name, and modules cannot be called. The second line will work, because we specified the module explicitly, which ensures editValue is evaluated as a subroutine.

Happy scripting!