Calling rountines from various sheets and modules

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,007
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,007
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,007

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,007
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,456
Messages
5,511,492
Members
408,853
Latest member
JoshuaHudsonpTi45

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top