Public variables and scope

[Dennis Benjamin]

Hey All

I'm learning VBA in Excel and am confused about variable scope. I have
declared a variable at the top of the thisWorkBook module as follows:

Option Explicit
Public mAb

when I add mAb to the Watchlist, and select Context = thisWorkbook, I can
see the contents. When I add another copy to the Watchlist, selecting
Context=(All Modules) it shows up as "Expression not defined". I am guessing
that this is the reason I am unable to store values in mAb from inside Subs
in my UserForms - they don't know about the variable. Is there some setting
that would cause this behavior? How can I share a variable between my
thisWorkBook and a UserForm?

Thanks!

 

[Rowan Drummond]

Public variables should be declared in a standard module and not a Class
Module. The modules behing forms, sheets and the ThisWorkBook module are
all Class Modules.
Move the declaration to a standard module.

Hope this helps
Rowan

 

[Dennis Benjamin]

Rowan

Thanks very much for you response ... I'm sure that will solve the problem.
If you're willing to answer one more question, could you explain to me how
to decide whether to place code in an individual sheet, thisWorkBook, or a
Module? I've ordered some books hoping that I will get an overview of the
language, but most resources I've found just jump straight into code w/o
discussing the architecture of an Excel workbook.


Thanks again!

 

[Rowan Drummond]

Hi Dennis

Generally you would use the Class modules (thisworkbook, and sheet
modules) for events that are associated with that object. The object
being the relevant sheet etc.
A standard module is used for macros which are not associated with a
specific object eg a macro which you can use to format any sheet.

For more info on macros see David McRitchie's intro at
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson has some detail on Events at:
http://www.cpearson.com/excel/events.htm

Hope this helps
Rowan