scope/ lifetime of variable

[jeffP]

HI all,
I'm so confused about this.
I've declared a variable and assigned a value in one module and want to use
it in another (or a few) but I seem to lose the value once I leave the
standard module that I started from. I've include a code example of what I'm
trying to do.
Any help is always appreciated.

--
jeff
(e-mail address removed)
... I used to have a handle on life, but it broke.

'in module1
Public Sub MYPaths()
Dim vbackup As String
vbackup = Sheets("mypaths").Range("d2").Value
Debug.Print vbackup
' this works fine and I view the value perfectly in immediate window
end sub

'in module2
Public Sub Test()
debug.print vbackup
'returns nothing in the immediate window
end sub

 

[jeffP]

Thanks for the help and I'm still confused. I run the MyPaths macro on
Workbook open and I can use other subs "for awhile"
but somewhere , somehow the variable VBACKUP loses it value (blank) and I
have to run the MyPaths macro again to use the variable. I read the help
file on "lifetime of a variable" and I "think" it should keep it's value
until the workbook is closed or it's given a different value.
Am I wrong about that.
Any help , understanding and education would be appreciated.

jeffP
(e-mail address removed)
... I used to have a handle on life, but it broke.

 

[Norman Jones]

Hi Jeff,

If a procedure which includes an End statement is run, all module-level
variables will be reset. Similarly, if you press the macro reset button or
if you select End in response to an error message, all module-level
variables will be reset.

 

[jeffP]

Norman,
End statement as in Sub End would mean basically any macro I run, correct? I
don't know if / how a variable is set "higher" than module-level so if you
could help me there.
Let me tell you what I'm trying to do. I have a workbook that is used on
four different computers (home,laptop,work, work) that I wrote code that
uses 5 or 6 other files for data and backups. These are all located in
different drives/ directories on each machine. So if I make a change in the
workbook at home and want to update the others I have to go into the code to
change all the paths on each copy on each computer. This is time consuming,
a pia, and very prone to error. It also means that I must set up each
workbook on each computer for the other users. Tom Oglivy suggested keeping
a database on a separate sheet and assigning variables to the paths. I made
the datasheet so D2 has the path C:\mydir\2004 where the worksheet is kept
,and D3 has D:\Backups\2004 , etc. I made a userform on workbook open with a
combobox to select one of the four computers.Combobox change() calls a sub
(mypaths) that has a Select statement to case which computer name was
selected. So if JeffHome is selected in combobox
Case "JeffHome"
vbackup = Sheets("mypaths").Range("d2").Value
vrounds = Sheets("mypaths").Range("d3").Value
vincome = Sheets("mypaths").Range("d4").Value
If Steves' laptop were selected
Case "SteveLapTop"
vbackup = Sheets("mypaths").Range("d6").Value
vrounds = Sheets("mypaths").Range("d7").Value
vincome = Sheets("mypaths").Range("d8").Value
Now, say I run the macro Save_Me_Now it has the path
ActiveWorkbook.SaveCopyAs Filename:= _
vbackup & "\NewBudget2005.xls"
and it would save fine on each as long as the database cells, in this
instance
D6 was setup properly. Of course, my problem is that VBackup loses it's
value and the workbook gets saved as \newbudget.xls into the current root
drive. Or code trying to run Workbooks.Open Filename:=VRounds &
"\mydata.xls" just
causes a runtime error "can't find file" because the variable VRounds is
blank.

This certainly seems confusing and a lot of work but it's better than
tracking down paths in code every time I move the workbook from computer to
computer. If you or anyone has a better way of moving back and forth I
certainly would appreciate learning how to do it.

As always, any help is always fully appreciated.

 

[Tom Ogilvy]

End Sub isn't what he meant. There is a standalone End command. If you
don't use that, it shouldn't be a problem. However, as Norman further
explained, if you are working in the VBE, there are many actions that can
reset public variables.

I would suggest putting your variable initialization case structure in a
separate routine. In the workbook_open, when the user selects which
machine, then put that value in define name

thisWorkbook.Names.Add Name:="PC", RefersTo:="=" & Combobox1.Text

then modify the case construct

sName = Application.Evaluate(thisworkbook.Names("PC").Refersto)
Select Case sName
Case "JeffHome"

then anytime you need to use your variables, first call your initialization
routine

an alternative would be to use defined names/named ranges rather than global
variables (this would be done only in the workbook_open event when the
selection is made).

Case "JeffHome"
ThisWorkbook.Names.Add Name:="Backup", RefersTo:="=mypaths!$d$2"
ThisWorkbook.Names.Add Name:="Rounds", RefersTo:="=mypaths!$d$3"
ThisWorkbook.Names.Add Name:="Income", RefersTo:="=mypaths!$d$4"


then when you want to use the path you would use

Range("Backup").Value & "MyBackup.xls"

rather then
vBackup & "MyBackup.xls"

 

[jeffP]

Tom,
this was a tremendous help. I used your alternate method suggestion,
names/named ranges.
As always, Thank you.