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:
[Read more…] about Writing a WeekNum(…) function for Visio
Solutions
Triggering Shape Mouse Enter and Leave JavaScript events in Visio Online
I am working on a project where Visio Online process diagrams are embedded within a SharePoint web page, and was surprised that the JavaScript onShapeMouseEnter() event was not being triggered as the mouse cursor moved over some of my shapes. After much searching by myself, and my colleague Seb Craft, we found that only the sub-shapes in the group were firing the event … So what was the problem?
Using Advanced Installer with Viso VSTO Add-Ins
I have written many add-ins for Visio over the years, some for private clients for use in-house, some as products for public release by my clients or by me, and some just to help me with my day-to-day Visio development tasks. The ease of installation is always an issue, and I have been using a digital certificate to guarantee its authenticity in almost all of these scenarios. I have also included licensing code from a third-party for some of my own products, but have been aware that the third-party product I have been using is not ideal because it needs its own registered component on the client machine. So, when a client asked that I use Advanced Installer to create the installation setups so that their licensing system could be used, then I was (and still am) all for it because it could become a better method for myself. However, there are some issues that I am overcoming through this transition ….not least that an apparently correctly assigned digital certificate was actually not valid.
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.
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.
Anchoring scaled shapes to page corners in Visio
I have read that other users have bemoaned the lack of a anchor to top left, or something similar in Visio, so I thought I would demonstrate the issue, then propose a solution. In particular, there are some master shapes on the Annotations and Title Blocks stencils in the Visio Extras category that you would expect to have this capability, but don’t. Wouldn’t it be useful to have the ability to keep the shape size and anchor position to page bottom left, bottom right, top right or top left? This would mean that the page size or scale could be changed, but the annotations and title blocks could remain anchored.
First, it is important to know that Visio shapes are anchored, by default, to the bottom left of a page (or the shape that they are inside the group of). These values are returned from the formulas in the PinX and PinY cells.
Secondly, the location of the pin within the shape is defined by the formulas in the LocPinX and LocPinY cells, and these are relative to the bottom left corner of the shape.
Then it is just a matter of adding some Actions rows to the ShapeSheet of each master in the Document Stencil. The principle is to utilise the User.AntiScale value to reset the formulas in the Width, Height, PinX or PinY cells. The User.AntiScale row exists in many Master shapes because it is useful for de-scaling values. It has the formula: