Call to Re-Named Module [to match Sub() name] fails ?

C

CRayF

I have a module name question?

When I “Right click; View Code" on the worksheet, In the top left is
(Project – VBA Project) window; at the bottom I have a "Modules" folder and
in there I have 4 modules. 2 of which I seemed to have renamed and not “yetâ€
seeing a problem.

However, in the last module “Module3†It’s intended to be a sub module;
called by the worksheet module from 2 different points. *All curently runs
fine*.

The sub routing was created with the following name:
... Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
.... code...

From the Worksheet View Code, I call this sub routine with the following
command:
'------------------------------------------------------------------------
' [default/Changed!] Button - Re-Build Program Summary Template
'------------------------------------------------------------------------
If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary True '<--- 1 -----
Range("K1").Value = "default"
End If
----------------------
All of this works fine…

Now, when I have this “ReBuildProgramSummary†sub routine selected, in
bottom left of this screen is a “Properties – Module3â€; it has a select
button below this with “Module3 Module†the only option, and below this on
the Alphabetic Tab is:
(Name) Module3.

When I try to change the (Name) field from Module3 to
“ReBuildProgramSummaryâ€, the
[ Compile Error - Expected variable or procedure, not module ]
at the ‘<----1 call above.

Can I not change this (Name) field name to match?
 
R

Rowan

You could change the name to match but it is not really recommended as
it causes just this type of problem. Bear in mind that a module simply
holds procedures and functions. It could hold a number of each. The name
of the module therefore is not key until you rename it to the same name
as a function. This is different for a Class Module where the name is
the actual class (object) that you are creating.

In your situation if you really want to rename the module the same as
the procedure then you would have to make this clear to your calling
code eg:

If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary.ReBuildProgramSummary True
Range("K1").Value = "default"
End If

Hope this helps
Rowan
 
C

CRayF

