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

Question

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.

Have you named your modules? This is the error message you get if you call a procedure in a module when both have the same name. — Tony Dallimore
– Tony Dallimore, Commented Jan 1, 2013 at 23:27
@TonyDallimore Please post your comment as an answer, so the OP can accept it. — Daniel
– Daniel, Commented Jan 2, 2013 at 0:02
@Daniel. I don't normally post two sentence answers but you are correct, I should have posted this as an answer. I have added a bit of background which may be useful to other readers. — Tony Dallimore
– Tony Dallimore, Commented Jan 2, 2013 at 9:23
@TonyDallimore I understand, I don't like to post 2 sentence answers either. And would also add more detail. :) — Daniel
– Daniel, Commented Jan 2, 2013 at 13:46

Tony Dallimore · Accepted Answer · 2013-01-02 09:20:11Z

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.

MacroHack · Accepted Answer · 2020-07-16 15:16:11Z

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

answered Jul 16, 2020 at 15:16

MacroHack

311 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related