bVisual

ShapeSheet Functions

Published on by

Multi-line text in Visio Shape Data

The Shape Data (nee Custom Properties) dialog and window only provides a single line to view or edit text values. It is a great shame because the value can be up to 64k characters, and long text can be pasted into it. I, and many others, have complained about this for many years to Microsoft, but nothing has been done to rectify this. Of course, I do develop custom user interface components for many customers, but that is not always desirable. Therefore I sometimes re-purpose standard Visio elements to provide the ability to view or edit multi-line text in Shape Data rows.

  • Description Shape Data row
  • Edit using ScreenTip or Action
  • Scrollable multi-line text
    Scrollable multi-line text
  • The first 248 characters are displayed on mouse hover
  • Simple ShapeSheet formulas to do this
[Read more…] about Multi-line text in Visio Shape Data

Published on by

Writing a WeekNum(…) function for Visio

I needed to display the week number of dates in a Visio project of mine, but there is no WeekNum(…) function built-in, so I had to write one, and allow for the date that the week numbers begin in to be varied from 1st January.  I also needed to allow for the week numbers to go backwards from the specified week number begin date.
Excel has a WeekNum(..) function, which can take an optional parameter for the day of the week to begin on, and an IsoWeekNum(..) function, and the following table shows the values change for the first 35 days of the year:
Excel Week Num Formulas
[Read more…] about Writing a WeekNum(…) function for Visio

Published on by

Changing Visio Shape Size and Angle with Linked Data

I was recently laying out a computer room floor plan to scale in Visio, from survey data of the cabinets\racks within it, and it occurred to me that I could work a bit smarter than I first thought. Originally, I was going to just add lots of rectangles, size them, rotate them, and label each them with their name. Then I thought, why not use the Width, Depth and Name columns from the survey data to do most of the work for me? It was so simple to do, and only took a few minutes, so I thought I would share my methodology because it demonstrates how easy it is to work smarter with Visio….

These are the simple steps shown above, written out for your education….

[Read more…] about Changing Visio Shape Size and Angle with Linked Data

Published on by

Enhancing the Built-In Containers in Visio

