Using Excel 2003

I have a bunch of public variables declared as

Public Grand_IncHours As Double
Public Grand_BudgetLabor As Double
Public Grand_IncLabor As Double
Public Grand_BudgetMat As Double
and about 25 others

I made them public because they are outputted to a worksheet in a
different procedure in another module. Everything was working fine;
but, something has happened that keeps them from resetting when my
code finishes.

The lines of code at the end of the component that calculates the
Public variables is:

Call Output_to_CSSR(r4, items, sub_clins, prorateflag)
Call Format_CSSR(r4)

Application.Calculation = xlCalculationAutomatic
ActiveSheet.PageSetup.PrintArea = r4.Cells(1, 1).CurrentRegion.Address
Application.ScreenUpdating = True

End Sub

These 5 lines of code all execute fine, and the out worksheet looks
fine, the screen is updated, and the print range is correct the first
time the code is run. If I run the code a second time the Public
variables are all doubled; a third time they are tripled etc. This
continues to happen until I reset the code in the VBA editor, or close
and reopen the file. When I look in the locals window during code
execution the variables appear to be well behaved, and they are no
longer available when the code finishes; but, they pick up where they
left off the next time the code runs. I can set them to zero at the
end of the code and everything seems to work fine. I have never had
to do that before on other projects, and this one was working fine
without doing that until this afternoon.

Can anyone shed any light on this behavior?

Thanks

Ken

Module-scope or project-scope variables retain their values even when
all VBA code has completed. If you need to reset them, you should do
that as the first task that is carried out by your code.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 16:32:16 -0700 (PDT), wrote:

Using Excel 2003

I have a bunch of public variables declared as

Public Grand_IncHours As Double
Public Grand_BudgetLabor As Double
Public Grand_IncLabor As Double
Public Grand_BudgetMat As Double
and about 25 others

I made them public because they are outputted to a worksheet in a
different procedure in another module. Everything was working fine;
but, something has happened that keeps them from resetting when my
code finishes.

The lines of code at the end of the component that calculates the
Public variables is:

Call Output_to_CSSR(r4, items, sub_clins, prorateflag)
Call Format_CSSR(r4)

Application.Calculation = xlCalculationAutomatic
ActiveSheet.PageSetup.PrintArea = r4.Cells(1, 1).CurrentRegion.Address
Application.ScreenUpdating = True

End Sub

These 5 lines of code all execute fine, and the out worksheet looks
fine, the screen is updated, and the print range is correct the first
time the code is run. If I run the code a second time the Public
variables are all doubled; a third time they are tripled etc. This
continues to happen until I reset the code in the VBA editor, or close
and reopen the file. When I look in the locals window during code
execution the variables appear to be well behaved, and they are no
longer available when the code finishes; but, they pick up where they
left off the next time the code runs. I can set them to zero at the
end of the code and everything seems to work fine. I have never had
to do that before on other projects, and this one was working fine
without doing that until this afternoon.

Can anyone shed any light on this behavior?

Thanks

Ken

I will do that. Thanks for your time this evening, and thanks for a great
site.
Ken

"Chip Pearson" wrote:

Module-scope or project-scope variables retain their values even when
all VBA code has completed. If you need to reset them, you should do
that as the first task that is carried out by your code.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 16:32:16 -0700 (PDT), wrote:

Using Excel 2003

I have a bunch of public variables declared as

Public Grand_IncHours As Double
Public Grand_BudgetLabor As Double
Public Grand_IncLabor As Double
Public Grand_BudgetMat As Double
and about 25 others

I made them public because they are outputted to a worksheet in a
different procedure in another module. Everything was working fine;
but, something has happened that keeps them from resetting when my
code finishes.

The lines of code at the end of the component that calculates the
Public variables is:

Call Output_to_CSSR(r4, items, sub_clins, prorateflag)
Call Format_CSSR(r4)

Application.Calculation = xlCalculationAutomatic
ActiveSheet.PageSetup.PrintArea = r4.Cells(1, 1).CurrentRegion.Address
Application.ScreenUpdating = True

End Sub

These 5 lines of code all execute fine, and the out worksheet looks
fine, the screen is updated, and the print range is correct the first
time the code is run. If I run the code a second time the Public
variables are all doubled; a third time they are tripled etc. This
continues to happen until I reset the code in the VBA editor, or close
and reopen the file. When I look in the locals window during code
execution the variables appear to be well behaved, and they are no
longer available when the code finishes; but, they pick up where they
left off the next time the code runs. I can set them to zero at the
end of the code and everything seems to work fine. I have never had
to do that before on other projects, and this one was working fine
without doing that until this afternoon.

Can anyone shed any light on this behavior?

Thanks

Ken