Variable losing its value

[dfein]

I have a workbook with code in "thisworkbook", there is a declaration Public ebv_codebook As String and in openworkbook event it gets set. However as soon as the openworkbook event is done the variable is reset to "" (ie blank). What is going on? Isn't the scope of a public variable on the top of a module the life of the module, ie until its gets closed?
Thanks
David Fein

[Mark O'Brien]

Are you trying to use this variable outwith the workbook object? The workbook module is a private module. This means that you cannot see a variable from outside of the module. Try declaring the variable in a standard module that contains a subroutine that is called by the Open event.

OR

Global variables are very messy, you would be better off declaring this as a constant or if it truly is a variable, then pass the variable between subroutines and functions that use the variable.

HTH (cuz I'm tired and may be speaking in slabbers)

[Ivan F Moala]

On 2002-05-22 17:16, dfein wrote:
I have a workbook with code in "thisworkbook", there is a declaration Public ebv_codebook As String and in openworkbook event it gets set. However as soon as the openworkbook event is done the variable is reset to "" (ie blank). What is going on? Isn't the scope of a public variable on the top of a module the life of the module, ie until its gets closed?
Thanks
David Fein

Hi David
A variable retains its value only over the course of the procedures
exercution. Declaring a variable as Public does NOT mean it holds
its value it just means that it is is visible to all procedures
in all modules in a directly referencing project, unless you have a
Option Private Module in the Generals section of your a Module.
The value of a variable may change over its lifetime, but it retains
some value (wether that be it's initialized value of 0,"" Empty or
what ever depending on the variable type declared
eg. A numeric variable is initialized to zero, a variable-length
string is initialized to a zero-length string (""), and a fixed-length
string is filled with the character represented by the ASCII character
code 0, or Chr(0). Variant variables are initialized to Empty.
Each element of a user-defined type variable is initialized as
if it were a separate variable.
So in your case the variable looses scope
after the workbook open is run.

One way around this as my friend Mark O has let us know is to
create a Class Module;

Eg.
1) Insert Class Module [Class1] = default name but you can change it
as long as your reference it by name.
2) Place this code in;

Option Explicit



Public vMyValue As Variant

'
'
'In a Std Module place this code;
'
'

Dim oClass As Class1



Public Sub main()



If oClass Is Nothing Then

    Set oClass = New Class1

    oClass.vMyValue = InputBox("Enter a value")

End If



MsgBox oClass.vMyValue



End Sub

'
'
'
Run the routine and see what happens.

[Mark O'Brien]

Ivan, I see you've added "Option Explicit"

http://www.mrexcel.com/board/viewtop...c=3544&forum=2

I thought those variable names sounded familiar.

[Ivan F Moala]

On 2002-05-22 18:19, Mark O'Brien wrote:
Ivan, I see you've added "Option Explicit"

http://www.mrexcel.com/board/viewtop...c=3544&forum=2

I thought those variable names sounded familiar.

Mark, I like this code so much I copied it
and made it my own !! by putting Option explicit

[Mark O'Brien]

Thank goodness I didn't show you the "proper" method using public properties and "Property Let", "Property Get".

On a third thought, my mate Andrew might be from Christchurch.

[Ivan F Moala]

On 2002-05-22 18:39, Mark O'Brien wrote:
Thank goodness I didn't show you the "proper" method using public properties and "Property Let", "Property Get".

On a third thought, my mate Andrew might be from Christchurch.

Your Too late I already have these properties
down pat

Yes I know an Andrew from C/C !!