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!