The concept of Structured Diagrams was introduced in Visio 2010 to provide core functionality for a variety of the templates in Visio, such as the Cross-Functional Flowchart, BPMN Diagram and Wireframe Diagram. This is primarily evident as Containers, Lists and Callouts and they can be customized (see Custom Containers, List and Callouts ), as shown in some of my previous articles ( see https://blog.bvisual.net/?s=container). One example of this extension can be found in all of the flowchart shapes that placed into a swimlane in a cross-functional flowchart. They have a Shape Data row, Function, that automatically inherits the text entered into the header of the swimlane. In another example, a Visio user can use Insert / Diagram Parts / Container to visually group other shapes together, and update the header text of the container. Well, I often do want my custom shapes to inherit the text of a container, so, in this article, I show how the built-in containers can be enhanced to provide this ability.

InheritingContainerText

You should be aware that I often exploit the fact that Visio creates a local copy of each master shape that it uses within a document. This can be modified and set to match master by name on drop, to ensure that it is the one to use, even if the original Microsoft provided version is used. In this case, I use this trick to create hidden, enhanced versions of the containers that are available in the gallery of containers. Microsoft also do this with the Dynamic connector shape in a number of their own templates.
If you examine the Function Shape Data row of any of the flowchart shapes, then you will find the formula:

[Read more…] about Enhancing the Built-In Containers in Visio

Published on by

Really Snapping to Grids in Visio

Visio has the ability to refine the options for snapping shapes to just grids, but this does not necessarily mean that shapes will automatically position themselves on a grid, or that the size is a multiple of a grid. What if you want some shapes to always be positioned on a fixed grid, and the width and height to always be a multiple on the horizontal and vertical grids respectively? What if you want the grid to start at the top left rather then the default bottom left? Fortunately, the ShapeSheet of the page and shapes can provide this ability, without the need for any external code.


You can set the page to have Fixed horizontal and vertical grids, each with different minimum spacing, if necessary. You can set the grid and ruler origins using the Ruler & Grid dialog, but it will be always relative to the bottom left corner of the page. So this can be a problem if you change the print margins or the page size, if you really want the grid origin to remain in another position, like top left.
You can just leave the Grid for snapping, and even change its strength to Strong, and even enter a larger number manually. This can help with snapping to the grids, but it is not enforced to be exclusive to the grids.

[Read more…] about Really Snapping to Grids in Visio

Published on by

Using EVALCELL() in Visio to calculate Workdays

I recently agreed to calculate the number of workdays between two dates in Visio, without realising how difficult it is. Excel users are lucky because it has the NETWORKDAYS() function, but Visio users are not so fortunate. However, Visio does have the EVALCELL function which can be extremely useful in cases like this. I got the inspiration from one of the answers in StackOverflow for a SQL user who also needed a solution.

First, I created a GetDaysUser-defined Cell row to calculate the number of inclusive days between two dates. This function requires two dates passed through as the start and end arguments.

User.GetDays
=INT(((DATEVALUE(ARG("e"))+1)-DATEVALUE(ARG("s")))/1 ed)
User.GetDays.Prompt
="Get the number of actual days between two dates, eg 1st Jan to 14th Feb : "&EVALCELL(User.GetDays,"s","01 Jan 2018","e","14 Feb 2018")

Second, I created a GetAdjusteDays User-defined cell row to provide the necessary addition of 1 day if the start date falls on a Sunday, and the subtraction of 2 days or 1 days if the end date falls on a Sunday or Saturday respectively.

User.GetAdjustedDays
=INT((EVALCELL(User.GetDays,"s",ARG("s"),"e",ARG("e"))+IF(WEEKDAY(DATEVALUE(ARG("s")))=7,1,0)-IF(WEEKDAY(DATEVALUE(ARG("e")))=7,2,IF(WEEKDAY(DATEVALUE(ARG("e")))=6,1,0))))
User.GetAdjustedDays.Prompt
="Get the number of days between two dates, adjusted for start and end day of the week : "&EVALCELL(User.GetAdjustedDays,"s","01 Jan 2018","e","14 Feb 2018")

Thirdly, I used the formula from my earlier article,Prototyping Visio ShapeSheet Formulas in Excel , the GetWorkdays User-defined Cell row to fully calculate the number of workdays (weekdays) between two dates.

User.GetWorkdays
=IF(WEEKDAY(DATEVALUE(ARG("s")))<=WEEKDAY(DATEVALUE(ARG("e"))),(FLOOR(INT(DATEVALUE(ARG("e"))-DATEVALUE(ARG("s")))/7)*5)+MAX(MIN(WEEKDAY(DATEVALUE(ARG("e")))+1,6)-WEEKDAY(DATEVALUE(ARG("s"))),0),(FLOOR(INT(DATEVALUE(ARG("e"))-DATEVALUE(ARG("s")))/7)*5)+MIN((WEEKDAY(DATEVALUE(ARG("e")))+6)-MIN(WEEKDAY(DATEVALUE(ARG("s"))),6),5))/1 ed
User.GetWorkdays.Prompt
="Get the number of work days between two dates, eg 1st Jan to 14th Feb : "&EVALCELL(User.GetWorkdays,"s","01 Jan 2018","e","14 Feb 2018")

This means that I can now include these User-defined Cell rows in any shape that I need to calculate the number of workdays for.
For example, if I have a shape with Start Date and End Date Shape Data rows, I can call either the User.GetDays or User.GetWorkdays functions using the EVALCELL() function.

Prop.DurationDays.Value
=GUARD(EVALCELL(User.GetDays,"s",Prop.StartDate,"e",Prop.EndDate))
Prop.DurationWorkdays.Value
=GUARD(EVALCELL(User.GetWorkdays,"s",Prop.StartDate,"e",Prop.EndDate))

Of course, I guarded these formulas because I do not want a user to accidently overwrite them.