Calling rountines from various sheets and modules

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,041
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
Ah, there's the trick. I'll give that a shot. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top