Circular References

[sazza7]

Actually I require the circular reference formula in my sheet. But this sheet goes to the client as well and they get scared to see the circular reference warning. Is there a way, apart from the iteration tick thing that I can allow the circular reference and a warning does not pop up on my clients computer?

[ben_hensel]

Generally I assume circular references are either an iterative calculation or a mistake.

If you don't want (or intend) to use iteration, then my shoot-from-the-hip response is to advise that you fix the spreadsheet 'cuz the logic is broken.

[alansidman]

PUt this in the Worksheet_Activate Event in the VBE

Please Login or Register  to view this content.

[MrShorty]

Is there a way, apart from the iteration tick thing that I can allow the circular reference and a warning does not pop up on my clients computer?

I am not aware of anything. A solution like alansidman's may block the warning from popping up on the client's computer, but the calculation will likely not calculate correctly unless the client checks the allow iterative calculation check box. IMO, you do need to decide how important/essential the circular reference is. Without knowing the details of the calculation, it is difficult to say from this side of the internet just how essential the iterative calculation is.

The compromise position I might suggest is to come up with a strategy that will be able to perform the iterative calculation without the circular reference. I generally use 2 strategies when I don't want to allow the circular reference:

1) Multiple copies of the calculation. This might look something like:

Please Login or Register  to view this content.

and continue copying for as many iterations as I think the algorithm will ever need. Then, the cell for the final result can be =B50 (if I did ~50 iterations). With the correct combination of relative and absolute references, this may use a lot of cells, but it does not need to take a lot of time to put together, as most of the cells are filled in a single copy/paste operation.

2) Use a UDF. Some clients may not like the idea of using xlsm macro enabled files or want to deal with the macro warnings. If they are more comfortable with allowing macros than allowing circular references, it may be preferable to put the iterative calculation inside of a VBA UDF. A generic UDF will look something like (pseudocode):

Please Login or Register  to view this content.

Depending on your VBA knowledge and skill, this may or may not take a significant chunk of time to code and debug. It can be a useful method of dealing with iterative calculations without needing circular references.

It will be between you and your clients which of the three strategies (client allows iterative calculation, use multiple copies of calculation, VBA UDF) will be preferred.

[sazza7]

I currently have the below coding in the spreadsheet which has stopped the circular reference warning, however this seems to re-appear if they already have another spreadsheet open. Is there a way to prevent this from happening?

Please Login or Register  to view this content.

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[MrShorty]

I cannot recreate the warning popup, so i think there is something else going on.

Do you have this paired with a "deactivate" or "close" method that is resetting the clients settings back to what they were before they activated your workbook? I could see the warning being re-triggered if "allow iteration" gets turned off (whether by a procedure of yours or by the client). I would expect a procedure like this would be paired with some kind of procedure that would return Excel's option state back to the user's preferred settings. If you have another procedure that is turning iteration off, then you may need to rethink what that other procedure is doing. If the client is manually turning iteration off, then you need to persuade the client to cooperate with your spreadsheet.

If there is something else going on, then we need a better description of what is happening.