Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine or
another won't work right from each module I try (ThisSheet, ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just made
the mistake of taking a couple of VBA classes... and telling the boss about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

This site might help. I don't know exactly what you are looking for, but
this is pretty fundamental stuff.

"MarquisB" wrote:

Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine or
another won't work right from each module I try (ThisSheet, ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just made
the mistake of taking a couple of VBA classes... and telling the boss about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

Hi JLGWhiz, and thanks for the quick reply.

what I'm looking for. Basically I've written my app, but now I have
routines spread across three modules. I'd like to condense them into a single
module, but the Workbook Open routine will only work when it's in the
ThisWorkbook module, Click events only work when they're in ModuleN, etc. I'm
trying to get a better background about modules in general so I know if I can
condense them at all. If so, what goes where, etc. Is that any clearer?
Thanks again...
--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell


"JLGWhiz" wrote:

This site might help. I don't know exactly what you are looking for, but
this is pretty fundamental stuff.

"MarquisB" wrote:

Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine or
another won't work right from each module I try (ThisSheet, ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just made
the mistake of taking a couple of VBA classes... and telling the boss about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

Marquis,

If you're willing to slightly modify your code, you could take all the event
handling out of the SheetN modules and put it in to the ThisWorkbook module.
A workbook has events that correspond to sheet events, and receive as a
parameter the sheet raising the event. For example,

Private Sub Worksheet_Change(ByVal Target As Range)

in each sheet can handled by

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

in ThisWorkbook.

Here, Sh is the sheet being changed. If you put the Worksheet_Change code
in Workbook_SheetChange, you must (!) ensure that Range references are
pointing to Sh. If you don't prefix Range references with Sh, they'll point
to the ActiveSheet not Sh.

I'd like to condense them into a single module

There is really no compelling reason to consolidate several code modules in
to a single module. You won't get any performance improvement. In fact, it
is often good practice to use multiple modules in a project. You can have a
separate module for a family of related procedures. Grouping related
procedures into their own module promotes good organization and code re-use.
If each module is self-contained, you will find that you can re-use modules
across multiple projects. For example, I have dozens of VBA modules each of
which contains a group of related procedures, such as utility functions for
arrays. If I'm working on a project that does work with arrays, I just
Import the Array module into the project and have access to all my array
handling procedures. No need to rewrite code for every project.

If you have multiple modules each contain essentially unrelated procedures,
there's wrong with consolidating them into a single module.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"MarquisB" wrote in message
...
Hi JLGWhiz, and thanks for the quick reply.

what I'm looking for. Basically I've written my app, but now I have
routines spread across three modules. I'd like to condense them into a
single
module, but the Workbook Open routine will only work when it's in the
ThisWorkbook module, Click events only work when they're in ModuleN, etc.
I'm
trying to get a better background about modules in general so I know if I
can
condense them at all. If so, what goes where, etc. Is that any clearer?
Thanks again...
--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell


"JLGWhiz" wrote:

This site might help. I don't know exactly what you are looking for, but
this is pretty fundamental stuff.

"MarquisB" wrote:

Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine
or
another won't work right from each module I try (ThisSheet,
ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution
a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just
made
the mistake of taking a couple of VBA classes... and telling the boss
about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

Hi Mr. Pearson:

My app will require a bit of tweaking yet, so modifying code & juggling
routines is no problem. Can the "ThisWorkbook" module also handle "OnClick"
events assigned to drawing objects though? I tried putting the OnClick events
in ThisWorkbook, but it seems they can't access my public variables from that
module.
--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell


"Chip Pearson" wrote:

Marquis,

If you're willing to slightly modify your code, you could take all the event
handling out of the SheetN modules and put it in to the ThisWorkbook module.
A workbook has events that correspond to sheet events, and receive as a
parameter the sheet raising the event. For example,

Private Sub Worksheet_Change(ByVal Target As Range)

in each sheet can handled by

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

in ThisWorkbook.

Here, Sh is the sheet being changed. If you put the Worksheet_Change code
in Workbook_SheetChange, you must (!) ensure that Range references are
pointing to Sh. If you don't prefix Range references with Sh, they'll point
to the ActiveSheet not Sh.

I'd like to condense them into a single module

There is really no compelling reason to consolidate several code modules in
to a single module. You won't get any performance improvement. In fact, it
is often good practice to use multiple modules in a project. You can have a
separate module for a family of related procedures. Grouping related
procedures into their own module promotes good organization and code re-use.
If each module is self-contained, you will find that you can re-use modules
across multiple projects. For example, I have dozens of VBA modules each of
which contains a group of related procedures, such as utility functions for
arrays. If I'm working on a project that does work with arrays, I just
Import the Array module into the project and have access to all my array
handling procedures. No need to rewrite code for every project.

If you have multiple modules each contain essentially unrelated procedures,
there's wrong with consolidating them into a single module.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"MarquisB" wrote in message
...
Hi JLGWhiz, and thanks for the quick reply.

