Error when calling subroutines "Expected Variable or Procedure, not Module."
Solution 1
Background
When Excel creates modules it names then Module1
, Module2
and so on. This can get confusing if, like me, you divide your routines into sets: Global, Task1, Task2 and so on.
Not everyone seems to know you can rename Excel modules. This is made obvious in Access where you are asked for a module's name. For Excel you have to discover this facility for yourself.
Select the module, click F4 and the modules properties will appear in a floating window just as it does for a form. The only property is Name
which you can change to anything that conforms to the rules for a variable name. Using this facility I rename my modules: Global
, Task1
, Task2
and so on, which allows me to easily identify the module I want to work on today.
Limitation
I believe you have encountered the one limitation with the choice of name for a module.
If I rename a module Task1
then a sub, function, or global variable within that module named Task1
is invisible from outside.
The error message "Expected Variable or Procedure, Not Module" is to be expected if the module containing sub schedule_3_day(ByRef ...
is named schedule3_day
.
Solution 2
If the module and the macro have the same name do this:
module.macro
So if my module and macro are both called FreezeColumnFilter then the code would be:
FreezeColumnFilter.FreezeColumnFilter
uncertaintea
Updated on June 08, 2022Comments
-
uncertaintea over 1 year
I am trying to create a subroutine that will call one of a couple of other subroutines.
When I use a call inside an if statement I get an error:
Expected Variable or Procedure, not Module.
Here's the call procedure:
Call schedule_3_day(shift1, ActiveWorkbook.Sheets("Employees"), ActiveWorkbook.Sheets("3 Day Template"))
Here's the function to which it refers.
Sub schedule_3_day(ByRef sourcesheet As Worksheet, ByRef employeesheet As Worksheet, ByRef template As Worksheet)
On it's own, i.e. when it is a self contained subroutine, schedule_3_day works as intended. I am trying to alter it to pass user defined variables into it.