Can Excel options be made as default or 'sticky'

[jonn6462]

I have a question about whether certain Excel options can be set as default, or otherwise be made 'sticky' so that I don't have to set them again and again when opening the same spreadsheet.

I believe the question is generic, but in this case I am using Excel 2007 SP3 MSO and running on Win7 Enterprise (64bit) SP1.

In my example, I have a spreadsheet I use regularly that contains formulas that must be solved iteratively in order to calculate their value. Sometimes when I open the spreadsheet, I get a warning message about a circular reference, and the tool pops up suggesting to guide me to find these cells and 'fix' the formulas to make them non-circular. I then have to decline the tool, go to the Office button, click Excel Options (below), click Formulas in left column, then under Calculation options check 'Enable iterative calculation' and the issue is resolved, the values calculate quickly and I can go to work. Fine, but I've done this literally over a hundred times now with this same spreadsheet!

I'm not even sure what triggers the circular reference warning. When I close the spreadsheet, then reopen, I don't have to do this. If I close Excel entirely, then reopen the spreadsheet, I don't have to do this. It doesn't seem to matter whether I save the spreadsheet or not, if I reopen 'soon' after closing, the option to solve iteratively is still checked and I'm OK. It may be happening when I restart Windows (which I don't do very often), or maybe after running CCleaner.

My question then is this: My preference would be to make iterative calculations a global default with any new spreadsheet, and not to ever have the option uncheck itself with any spreadsheet for which I've saved this way. Is there a way to do this? Does it involve Normal.dot or a registry setting (which I can do), or is VB coding required (not preferred)? Second best would be to make iterative calculations more 'sticky' for a given saved spreadsheet. To do that, I need to understand better what upsets my spreadsheet so that the option occasionally becomes unchecked.

Thanks!

 

[jkpieterse]

The Calculation settings of Excel are determined by the FIRST workbook that is opened in an Excel session. SO if you first open a workbook with iteration turned on, iteration stays on until you turn it off (and vice versa). This also applies when yu close all workbooks and then open a new one. This too counts as "First workbook opened by Excel".

If you have a workbook that needs iteration, put this code in its ThisWorkbook module:

Private Sub Workbook_Open()
    Application.OnTime Now, "SetIteration"
End Sub

and place this code in a normal module:

Sub SetIteration()
    With Application
        .MaxIterations = 1000
        .MaxChange = 0.0001
        .Iteration = True
    End With
End Sub

 

[jonn6462]

Thanks for the response.

Sorry, I'm weak on VBE. I have written the two modules and can see them under VBAProject in the target worksheet, but don't know how to associate them with ThisWorkbook and Normal as you've indicated. Please point me where I can learn more - 'help' hasn't helped much so far...

I am surprised that a "First workbook opened by Excel" mechanism even exists. If an option is a property of the workbook, then I'm not sure if you're saying opening another workbook first simply SUPPRESSES the options for those that follow, or if it actually CLEARS them. If the latter is true, then if not careful as you work, all options would tend to revert to defaults, which seems objectionable.

Thanks again!

 

[jkpieterse]

The first macro I posted goes into the ThisWorkbook entry, which you can find in the Project explorer (View, Project explorer), under the workbook's VBAProject entry, "MicrosoftExcel Objects" branch.
The second one goes in any module under the "Modules" branch.

Te rephrase the calculation settings behaviour: It is set by the first workbook you open. For any subsequent workbook the settings remain unchanged, regardless of with which settings that workbook was saved.

 

[jonn6462]

First, thank you for your help setting up macros to enable calculation options each time I open a specific workbook. In the case where I needed iterative calculations enabled, this helped avoid circular reference warnings if not the FIRST workbook to be opened. However, now I have an even more persitent nuisance to work out. I am a mere local admin on a work computer, so now I get security warnings each and EVERY time I open this workbook because the macros are unsigned. I have read where I can set up Excel to automatically load unsigned macros when Excel starts by placing them in a workbook under the XLStart folder. Haven't done it yet, so don't know whether the method or outcome varies depending on what version of Excel or Windows. At best, this means the macro will ALWAYS apply these options whenever Excel is started. I would have preferred a way to always enable macros to run if I'm their author. Your comments or suggestions welcome. Thanks again!

 

[jkpieterse]

I think in Excel 2007 you can set up trusted folders, but I'm not near a machine with Excel2007. Check the security settings in Excel options.

 

[xenou]

See here (look for the instructions for Excel): Create, remove, or change a trusted location for your files - Excel - Office.com