Now() then, settle down!

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.

image

However, if you save or close then open the workbook, then all of the NOW() formulae are re-calculated at the same time:

image

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:

image

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!) :

image

So, be careful how you use the NOW() function in Visio because overuse will slow down your diagram.

2 Responses to “Now() then, settle down!”

  1. Chris Roth Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: