Calling rountines from various sheets and modules

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,008
I have a couple of formatting subroutines that I wish to call from several locations. Some are on the "Workbook" object, some are on the main Worksheet object, and others are in a module. But, when I call the routines they fail unless they are in the same object.

1) What are the basic rules for when you want to put one in Workbook, worksheet, or a module?
2) Where can I store these basic routines so that they can be accessed from anywhere and is there a trick ("Public"?)
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,008
I put public and they still couldn't be seen by routines in other objects. To make it work I have copies of the subroutines in every module.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
You mean everything is in the same file(workbook) and you can't reach it if you are not on the same sheet for example??
how do you try to call/access them? what are they (Sub, Function,...)?
Generally when you put it in a simple module it should be accessible from anywhere in the file.
Send some bits to help - a sample, a screenshot, ... anything to step on.
But in general - it's not a good idea to duplicate Sub-names.
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,008

ADVERTISEMENT

I'll try this again now that I'm back from vacation...

In "Module 1" I call a routine that was placed in the code section of a worksheet but the call from Module 1 could not find it. I then moved that code to Module 1 and it was fine. It WASN'T marked private. Why couldn't I find it? What's the trick?
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,008
Anybody? This is hammering me again. I can't post any sample code (it's on a different "sequestered" machine), but I have one subroutine that gets called from two different subroutines that are on different modules in the same workbook.

Sub 1 is on Sheet8
Sub 2 is on Sheet8
Sub 3 is in Module1

When I call Sub 2 from Sub 1, fine.

When I call Sub 2 from Sub 3...no work unless I move Sub 2 to Module1...but then Sub 1 won't work. So I have to copy it which is both stupid and a waste.

PS: the word "Private" doesn't appear anywhere in my workbook.

Shouldn't be this hard.

Ideas?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
When you call a sub which is not in a Module (for example in a Userform Code module or in a Worksheet Code Module) or if it is contained in a different object than the one which is calling it - you must specify where it is.
Call UserForm1.Sub1
or
Call Sheet1.Sub2
....

So to avoid complications it is a good idea to put all code not specific to a sheet of form in a module. Even if it is specific to a worksheet - only call it from the sheet code and still keep it in a module.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,116
Messages
5,526,921
Members
409,730
Latest member
mimipiz

This Week's Hot Topics

Top