Public variables and scope

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!

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 wrote:
> 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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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 wrote:
>> 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!

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

Dennis Benjamin wrote:
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>
>>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 wrote:
>>
>>>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!
>
>
>