Scope, Public Vars, Referenced workbooks

[Neal Zimm]

Hi -

Background: I've made the mistake of writing quite a bit
of code before truly understanding all the key aspects of
scope and add-ins.

After reading the scope, and related topics in Help I didn't find
mention of "ThisWorkbook" being somehow special (except for the
events, of course, which I know are private). I have open
and before close code which is working.

In the General Declarations of the ThisWorkbook object, I have:

Public ThisWbkString As String 'test var

There are two "regular" modules in the same workbook:
Reference, and TestMod1

In TestMod1 I have, but get the msg "variable not identified"
for:

Sub PublicTest()
ThisWbkString = "test a public var's value"
MsgBox ThisWbkString
End Sub

When I move the public declaration to the Reference module's
declarations section, the Sub works as anticpated.

1) So, why did the PublicTest sub fail the first time?

2) The above is a precursor to the real issue, which is
the app I'm working on is in two workbooks, let's call 'em
User.xls and Code.xls. The Code book will eventually become an add-in,
about which I'm learning, but am not there yet. (I have
Walkenbach's 2003 VBA book)

I have a TON of vars in the Code.xls "regular" module declarations section,
which I use to hold constant type 'read only' data which the subs and
functions use. It has 4 modules and I have exact copies of the same
declarations section in each.)
The open event of User opens the code.xls workbook.
Subs sitting in User call subs in code.xls and so far,
it's working.

If I reference code.xls in the User.xls, will I avoid typing a
lot of "Public"s in front of these declared vars, and avoid
the copies of the 'main' declarations section, or am I
better off hurrying up the Make-Code.xls-into-an-AddIn event?
I am leery of referencing a workbook when I know so little about it.

thanks.
Neal Z.

 

[Bob Phillips]

Hi -

Background: I've made the mistake of writing quite a bit
of code before truly understanding all the key aspects of
scope and add-ins.

After reading the scope, and related topics in Help I didn't find
mention of "ThisWorkbook" being somehow special (except for the
events, of course, which I know are private). I have open
and before close code which is working.

In the General Declarations of the ThisWorkbook object, I have:

Public ThisWbkString As String 'test var

There are two "regular" modules in the same workbook:
Reference, and TestMod1

In TestMod1 I have, but get the msg "variable not identified"
for:

Sub PublicTest()
ThisWbkString = "test a public var's value"
MsgBox ThisWbkString
End Sub

When I move the public declaration to the Reference module's
declarations section, the Sub works as anticpated.

1) So, why did the PublicTest sub fail the first time?

ThisWorkook is a class module, so any public variable is a property of that
class, and to access it you need to qualify with the class

MsgBox Thisworkbook.ThisWbkString

2) The above is a precursor to the real issue, which is
the app I'm working on is in two workbooks, let's call 'em
User.xls and Code.xls. The Code book will eventually become an add-in,
about which I'm learning, but am not there yet. (I have
Walkenbach's 2003 VBA book)

I have a TON of vars in the Code.xls "regular" module declarations section,
which I use to hold constant type 'read only' data which the subs and
functions use. It has 4 modules and I have exact copies of the same
declarations section in each.)
The open event of User opens the code.xls workbook.
Subs sitting in User call subs in code.xls and so far,
it's working.

If I reference code.xls in the User.xls, will I avoid typing a
lot of "Public"s in front of these declared vars, and avoid
the copies of the 'main' declarations section, or am I
better off hurrying up the Make-Code.xls-into-an-AddIn event?
I am leery of referencing a workbook when I know so little about it.

Don't replicate the variable names, they may have the same name, but you are
creating multiple copies. I tend to create a module that holds all of my
global, public, variables and constants.

 

[Susan]

Hi -

Background: I've made the mistake of writing quite a bit
of code before truly understanding all the key aspects of
scope and add-ins.

neal - i'm doing the same thing you are - writing while
learning. i don't necessarily think that's a "mistake",
some of us are kinethestic (sp?) learners who learn
best while DOING. i can read Walkenbach's books
till i'm blue in the face, but i don't really learn it until
i actually try it. i just try not to "bother" the experts
here until i've tried something six ways to sunday AND
looked it up in the book AND researched it on the
newsgroup without having any success myself.

the question YOU ask today may be the one I need
an answer to tomorrow! (& vice-versa)
good luck!
susan

 

[Neal Zimm]

Thanks Bob,
I should have figured it, when I exported the this.. code it exported as a
class and I thought that odd. No where have I seen class and thisworkbook
mentioned togetther yet.

 

[Bob Phillips]

All worksheet code modules are also classes Neal.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Neal Zimm]

A kindred spirit ! Good luck to you too. Walken.....'s book is pretty good,
there are lots of coding examples and the CD in the back of it is well done.
2003 Power Programming is the title if you're interested.