Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Jim,

The variable strPath will retain its value as long as the workbook is open,
or an End statement is executed, or VBA resets the VBProject due to your
editing code.

explains the lifetime of objects/variables?

See "scope" in the VBA Help.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim Carlock" wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

assume you mean
Private Sub Workbook_Open()
strPath = ThisWorkbook.Path
End Sub

Rather than ThisWorkbook_Open


-------


the lifetime is the lifetime of the open workbook.

It is explained in Excel VBA help.

In help, look for variables, then lifetime:

A module-level variable differs from a static variable. In a standard module
or a class module, it retains its value until you stop running your code. In
a class module, it retains its value as long as an instance of the class
exists. Module-level variables consume memory resources until you reset
their values, so use them only when necessary.

the thisworkbook exists for as long as the workbook is open.

Perhaps this is just an example, but why not use Thisworkbook.Path directly.
--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Yes, that's correct.

I've tried the following in the Workbook object as well...

Public gStrPath As String

Private Sub Workbook_Open()
gStrPath = ThisWorkbook.Path
End Sub

But inside of Sheet1, Sheet2, Sheet3 the gStrPath isn't visible.

So Public really isn't public or Sheet1 isn't an object created
by Workbook.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
assume you mean
Private Sub Workbook_Open()
strPath = ThisWorkbook.Path
End Sub

Rather than ThisWorkbook_Open


-------


the lifetime is the lifetime of the open workbook.

It is explained in Excel VBA help.

In help, look for variables, then lifetime:

A module-level variable differs from a static variable. In a standard
module
or a class module, it retains its value until you stop running your code.
In
a class module, it retains its value as long as an instance of the class
exists. Module-level variables consume memory resources until you reset
their values, so use them only when necessary.

the thisworkbook exists for as long as the workbook is open.

Perhaps this is just an example, but why not use Thisworkbook.Path
directly.
--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Heh, Thanks Chip. "scope" isn't in the index and is NOT found when
searched for.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Chip Pearson" wrote in message
...
Jim,

The variable strPath will retain its value as long as the workbook is
open,
or an End statement is executed, or VBA resets the VBProject due to your
editing code.

explains the lifetime of objects/variables?

See "scope" in the VBA Help.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim Carlock" wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Something is seriously wrong with my help. Oof, I need to find where
the VBA help file is. Excel seems to be a separate giberish item.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Chip Pearson" wrote in message
...
Jim,

The variable strPath will retain its value as long as the workbook is
open,
or an End statement is executed, or VBA resets the VBProject due to your
editing code.

explains the lifetime of objects/variables?

See "scope" in the VBA Help.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim Carlock" wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Hi Jim,

You have to keep in mind that the code modules behind document objects
like the workbook and worksheets are actually class modules, not standard
modules. A public declaration in a class module is the same as creating a
custom property of that class. Therefore, in your example below, you could
access the gStrPath value from other modules in the following manner:

Debug.Print ThisWorkbook.gStrPath

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jim Carlock" wrote in message
...
Yes, that's correct.

I've tried the following in the Workbook object as well...

Public gStrPath As String

Private Sub Workbook_Open()
gStrPath = ThisWorkbook.Path
End Sub

But inside of Sheet1, Sheet2, Sheet3 the gStrPath isn't visible.

So Public really isn't public or Sheet1 isn't an object created
by Workbook.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
assume you mean
Private Sub Workbook_Open()
strPath = ThisWorkbook.Path
End Sub

Rather than ThisWorkbook_Open


-------


the lifetime is the lifetime of the open workbook.

It is explained in Excel VBA help.

In help, look for variables, then lifetime:

A module-level variable differs from a static variable. In a standard
module
or a class module, it retains its value until you stop running your
code.
In
a class module, it retains its value as long as an instance of the class
exists. Module-level variables consume memory resources until you reset
their values, so use them only when necessary.

the thisworkbook exists for as long as the workbook is open.

Perhaps this is just an example, but why not use Thisworkbook.Path
directly.
--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Jim,

What version of Excel are you using? In 2002, searching for 'scope' brings
up the topic 'Understanding Scope And Visibility', which it the topic you
want.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com




"Jim Carlock" wrote in message
...
Heh, Thanks Chip. "scope" isn't in the index and is NOT found when
searched for.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Chip Pearson" wrote in message
...
Jim,

The variable strPath will retain its value as long as the workbook is
open,
or an End statement is executed, or VBA resets the VBProject due to your
editing code.

explains the lifetime of objects/variables?

See "scope" in the VBA Help.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim Carlock" wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

How would I process a module before I process what's in
ThisWorkbook ?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
assume you mean
Private Sub Workbook_Open()
strPath = ThisWorkbook.Path
End Sub

Rather than ThisWorkbook_Open


-------


the lifetime is the lifetime of the open workbook.

It is explained in Excel VBA help.

In help, look for variables, then lifetime:

