Values assigned to variables staying in memory when procedure stop

[Guest]

Good afternoon, again!

I have a Worksheet_Change macro that only kicks in if the TARGET is within
certain worksheet boundaries.

Within this macro, I have a MSGBOX that displays the values of the variables
containing the first/last rows/columns in my "Database" range, which are
usually set by a macro called DefineDatabase. However, although
DefineDatabase isn't run before the MSGBOX is displayed, it displays the
correct values! The variables in question are declared at Module and not
Procedure level.

DefineDatabase HAS been run several times times throughout the morning as
part of my testing, and it doesn't contain any commands to set the variables
to zero before it starts, or when it finishes.

I somewhat mistakenly thought that when a macro stopped running, the values
held by variables were reset to NOTHING/NIL/NADA/ZERO. Is this not the case?
The variables concerned are declared as PUBLIC, so as to be available to
macros in more than one module, but they're not STATICs or anything like that.

It's probably just a Friday afternoon thing, but I'd be grateful if someone
could help me out here, otherwise I'm liable to spend all weekend thinking
"...but how dows it KNOW?"

Yes, I know I should get out more often..!

Thanks in advance and have a good weekend

Pete

 

[Norman Jones]

Hi Peter,

Module level variables retain their assigned values (unless cleared)
throughout an Excel session.

 

[Guest]

Norman,

Well, you live and learn.

I assume that Procedure level variables lose theirs when the procedure ends..?

Oh, and as for yesterday's problem, it wasn't a corrupted workbook after all.

Because of the way in which I was concatenating worksheet values and
variable names to create the final save As workbookname, the whole thing was
getting a bit screwed up. Although i couls save correctly with only the
Filename parameter, the minute I added password, file type, readonly
recommended, that was when it all went wrong. Excel couldn't determine the
correct file format and was trying to save the workbook AS a folder instead
of INTO it - hence the files with the TempFile type names.
What eventually solved the problem was changing the filetype from xl9795 to
xlnormal - don't ask me why but it DOES work.

Here's a sample of the final code, if you're interested:

Sub SaveTestData()
Application.DisplayAlerts = False
YearWeekResourceName
SetFolderName
SaveString = Year.Value & "-" & Format(WeekNumber.Value, "00") & " " &
TestDataCell.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

(TestDataCell is set in a loop which cycles through the contents of a range
of cells and calls Saveme each time)

Sub YearWeekResourceName()
Set WeekNumber = Sheets("Database").Range("WeekNumber")
Set Year = Sheets("Database").Range("Year")
Set ResourceName = Sheets("Database").Range("ResourceName")
End Sub

Sub SetFolderName()
FolderName = "D:\Pete's Operations\TestData\"
End Sub

Thanks for all your help on this 9and for answering this one, too) - it
really helped to know that someone was working with me through it!

Have a nice weekend

Pete

 

[Norman Jones]

Hi Peter,

If module level variables did not retain their value

would be pointless.

I assume that Procedure level variables lose theirs when the procedure
ends..?
Correct.

Oh, and as for yesterday's problem, it wasn't a corrupted workbook after
all.

Yes, I saw your final post in that thread and immediately cancelled my
letter of condolences and the flowers!

 

[Guest]

Cool.

I've learned a lot today - thanks again - see you next week, doubtless!

Pete