ThisWorkbook variables...

  • Thread starter Thread starter Jim Carlock
  • Start date Start date
J

Jim Carlock

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,

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.
 
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.
 
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.
 
Heh, Thanks Chip. "scope" isn't in the index and is NOT found when
searched for.
 
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.
 
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,

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 (e-mail address removed)
 
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.
 
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)
 
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!
 
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.
 
Back
Top