Values assigned to variables staying in memory when procedure stop
 

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

Values assigned to variables staying in memory when procedure stop
 

Hi Peter,

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


---
Regards,
Norman



"Peter Rooney" wrote in message
...
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

Values assigned to variables staying in memory when procedure
 

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" wrote:

Hi Peter,

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


---
Regards,
Norman



"Peter Rooney" wrote in message
...
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

Values assigned to variables staying in memory when procedure
 

Hi Peter,

If module level variables did not retain their value

The variables concerned are declared as PUBLIC, so as to be
available to macros in more than one module

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!


---
Regards,
Norman



"Peter Rooney" wrote in message
...
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" wrote:

Hi Peter,

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


---
Regards,
Norman



"Peter Rooney" wrote in message
...
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

Values assigned to variables staying in memory when procedure
 

Cool.

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

Pete





"Norman Jones" wrote:

Hi Peter,

If module level variables did not retain their value

The variables concerned are declared as PUBLIC, so as to be
available to macros in more than one module

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!


---
Regards,
Norman



"Peter Rooney" wrote in message
...
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" wrote:

Hi Peter,

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


---
Regards,
Norman



"Peter Rooney" wrote in message
...
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