different places to put excel code?

  • Thread starter Thread starter Ross
  • Start date Start date
R

Ross

hi there,

could someone tell me the difference between putting a sub in a
worksheet specific code module and a general module?

When I hope my spreadsheet, then do alt-f11, I see Sheet1 with a place
for code, as well as a Module1 that takes code. Sometimes I find that
if I am trying to call a procedure from within Sheet1 code, it won't
find it unless I put the called procedure in the Module1.

I know this must be very basic stuff for Excel. A brief explanation
would be appreciated!

Thanks
Ross
 
I'll try.
sheet specific macros such as worksheet_change must be in the sheet module.
UDF (User defined functions) must be in a regular MODULE
workbook_open and others must be in the ThisWorkbook module
Regular old macros can often be anywhere as long as properly referenced.
HTH
 
Ross said:
hi there,

could someone tell me the difference between putting a sub in a
worksheet specific code module and a general module?

When I hope my spreadsheet, then do alt-f11, I see Sheet1 with a place
for code, as well as a Module1 that takes code. Sometimes I find that
if I am trying to call a procedure from within Sheet1 code, it won't
find it unless I put the called procedure in the Module1.

I know this must be very basic stuff for Excel. A brief explanation
would be appreciated!

Thanks
Ross
Click to expand...

There are 5 type of location you can put your VBA Code

1) A worksheet (sheet1, sheet2 etc)

This location is where code should be placed that traps one of the
worksheet events (Activate, BeforeDoubleClick, BeforeRightClick etc)
or that traps events of an object owned by the worksheet such as
a ComboBox or RadioButton

2) The ThisWorkbook object

This location is where code should be placed that traps one of the
workbook events (SheetActivate, SheetBeforeDoubleClick,
SheetBeforeRightClick etc)

3) UserForms - Userforms are what you use to define your user interface
and any code specific to that interface such as all the TextBox , ComboBox,
ListBox events shoud go here. You may also add code routines
that are called by that form only

4) Modules - This is the place to put most of your general purpose
program procedures. Basically the rule should be that if you want
call a routine from other places in the Project the procedure
should go into a module.

As your programs get more complex you'll find it valuable to
have some standard modules that you can load into each new
project

5) Class Modules

This is where you put code when you want to declare and
use new object types. Like Excel Objects your types
can have properties and methods , which are defined
in the classmodule and then instantiated and used from
a userform or code module

I suspect this is a little beyond your scope at present

Keith
 
Classes
Class module

Event Related Code (except classes)
Workbook level events : ThisWorkbook module
Sheet level events: Sheet module associated with the sheet
ActiveX Controls on Sheets events: Sheet module associated with the sheet
they are on
Userform/controls on userforms: Userform module
Forms Toolbar controls: General Module
Commandbar OnAction Macros: General Module

Everything else should go in a general module
including user defined functions to be used in a worksheet.

http://groups.google.com/groups?selm=eMKM#8raBHA.2080@tkmsftngp03&oe=UTF-8&output=gplain


http://groups.google.com/groups?selm=u9e99ApaBHA.1432@tkmsftngp04&oe=UTF-8&output=gplain

http://groups.google.com/groups?selm=ea0fEgt4AHA.964@tkmsftngp03&oe=UTF-8&output=gplain
 
Back
Top