So in my case, the external sub I created :
Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
Was placed in Module3. If over change I end up with others… Is there a way
to identify the Sub() by name or do you have to remember it was in Module3?
(I may be missing something elementary here but I am very new to this VBA.


Rowan said:
You could change the name to match but it is not really recommended as
it causes just this type of problem. Bear in mind that a module simply
holds procedures and functions. It could hold a number of each. The name
of the module therefore is not key until you rename it to the same name
as a function. This is different for a Class Module where the name is
the actual class (object) that you are creating.

In your situation if you really want to rename the module the same as
the procedure then you would have to make this clear to your calling
code eg:

If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary.ReBuildProgramSummary True
Range("K1").Value = "default"
End If

Hope this helps
Rowan
I have a module name question?

When I “Right click; View Code" on the worksheet, In the top left is
(Project – VBA Project) window; at the bottom I have a "Modules" folder and
in there I have 4 modules. 2 of which I seemed to have renamed and not “yetâ€
seeing a problem.

However, in the last module “Module3†It’s intended to be a sub module;
called by the worksheet module from 2 different points. *All curently runs
fine*.

The sub routing was created with the following name:
.. Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
... code...

From the Worksheet View Code, I call this sub routine with the following
command:
'------------------------------------------------------------------------
' [default/Changed!] Button - Re-Build Program Summary Template
'------------------------------------------------------------------------
If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary True '<--- 1 -----
Range("K1").Value = "default"
End If
----------------------
All of this works fine…

Now, when I have this “ReBuildProgramSummary†sub routine selected, in
bottom left of this screen is a “Properties – Module3â€; it has a select
button below this with “Module3 Module†the only option, and below this on
the Alphabetic Tab is:
(Name) Module3.

When I try to change the (Name) field from Module3 to
“ReBuildProgramSummaryâ€, the
[ Compile Error - Expected variable or procedure, not module ]
at the ‘<----1 call above.

Can I not change this (Name) field name to match?
Click to expand...
Click to expand...
 
R

Rowan

You would have to remember where you put each sub if you want to edit
them. If you do not have any ambiguous naming then you don't have to use
the module name when referencing a procudure in code so you can just use
Call ReBuildProgramSummary True rather than
Call Module3.ReBuildProgramSummary True

When you open a module window the Prodedure drop down, Top Right, has a
list of all procedures and functions that are sored in that module.
Selecting one from this dropdown will take the cursor to the first line
of that procedure.

Regards
Rowan
So in my case, the external sub I created :
Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
Was placed in Module3. If over change I end up with others… Is there a way
to identify the Sub() by name or do you have to remember it was in Module3?
(I may be missing something elementary here but I am very new to this VBA.


:

You could change the name to match but it is not really recommended as
it causes just this type of problem. Bear in mind that a module simply
holds procedures and functions. It could hold a number of each. The name
of the module therefore is not key until you rename it to the same name
as a function. This is different for a Class Module where the name is
the actual class (object) that you are creating.

In your situation if you really want to rename the module the same as
the procedure then you would have to make this clear to your calling
code eg:

If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary.ReBuildProgramSummary True
Range("K1").Value = "default"
End If

Hope this helps
Rowan
I have a module name question?

When I “Right click; View Code" on the worksheet, In the top left is
(Project – VBA Project) window; at the bottom I have a "Modules" folder and
in there I have 4 modules. 2 of which I seemed to have renamed and not “yetâ€
seeing a problem.

However, in the last module “Module3†It’s intended to be a sub module;
called by the worksheet module from 2 different points. *All curently runs
fine*.

The sub routing was created with the following name:
.. Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
... code...

From the Worksheet View Code, I call this sub routine with the following
command:
'------------------------------------------------------------------------
' [default/Changed!] Button - Re-Build Program Summary Template
'------------------------------------------------------------------------
If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary True '<--- 1 -----
Range("K1").Value = "default"
End If
----------------------
All of this works fine…

Now, when I have this “ReBuildProgramSummary†sub routine selected, in
bottom left of this screen is a “Properties – Module3â€; it has a select
button below this with “Module3 Module†the only option, and below this on
the Alphabetic Tab is:
(Name) Module3.

When I try to change the (Name) field from Module3 to
“ReBuildProgramSummaryâ€, the
[ Compile Error - Expected variable or procedure, not module ]
at the ‘<----1 call above.

Can I not change this (Name) field name to match?
Click to expand...
Click to expand...
Click to expand...
 
R

Rowan

This may help:
http://www.cpearson.com/excel/codemods.htm

So in my case, the external sub I created :
Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
Was placed in Module3. If over change I end up with others… Is there a way
to identify the Sub() by name or do you have to remember it was in Module3?
(I may be missing something elementary here but I am very new to this VBA.


:

You could change the name to match but it is not really recommended as
it causes just this type of problem. Bear in mind that a module simply
holds procedures and functions. It could hold a number of each. The name
of the module therefore is not key until you rename it to the same name
as a function. This is different for a Class Module where the name is
the actual class (object) that you are creating.

In your situation if you really want to rename the module the same as
the procedure then you would have to make this clear to your calling
code eg:

If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary.ReBuildProgramSummary True
Range("K1").Value = "default"
End If

Hope this helps
Rowan
I have a module name question?

When I “Right click; View Code" on the worksheet, In the top left is
(Project – VBA Project) window; at the bottom I have a "Modules" folder and
in there I have 4 modules. 2 of which I seemed to have renamed and not “yetâ€
seeing a problem.

However, in the last module “Module3†It’s intended to be a sub module;
called by the worksheet module from 2 different points. *All curently runs
fine*.

The sub routing was created with the following name:
.. Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
... code...

From the Worksheet View Code, I call this sub routine with the following
command:
'------------------------------------------------------------------------
' [default/Changed!] Button - Re-Build Program Summary Template
'------------------------------------------------------------------------
If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary True '<--- 1 -----
Range("K1").Value = "default"
End If
----------------------
All of this works fine…

Now, when I have this “ReBuildProgramSummary†sub routine selected, in
bottom left of this screen is a “Properties – Module3â€; it has a select
button below this with “Module3 Module†the only option, and below this on
the Alphabetic Tab is:
(Name) Module3.

When I try to change the (Name) field from Module3 to
“ReBuildProgramSummaryâ€, the
[ Compile Error - Expected variable or procedure, not module ]
at the ‘<----1 call above.

Can I not change this (Name) field name to match?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Global variables - where do you place them? 16
Call module 2
Retain Workbook Name throughout Module 2
how to call a sub that has arguments 3
Removing & Importing a macro module 3
Returning the name of a module 6
Can I use a variable contain the name of a sub or function to call it? 4
Excel default module when opening visual basic editor 0

Top