Nartherner
New Member
- Joined
- Mar 28, 2012
- Messages
- 17
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?
Thanks for the advice!
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?
Code:
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!