CODEWITHSUNDEEP
vbaexcel
okapishomapi
okapishomapi

Reputation: 175

Excel option to calculate values for each cell only once

I have a project whose requirements include a circular reference (just so you know why I'm deliberately putting this in my workbook).

Within the doc, I'd like for each cell in the circle to calculate only once. I know this is possible through the Windows Button > Excel Options > Formulas > Enable Iterative Calculations, but I have no way to enforce that option on everyone who touches the workbook. Is there any way (through VBA, maybe, or cell/sheet options?) to make a cell calculate its value only once?

Sounds like a longshot to me, but I really really appreciate your help :)

Upvotes: 1

Views: 4464

Answers (1)

David Zemens
David Zemens

Reputation: 53653

Something like this:

Private Sub Workbook_Open()
With Application
    .Iteration = True
    .MaxIterations = 1 '<-- or whatever the maximum allowable # you want
    .MaxChange = 0.001  '<-- modify as necessary, or remove if desired
End With
End Sub

Private Sub Workbook_Close()
    Application.Iteration = False
End Sub

Place these in the Workbook module.

This was one of the first answers on Google, which you would've found had you done any searching on your own:

http://www.mrexcel.com/forum/excel-questions/444155-need-macro-enable-iterative-calculation.html

Upvotes: 4

Related Questions