This is a quick post on some of the differences between the two methods of setting a cell formula.
Local and Universal
First off, let me clear up the difference between members ending with and without the letter U.
Visio makes a distinction in many places between local naming and ‘universal’ naming. In the UI you mostly see local naming, for example master names, page names and shape names (in the Drawing Explorer). This allows the user to see a localized version in their preferred language, while a universal set of names is maintained for consistent access when interacting with Visio via code.
So, although there are the local Formula and FormulaForce methods, you generally want to use the universal versions in your code.
FormulaForceU
When writing a formula to a cell you normally set this as a string using Cell.FormulaU:
VBA:
1: Dim vCell As Visio.Cell
2: Set vCell = ActiveWindow.SelectedCell
3: vCell.FormulaU = "Width*2"
C#:
1: var vCell = vApp.ActiveWindow.SelectedCell;
2: vCell.FormulaU = "Width*2";
[Note – if you want to see where ‘vApp’ comes from in the C# snippet, take a look at this post on using C# with Visio.]
You only need FormulaForceU when the target formula is guarded with the GUARD ShapeSheet function. With a guarded formula, a ‘Cell is guarded’ exception will be thrown if you attempt to use FormulaU, and so you have to use FormulaForceU instead, which will blow away any existing formula irrespective of what it contains.
So I think that the above is reasonably well known, but what’s not quite as clear is how the two formula setting methods work with other ShapeSheet functions.
SETATREF and SETATREFEXPR
The SETATREF and SETATREFEXPR functions are generally all about changing the normal flow of cell interactions.
SETATREF allows the hosting cell to both consume the expressions you place inside it and to push those values on to another cell at the same time. Think of a control handle whose raw input you want to accept, but also interpret in further calculation.
The purpose of the SETATREFEXPR function is to act as a placeholder for incoming values, generally from the UI, but also via code. The function is often used in conjunction with SETATREF but can also be useful just on its own.
So given these two functions how do you set those incoming values in code? Well it turns out the FormulaU (and the Result equivalents) will not overwrite the formula if it encounters these two function and will instead insert the value.
So if you have a cell such as
User.MyCell = SETATREFEXPR()
…and then run this code:
shp.CellsU(“User.MyCell”).FormulaU = “100”;
the formula would then read as:
User.MyCell = SETATREFEXPR(100)
However, if you ran this code:
shp.CellsU(“User.MyCell”).FormulaForceU = “100”;
the formula would then read as:
User.MyCell = 100
The same goes for SETATREF() – using .FormulaU sets the value and using FormulaForceU sets the entire formula.
Who wins?
So as a final quiz, what do you think would be the result of setting this cell:
User.MyCell = GUARD(SETATREFEXPR(100))
…with this code:
shp.CellsU(“User.MyCell”).FormulaU = “200”;
Well my guess (before I found out) was that Visio would spot the SETATREFEXPR in the formula and insert the 200 value. It turns out that that’s not the case – in fact GUARD wins and an exception is thrown.