A module-level variable differs from a static variable. In a standard
module
or a class module, it retains its value until you stop running your code.
In
a class module, it retains its value as long as an instance of the class
exists. Module-level variables consume memory resources until you reset
their values, so use them only when necessary.

the thisworkbook exists for as long as the workbook is open.

Perhaps this is just an example, but why not use Thisworkbook.Path
directly.
--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

I searched Excel and missed it. Found it in VBA.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Chip Pearson" wrote in message
...
Jim,

What version of Excel are you using? In 2002, searching for 'scope'
brings
up the topic 'Understanding Scope And Visibility', which it the topic you
want.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com




"Jim Carlock" wrote in message
...
Heh, Thanks Chip. "scope" isn't in the index and is NOT found when
searched for.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Chip Pearson" wrote in message
...
Jim,

The variable strPath will retain its value as long as the workbook is
open,
or an End statement is executed, or VBA resets the VBProject due to
your
editing code.

explains the lifetime of objects/variables?

See "scope" in the VBA Help.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Jim Carlock" wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

You put the variable in a module,
you set it in thisworkbook using the workbook_Open event.

Other than that, I am not sure what you are asking.

--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
How would I process a module before I process what's in
ThisWorkbook ?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
assume you mean
Private Sub Workbook_Open()
strPath = ThisWorkbook.Path
End Sub

Rather than ThisWorkbook_Open


-------


the lifetime is the lifetime of the open workbook.

It is explained in Excel VBA help.

In help, look for variables, then lifetime:

A module-level variable differs from a static variable. In a standard
module
or a class module, it retains its value until you stop running your
code.
In
a class module, it retains its value as long as an instance of the class
exists. Module-level variables consume memory resources until you reset
their values, so use them only when necessary.

the thisworkbook exists for as long as the workbook is open.

Perhaps this is just an example, but why not use Thisworkbook.Path
directly.
--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Ah, one of the sweet mysteries of MS Excel. I don't think that we are
processing the module, just reading global declarations. I'm not sure that
it makes a diff if it works. (and it does)

"Jim Carlock" wrote in message
...
How would I process a module before I process what's in
ThisWorkbook ?

It looks like ALL public code/variables in ANY module
becomes available automatically.

In VB6, I've had to define a Main() sub and run from that
to get code in the module to be visible. It's still not making much
sense to me. I was thinking that the code must be compiled to
an intermediate file and linked in. I'm thinking it must do some
of the interpretation when the Workbook is loaded. I'm just
having problems seeing how the scope is being resolved.

-- Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
You put the variable in a module,
you set it in thisworkbook using the workbook_Open event.

Other than that, I am not sure what you are asking.

--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
How would I process a module before I process what's in
ThisWorkbook ?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Tom Ogilvy" wrote in message
...
assume you mean
Private Sub Workbook_Open()
strPath = ThisWorkbook.Path
End Sub

Rather than ThisWorkbook_Open


-------


the lifetime is the lifetime of the open workbook.

It is explained in Excel VBA help.

In help, look for variables, then lifetime:

A module-level variable differs from a static variable. In a standard
module
or a class module, it retains its value until you stop running your
code.
In
a class module, it retains its value as long as an instance of the
class
exists. Module-level variables consume memory resources until you
reset
their values, so use them only when necessary.

the thisworkbook exists for as long as the workbook is open.

Perhaps this is just an example, but why not use Thisworkbook.Path
directly.
--
Regards,
Tom Ogilvy

Jim Carlock wrote in message
...
Option Explicit

Private strPath As String

Private Sub ThisWorkbook_Open()
strPath = ThisWorkbook.Path
End Sub

What's the lifetime of strPath? There a reference anywhere that
explains the lifetime of objects/variables?

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

Yeah, keeps me awake nights sometimes. But I didn't program Excel, I just
program in it.

Just figure it this way: Workbook opens. Looks for Global conditions, ie.
number and names of worksheets, Charts in worksheets, Macros in
Worksheets....Cl\ick, Whirr, Grind, Build........
Look for commands in ThisWorkBook. Does Commands. Sends secret message to
Microsoft. Opens ActiveSheet. Starts Commands in Open Workbook. Somewhere
in there the global declarations have been read, long before the
Workbook_Open stuff happens.

"Jim Carlock" wrote in message
...
LOL I think you are very right. I'm just one of those folks that will
go to extreme lengths to figure out the why because for some
reason things just don't sit well with me until I know the why. I'll go
nuts trying to figure out the why and I'll lose sleep because of it and
I'll end up getting nothing done until I have a firm chokehold on WHY!

LOL It's one of my biggest downfalls sometimes.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Bradley Dawson" wrote in message
...
Ah, one of the sweet mysteries of MS Excel. I don't think that we are
processing the module, just reading global declarations. I'm not sure
that
it makes a diff if it works. (and it does)

"Jim Carlock" wrote in message
...
How would I process a module before I process what's in
ThisWorkbook ?