Using public variables to avoid making unnecessary changes to excel settings


New Member
Mar 28, 2012
I am familiar with private and public variable declarations but I wanted to check some code with the experts before implementing it in my spreadsheet and distributing.

Basically, the spreadsheet I have contains a necessary circular reference. I want to turn on iterative calculations if the user has them turned off. However, I don't want to presume to know better than the user and leave iterative calculations enabled after they are done working with the sheet. I know this could easily be accomplished by just turning off iterative calculations with a workbook before close event handler, but again, if the user already has chosen to enable iterative calculations, I don't want to turn them off for them.

So basically I'm thinking that I will create 2 event handlers for opening and closing. For the open handler, I want to check if iterative calculations are on and if they are, declare a variable as true. In this case, the before close handler will leave the iterative calculations on.

In the other case, if iterative calculations are off, I want to declare the variable as false, turn on iterative calculations, and then turn them off again with the before close event.

Would this be accomplished with the code below?

Public Sub Workbook_Open()

'declare the boolean iterative calculation already on/off
Dim IterAlreadyOn As Boolean

'check if iterative calculations are on or off
If Application.Iteration = False Then
    Set IterAlreadyOn = False   'set variable to false if iterations are off
    Application.Iteration = True 'turn iterations on
End If

End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)

'Turn off iterative calculations only if they were off on startup
If IterAlreadyOn = False Then
    Application.Iteration = False
End Sub

Thanks for the advice!

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand


MrExcel MVP
Sep 2, 2009
Declare the Boolean at the very top of the code module so it is persistent between the two procedures.

Try this...

[color=green]'declare the boolean iterative calculation already on/off[/color]
[color=darkblue]Private[/color] IterationSetting [color=darkblue]As[/color] [color=darkblue]Boolean[/color]

[color=darkblue]Public[/color] [color=darkblue]Sub[/color] Workbook_Open()
    IterationSetting = Application.Iteration   [color=green]'Store iterations setting[/color]
    Application.Iteration = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Public[/color] [color=darkblue]Sub[/color] Workbook_BeforeClose(Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    Application.Iteration = IterationSetting  [color=green]'Restore setting[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


Well-known Member
Aug 1, 2010
I think AlphaFrog has it right, but would also think that best-parctice would be to store/alter/restore the setting within the module where you need iterative set, not at, because it is Application level and could affect other open WBs. Same as EnableEvents.

Jaafar Tribak

Well-known Member
Dec 5, 2002
Office Version
  1. 2016
  1. Windows
Also, bear in mind that variables can go out of scope even Public or Module level variables and thus lose their contents... This is not likely but it could happen if the Project is accidently reset if for example a VBA error occurs or if a new control is added to a worksheet ... etc
In order to ensure that the setting is always preserved regardless, you could store it somewhere like in a cell or in temporary workbook name ... etc

As Tweedle pointed out , I would use the Workbook_Activate and Workbook_DeActivate events in conjuction with the Workbook_Open and Workbook_BeforClose events to store and reset the setting

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back