Lifetime of VBA variables

I use VBA with Excel; I do not use VB.

My Visual Basic User's Manual says that Module-level variables retain
their values while Visual Basic is running. Does that mean as long as
Excel is open? Or as long as an Excel workbook is open? Or something else?

Thanks,
Alan Beban

They retain their value for as long as the workbook is open... The same is
true of static variables...

"Alan Beban" wrote:

> I use VBA with Excel; I do not use VB.
>
> My Visual Basic User's Manual says that Module-level variables retain
> their values while Visual Basic is running. Does that mean as long as
> Excel is open? Or as long as an Excel workbook is open? Or something else?
>
> Thanks,
> Alan Beban
>

Jim Thomlinson wrote:
> They retain their value for as long as the workbook is open... The same is
> true of static variables...

Thank you.

Alan Beban
>
> "Alan Beban" wrote:
>
>
>>I use VBA with Excel; I do not use VB.
>>
>>My Visual Basic User's Manual says that Module-level variables retain
>>their values while Visual Basic is running. Does that mean as long as
>>Excel is open? Or as long as an Excel workbook is open? Or something else?
>>
>>Thanks,
>>Alan Beban
>>

What Jim Thomlinson wrote has a 'kinda...' to it.

Module level variables retain their values as long as the workbook is
open *and* there is no unhandled fault in the code.

Also known (documented?) is a bug wherein all global variables lose
their values when one programmatically adds (deletes? does something
else to?) a control in an userform. I suspect there are other cases
where module level variables are reset -- intentionally or otherwise.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <OWGafhxNFHA.688@TK2MSFTNGP10.phx.gbl>, unavailable@no.com
says...
> Jim Thomlinson wrote:
> > They retain their value for as long as the workbook is open... The same is
> > true of static variables...
>
> Thank you.
>
> Alan Beban
> >
> > "Alan Beban" wrote:
> >
> >
> >>I use VBA with Excel; I do not use VB.
> >>
> >>My Visual Basic User's Manual says that Module-level variables retain
> >>their values while Visual Basic is running. Does that mean as long as
> >>Excel is open? Or as long as an Excel workbook is open? Or something else?
> >>
> >>Thanks,
> >>Alan Beban
> >>
>

Just to add -

> I suspect there are other cases
> where module level variables are reset -- intentionally or otherwise.

Particularly in XL97, an unhandled error in a UDF, even in another workbook,
can cause all code to terminate and all public variables destroyed. When I
say terminate - just stop.

In "our" otherwise perfect code & wb, no way of knowing if user has UDF's in
other wb's liable to fail. Means disabling calculation if code is going to
trigger a calculation event. While "our" code is not running, no way of
knowing what may have occurred in the meantime to destroy any public var's.

I'm almost sure I've experienced similar in XL2K but cannot now recreate the
problem in later versions.

> Also known (documented?) is a bug wherein all global variables lose
> their values when one programmatically adds (deletes? does something
> else to?) a control in an userform.

Also adding manually or with code ActiveX controls to a worksheet, in some
but not all scenarios, can cause recompile (advised to me by Stephen Bullen
in another thread).

Regards,
Peter T

"Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1cb89b5d6045ba4198af68@msnews.microsoft.com...
> What Jim Thomlinson wrote has a 'kinda...' to it.
>
> Module level variables retain their values as long as the workbook is
> open *and* there is no unhandled fault in the code.
>
> Also known (documented?) is a bug wherein all global variables lose
> their values when one programmatically adds (deletes? does something
> else to?) a control in an userform. I suspect there are other cases
> where module level variables are reset -- intentionally or otherwise.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <OWGafhxNFHA.688@TK2MSFTNGP10.phx.gbl>, unavailable@no.com
> says...
> > Jim Thomlinson wrote:
> > > They retain their value for as long as the workbook is open... The
same is
> > > true of static variables...
> >
> > Thank you.
> >
> > Alan Beban
> > >
> > > "Alan Beban" wrote:
> > >
> > >
> > >>I use VBA with Excel; I do not use VB.
> > >>
> > >>My Visual Basic User's Manual says that Module-level variables retain
> > >>their values while Visual Basic is running. Does that mean as long as
> > >>Excel is open? Or as long as an Excel workbook is open? Or something
else?
> > >>
> > >>Thanks,
> > >>Alan Beban
> > >>
> >