View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] EllaBaxter@gmail.com is offline
external usenet poster
 
Posts: 1
Default scope of public variables

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