I am working on a Visio VSTO project where I need to synchronise a UI element with the current session Microsoft Office Theme. At first, I thought that there would be some way of getting a value from the Visio object model, but I was wrong…
The Office Theme can be selected from either the Visio Options, or the Account Options panels.
[Read more…] about Getting the Microsoft Office Theme ValueI 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
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?
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.
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.
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.