Calling rountines from various sheets and modules

roscoe

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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Don't put Private
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
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,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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,026

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,026
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,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

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
Top