Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope, Public Vars, Referenced workbooks
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope, Public Vars, Referenced workbooks
"Neal Zimm" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope, Public Vars, Referenced workbooks
Neal Zimm wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope, Public Vars, Referenced workbooks
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. -- Neal Z "Bob Phillips" wrote: "Neal Zimm" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope, Public Vars, Referenced workbooks
All worksheet code modules are also classes Neal.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Neal Zimm" wrote in message ... 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. -- Neal Z "Bob Phillips" wrote: "Neal Zimm" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope, Public Vars, Referenced workbooks
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. -- Neal Z "Susan" wrote: Neal Zimm wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
scope of public variables | Excel Programming | |||
Different Behaviour of Public Object Vars in Worksheets vs. Add-In | Excel Programming | |||
Public variables and scope | Excel Programming | |||
Scope of a public variable | Excel Programming | |||
Scope of Public Variable | Excel Programming |