Overwriting cell formulas with FormulaForceU

  • Article

Recently I was attempting to use the FormulaU method of a Cell object to set the formula of the cell, but each time I tried to call the method I was prompted with a ‘Circular formula reference’ error.  This was a little puzzling as the formula I was trying to write to the cell did not contain any circular references and I could manually enter the formula in the cell using the ShapeSheet UI without error.

image

The problem was the original formula in the cell contained a formula using the SETATREF() function.

If you are not familiar with this function then take a look at this article for a detailed explanation. 

https://blogs.msdn.com/b/visio/archive/2006/09/29/777101.aspx

I received this error because the SETATREF() function in the target cell forwards the content that is sent to the cell via the UI or the API.  In my case the formula I was attempting to set was being forwarded to the cell that was referenced by the SETATREF() function resulting in the Circular Reference error.

FormulaForceU() is the answer as this method will overwrite the content of the target cell no matter what it contains, even GUARD is ignored.