What, Why three areas to save code

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

Robert Christie

Hi

My questions relate to the "Why", "Where" and "What" type
of macro code should be stored in (I believe 3 places)
that Excel has.
1. in the worksheet.
2. in a module/s.
3. in the Personal.xls.

I understand the reasons for the No.3.
Personal.xls is opened but hidden at Excel Start-up. This
allows the user to add a command button to a toolbar to
carry out a specific task on any workbook opened and call
up any procedure within Personal.xls.

Could someone explain the "Why", "Where" and "What" of
the other two places;
What was each originally designed to do/achieve?
How should each be used efficiently?

TIA

Bob C.
 
The last version to store code in a sheet was xl95 and those were macro
sheets--not worksheets.

And code is stored in module (a general module, a class module (like a userform
or behind a worksheet or behind ThisWorkbook)). But all of it is in a module.

But those modules don't exist on their own. They exist in the projects of
workbooks. And personal.xls is a workbook that (usually) has code in the
modules that are contained in its project.

So you're going to put all your code in modules (in some workbook).

Lots of people have their own custom functions/routines that they want available
each time they start excel. A common spot for these functions/routines is in
the personal.xls workbook.

But there can be other functions/routines that are only useful in a particular
workbook. These routines could be stored in the same workbook that contains the
data.

And there are other functions/routines that are useful for a type of workbook.
Say every month you create a report for your boss. You create the workbook from
scratch each time. But there are routines that are particularly useful when
you're doing these reports.

But they're not very useful for the other 99% that you spend in excel. These
could be stored in another workbook (just like personal.xls, but named something
else) that you open only when you need it.

In fact, sometimes people save these types of workbooks as addins. They'll
build toolbars that are used to invoke the macros in the addin. The workbook is
even more hidden than personal.xls is. Most people don't need to get to the
worksheets (manually)--even if the code uses the data on its own worksheets for
some purpose.
 
Hi Dave

And thank you for your reply.

In an eight sheet workbook of mine called "Transactions"
the last sheet has these Private Sub's included.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As
Range, Cancel As Boolean)

Private Sub Worksheet_BeforeRightClick(ByVal Target As
Range, Cancel As Boolean)

By right clicking the Sheet tab and selecting "View
Code", brings up a window called "Transactions.xls
[Sheet8 (Code)]" these two Sub's are the only macro's
shown in that window.
They are also shown if I select the workbooks Module1
along with 15 other macro's used throughout the other
seven worksheets.
So if a piece of code/macro is to be used only on a
particular worksheet, should that piece of code be added
to the workbook through the worksheet?
I ask this question due to being a novice at programming
writing, I usually record a macro first, then with help
from this group modify the code to shorten, speed and
tidy up the code.
I record my actions via the forms command button, this
places the code in a module. But clicking the sheets tab -
-> view code shows a blank window.
Hence my original post, if you have answered my question
already and I have not understood your answer, I do
apologise.

Regards
Bob C.
-----Original Message-----
The last version to store code in a sheet was xl95 and those were macro
sheets--not worksheets.

And code is stored in module (a general module, a class module (like a userform
or behind a worksheet or behind ThisWorkbook)). But all of it is in a module.

But those modules don't exist on their own. They exist in the projects of
workbooks. And personal.xls is a workbook that (usually) has code in the
modules that are contained in its project.

So you're going to put all your code in modules (in some workbook).

Lots of people have their own custom functions/routines that they want available
each time they start excel. A common spot for these functions/routines is in
the personal.xls workbook.

But there can be other functions/routines that are only useful in a particular
workbook. These routines could be stored in the same workbook that contains the
data.

And there are other functions/routines that are useful for a type of workbook.
Say every month you create a report for your boss. You create the workbook from
scratch each time. But there are routines that are particularly useful when
you're doing these reports.

But they're not very useful for the other 99% that you spend in excel. These
could be stored in another workbook (just like
Click to expand...
personal.xls, but named something
 
Interspersed.

Robert said:
Hi Dave

And thank you for your reply.

In an eight sheet workbook of mine called "Transactions"
the last sheet has these Private Sub's included.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As
Range, Cancel As Boolean)

Private Sub Worksheet_BeforeRightClick(ByVal Target As
Range, Cancel As Boolean)

By right clicking the Sheet tab and selecting "View
Code", brings up a window called "Transactions.xls
[Sheet8 (Code)]" these two Sub's are the only macro's
shown in that window.
Click to expand...

And that window that you see is a module--a worksheet module (if you want).
They are also shown if I select the workbooks Module1
along with 15 other macro's used throughout the other
seven worksheets.
Click to expand...

Are the worksheet_beforerightclick & _beforedoubleclick also in Module1 (as an
exact copy)?

If yes, this sounds like someone pasted them (or typed) them in the wrong spot,
then instead of moving them, they copied and never cleaned up those
_beforexxxclick routines in the general module (module1).

So if a piece of code/macro is to be used only on a
particular worksheet, should that piece of code be added
to the workbook through the worksheet?
Click to expand...

If there's a general routine that a worksheet event uses, I'll put it in a
general module. (It might be useful from another worksheet--or just useful for
a macro in a general module.

But if it's a worksheet event, (_beforerightclick, _change, _selectionchange),
these have to be in the worksheet module (well, if they're common between
worksheets, you could also put the "expanded" version behind the ThisWorkbook
module.

In one worksheet module, you could have this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
....
End sub

But if it's really the same code over and over and over, you could use a
workbook event. Excel will pass you the worksheet that's changing (not
necessary in the individual worksheet_selectionchange event--since you're only
on that one worksheet). And it'll pass you the target (the range selected).
The target gets passed in both procedures.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
....
end sub

=========
I'd keep all my events where they belong--either behind the worksheet or behind
the workbook. But the General routines in general modules.

Chip Pearson has a white paper at:
http://www.cpearson.com/excel/events.htm
You may want to take a look at it.

He also has a sample workbook that will show how the events fire (and in what
order) at:
http://www.cpearson.com/excel/download.htm
Look for EventSeq.
 
Back
Top