Where to Place Public Subs and Functions

[Guest]

Excel 2003. I have a multisheet workbook. Each sheet has many controls that
call some VBA code. Many of the subroutines and functions performed are
common to every sheet. I am presently duplicating the code for each sheet.
Where may I place these subs and functions so that they may become public,
and thus eliminate the duplication of code? I tried placing them in the
"code page" associated with "ThisWorkbook" and declaring them public. But
when I try to call these "public" routines from the individual sheets, they
cannot be found. What am I doing wrong?

By the way, what do you call the "code page" associated with inidivual
sheets and the "code page" associated with "ThisWorkbook"?

 

[Don Guillett]

As always, post YOUR code for comments. Saves time and effort

 

[Darrin Henshaw]

You could place all your common routines and functions as modules. Just
right click the Project in the VBE, and go Insert > Module. Then you
should be able to call them.

 

[Guest]

I tried to state my question in generic terms that would not need code
examples. Again the question is this:

How may I make a subroutine or function public in an Excel workbook. I want
to be able to call the subroutine or function from any of the worksheets. I
have tried putting the subroutines and function in the code space for
"ThisWorkbook" and declaring them Public. This does not work. I have
however been able to call the subroutine or function using:

Call ThisWorkbook.<subroutinename>

However, I would like to be able to call these "public" routines without
having to prefix the call with "ThisWorkbook." Any help would be appreciated.

As always, post YOUR code for comments. Saves time and effort

 

[Don Guillett]

Then just put in a module instead of ThisWorkbook module or sheet module

--
Don Guillett
SalesAid Software
(e-mail address removed)

I tried to state my question in generic terms that would not need code
examples. Again the question is this:

How may I make a subroutine or function public in an Excel workbook. I want
to be able to call the subroutine or function from any of the worksheets. I
have tried putting the subroutines and function in the code space for
"ThisWorkbook" and declaring them Public. This does not work. I have
however been able to call the subroutine or function using:

Call ThisWorkbook.<subroutinename>

However, I would like to be able to call these "public" routines without
having to prefix the call with "ThisWorkbook." Any help would be appreciated.

 

[Bob Phillips]

Place a function or sub in a standard code module, and then in the controls
click event, call that (those) common functions.

A worksheet has a sheet class module, a workbook has a workbook class
module.

 

[Guest]

Thank you for the solution. God bless.