Error when calling subroutines "Expected Variable or Procedure, not Module."

19,095

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

Share:
19,095
uncertaintea
Author by

uncertaintea

Updated on June 08, 2022

Comments

  • uncertaintea
    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.