what I'm looking for. Basically I've written my app, but now I have
routines spread across three modules. I'd like to condense them into a
single
module, but the Workbook Open routine will only work when it's in the
ThisWorkbook module, Click events only work when they're in ModuleN, etc.
I'm
trying to get a better background about modules in general so I know if I
can
condense them at all. If so, what goes where, etc. Is that any clearer?
Thanks again...
--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell


"JLGWhiz" wrote:

This site might help. I don't know exactly what you are looking for, but
this is pretty fundamental stuff.

"MarquisB" wrote:

Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine
or
another won't work right from each module I try (ThisSheet,
ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution
a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just
made
the mistake of taking a couple of VBA classes... and telling the boss
about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

There is no "OnClick" event for shapes. If you're talking about the OnAction
property, or the assigned macro, you can put the code in ThisWorkbook, and
assign "ThisWorkbook.MacroName" to the OnAction property or assigned macro.
MacroName MUST be declared Public. E.g.,


Public Sub MacroName()
Debug.Print "OK
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"MarquisB" wrote in message
...
Hi Mr. Pearson:

My app will require a bit of tweaking yet, so modifying code & juggling
routines is no problem. Can the "ThisWorkbook" module also handle
"OnClick"
events assigned to drawing objects though? I tried putting the OnClick
events
in ThisWorkbook, but it seems they can't access my public variables from
that
module.
--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell


"Chip Pearson" wrote:

Marquis,

If you're willing to slightly modify your code, you could take all the
event
handling out of the SheetN modules and put it in to the ThisWorkbook
module.
A workbook has events that correspond to sheet events, and receive as a
parameter the sheet raising the event. For example,

Private Sub Worksheet_Change(ByVal Target As Range)

in each sheet can handled by

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

in ThisWorkbook.

Here, Sh is the sheet being changed. If you put the Worksheet_Change
code
in Workbook_SheetChange, you must (!) ensure that Range references are
pointing to Sh. If you don't prefix Range references with Sh, they'll
point
to the ActiveSheet not Sh.

I'd like to condense them into a single module

There is really no compelling reason to consolidate several code modules
in
to a single module. You won't get any performance improvement. In fact,
it
is often good practice to use multiple modules in a project. You can have
a
separate module for a family of related procedures. Grouping related
procedures into their own module promotes good organization and code
re-use.
If each module is self-contained, you will find that you can re-use
modules
across multiple projects. For example, I have dozens of VBA modules each
of
which contains a group of related procedures, such as utility functions
for
arrays. If I'm working on a project that does work with arrays, I just
Import the Array module into the project and have access to all my array
handling procedures. No need to rewrite code for every project.

If you have multiple modules each contain essentially unrelated
procedures,
there's wrong with consolidating them into a single module.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"MarquisB" wrote in message
...
Hi JLGWhiz, and thanks for the quick reply.

what I'm looking for. Basically I've written my app, but now I have
routines spread across three modules. I'd like to condense them into a
single
module, but the Workbook Open routine will only work when it's in the
ThisWorkbook module, Click events only work when they're in ModuleN,
etc.
I'm
trying to get a better background about modules in general so I know if
I
can
condense them at all. If so, what goes where, etc. Is that any clearer?
Thanks again...
--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell


"JLGWhiz" wrote:

This site might help. I don't know exactly what you are looking for,
but
this is pretty fundamental stuff.

"MarquisB" wrote:

Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in
several
different modules, for sheet events, workbook events, etc. I'd like
to
collect them into a single module to ease distribution, but one
routine
or
another won't work right from each module I try (ThisSheet,
ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease
distribution
a bit.
My app is for Excel 2003 under Windows XP, but will need to be
backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I
just
made
the mistake of taking a couple of VBA classes... and telling the
boss
about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

Sorry bout that. Forgot to paste the site.

http://www.excel-vba.com/excel-vba-contents.htm

"MarquisB" wrote:

Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine or
another won't work right from each module I try (ThisSheet, ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just made
the mistake of taking a couple of VBA classes... and telling the boss about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell

Go to http://www.mvps.org/dmcritchie/excel/excel.htm
He has the info you want and links to other sites with more.
HTH,
John






MarquisB wrote:
Hey y'all!

Anybody know where I can find an article that explains modules more
thoroughly than Help? I'm writing an app that has routines in several
different modules, for sheet events, workbook events, etc. I'd like to
collect them into a single module to ease distribution, but one routine or
another won't work right from each module I try (ThisSheet, ThisWorkbook,
Modules, etc.) I'd rather not use Personal, again to ease distribution a bit.
My app is for Excel 2003 under Windows XP, but will need to be backward
compatible with Excel 2000 under 98SE.

In case you couldn't guess, I'm not a professional programmer. I just made
the mistake of taking a couple of VBA classes... and telling the boss about
them ;-) Thanks for the help!

--
Marquis B.
Hampstead, NC USA

"Perpetual optimism is a force multiplier."
- Gen. Colin Powell