It has long been said that Visio’s ShapeSheet was modelled on Excel’s WorkSheet with it’s ability to enter formulae from a set list of functions. Indeed, there is a lot of similarity between some of Visio’s ShapeSheet functions and similar ones found in Excel, however one should exercise some caution and check that the behaviour is as expected. I recently forgot to do this with the NOW() function, and it caused me some grief for a while.
I had constructed a Today shape in Visio, and used the NOW() function to position the shape in the horizontal, and to display the date value in a Shape Data row and in the ScreenTip.
In Excel, if you enter the NOW() function into a cell, it immediately evaluates when the formula is entered. So, if you enter the same formula in multiple cells (by typing … not copying), it will evaluate at the time that you enter it. Thus you can apparently have different times displayed.
However, if you save or close then open the workbook, then all of the NOW() formulae are re-calculated at the same time:
In Visio,the behaviour is different. In the following example, I entered the formula =NOW() into the text of three rectangles, and, as you can see, the time is always the same:
Moreover, the time updates every minute! In the following screenshot, the Event Monitor, rom the Visio SDK, shows how the NOW() function is fired every minute, in every cell that uses it (even if it is in a Master!) :
So, be careful how you use the NOW() function in Visio because overuse will slow down your diagram.
There is a registry setting that controls how often NOW fires. If you’ve set Visio to show all settings in the registry (somewhere in the Visio options settings), you find this setting:
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Visio\Application
animation = 60000
Set animation to 1000, for example, and your NOW will update every second. If you have a lot of these on the page, your drawing will flicker like nuts. But you can get things to move around on the page too, which can be neat and useful. Hence the setting is called “animation”.
I have a shape on my test page with NOW() displayed as insert-field text. It is updating live, like a real digital clock. I’ll have to remember to reset animation to 60000.
If you need values from NOW, but don’t want them to update, you can use the SETF ShapeSheet function. For example, you can blast a timestamp into a user cell to note when a shape was dropped:
EventDrop = SETF(GetRef(User.Date),NOW())
When the shape is dropped, the evaluation of NOW() will be written to the User.Date cell that I created. Interestingly, the NOW() in this cell doesn’t cause EventDrop to constantly “go off” and re-blast the value to User.Date. But you could add a bit more to the formula:
EventDrop = SETF(GetRef(User.Date),NOW())+SETF(“EventDrop”,”0″)
The second SETF in this expression overwrites the EventDrop formula, effectively erasing it.
Note that it uses quotes around the cell name and doesn’t make use of GetRef. This is an older syntax, which has the advantage of shielding the formula from being fully evaluated on entry. Visio won’t let us enter …+ SETF(GetRef(EventDrop),0) because that is a circular reference. With quotes, Visio waits until the EventDrop actually happens, THEN tries to make sense of the formula.
This quoted-cellname syntax is a bit un-kosher, and I wouldn’t recommend using it all over the place, especially for custom-named user- and shape data cells that may or may not exist. But every Visio shape has the EventDrop cell, so it is safe in this example, and is a good way to write one-time, on-drop formulas.
Thanks Chris, that’s useful for users who have the ability to mess with their Registry