Life with Visio and other Microsoft Toys!

Clean up Visio Masters

leave a comment »

Anyone who has created Visio masters may have noticed that the masters may require cleanup. When selecting a master to edit, you may notice that the edit window seems to be empty and you  will need to center the image to bring it back in view. Other times, the shape is bigger than the page it is sitting on in the edit window. So, a little editing on the page shapesheet is required, either the values or grabbing the sides and readjusting.

I am not sure whether the cleanup actually makes a different, but it is less of a distraction when editing the masters.

Recently I was faced with the task of doing this cleanup to more than 500 shapes, so VBA to the rescue.

With a little research, I came up with


So I used the following code to

Public Sub CleanMasters()
Dim vsoMaster As Visio.Master
Dim vsoMasterCopy As Visio.Master
Dim vsoShape As Visio.Shape
Dim i As Integer
For Each vsoMaster In ActiveDocument.Masters
If vsoMaster.Type = 1 Then
Set vsoMasterCopy = vsoMaster.Open
End If
Next vsoMaster
End Sub

So far this seems to work. I did notice that the page has a margin rather than being the same size as the master. No big deal. The key factor is that it is consistent.

Thanks JG for being a sounding board.


John Marshall… Visio MVP


Written by johnvisiomvp

October 19, 2018 at 9:24 pm

Posted in Shapes, Uncategorized, VBA, Visio

Tagged with , ,

Microsoft MVP

leave a comment »

There are a number of articles on how to become an MVP and since is the 25th anniversary of the programme, I thought I should add my view and fill in some the background.

Last year Visio turned 25.  I have volunteered with the product team for 24 of those years, something that no other MVP can say so I am in a unique position to talk about the MVP programme.  I have haunted some of the same CompuServe forums with the original MVPs and I am honoured to be able to call them friends.

So, what is an MVP? The basic definition is a supporter of a Microsoft community.  In the early days, it was a product, but it has been expanded to services.  MVPs are not paid for their service but do receive a few perks like MSDN and some get togethers.  They are not Microsoft yes men (or women) and are famous for criticizing MS.  Of course, the criticism is not hollow.  It usually is well thought out and comes with reasons.

So, what does it take to become an MVP? In the early days, it was a matter of being a prolific poster on certain CompuServe forums.  Why CompuServe? In the early 1990s, a number of companies, including Microsoft, started to use CompuServe’s forums for customer support.  This was a switch from one on one support that was available only during office hours to group support that was available 24×7.  So, while waiting for a response, you could check out questions from other posters.  This is the start of the MVP program.  In addition to looking for solutions, people were providing answers.  Other than killing time, why would they do that? They liked to help.  They enjoyed a challenge in finding a solution.  They had been helped by others and wanted to pay it back.

In 1993 Calvin Hsia, a FoxPro developer who lived in Hawaii, thought it might be fun to figure out who posted the most on the CompuServe’s forum.  He then published a list of hundreds of the forum’s “Most Verbose People” as he liked to call them.

Microsoft saw value in that verbosity, so much so that the members of “Calvin’s List,” along with its creator, became Microsoft’s first Most Valuable Professionals (MVPs).

Initially, it was one person who thought that those who were helping out on the Microsoft forums deserved some recognition.  It was just a thank you post.  This idea was taken up by others at Microsoft, who on their own time, helped expand the list.  Eventually some of the recognized were invited out to Redmond to meet the product teams they supported.  This was the first MVP summit, a small group of volunteers who enjoyed helping their online communities getting recognition from Microsoft.

This was a grass roots movement and luckily, someone terminated the programme in 1999 because they thought that the access the MVPs were getting was a potential problem.  Over the weekend, those who were helped in the forums were quite vocal with Microsoft upper management and let their feelings be known.  The programme was quickly reinstated, but now they had the backing of upper management who remain a staple of the MVP summits.  The initial MVP staff ran the MVP programme in addition to their regular work.  With the reinstatement of the programme, they now had backing and support from upper management (and a budget).

Microsoft hands out awards for people who they believe excel at helping the various Microsoft communities.  It is not a certification that can be studied for and earned.

Initially the programme was based around a few products and the leads were based with those products, now it covers most of the Microsoft products.  (I am still waiting to meet the MS Bob MVP).  Regional MVP leads have been added to address the needs of those MVPs around the world.  The regional MVP leads need to understand the full suite of Microsoft offerings.  So even though they may not know the nuisances of a certain switch in your product, they understand your passion and will try and get someone to help with the answer.

So, why did so and so not get an award? Like the Academy awards, only so many can be recognized.  This does not mean that those not receiving an Academy award are terrible actors, just that the Academy saw something to recommend one of the others.  Also, the programme has been expanded world wide and it is an extremely difficult task for the MVP leads to evaluate all the recommendations they receive.  As far as I know, the MVP leads do not have a quota for having x number of Product Y MVPs in their area, but there are constraints on how large the programme can grow within their budget.  So, there are many reasons why someone was not awarded.

So why is there not a shopping list for MVP criteria?

 So, if I answer 500 or 1,00 questions, do I qualify? What is the quality of the answers? Is it a yes/no or does it have a detailed explanation or recommendation.  Obviously, quantity alone can not be a criterion.  Who is to say that talking to a user group of 25 is less important than talking to one of 3,000.  Since they are making money, should authors be excluded? A number of authors actually loose money writing about technical products, some actually do get rich.  Again, this is another criterion that is difficult to quantify.  If the criteria is quantified like other certifications, Then we will have people going for the award rather than helping the communities.  One year, several Office MVPs were not awarded because other individuals had higher answer counts.  On further examination it was found that some of the answers were not useful and were just there to boost the counts.  So, I hope that Microsoft keeps the award criteria a mystery.  Yes, the MVP leads are human and do make mistakes, but the selection process is not easy.

MVPs do not drink the Kool-Aid, beer whiskey or iced tea, maybe lots of coffee, but definitely not the Kool-Aid.

MVPs loyalty is to the community they support, it is not to Microsoft.  They love their product or service and Microsoft understands that.  The MVPs may complain, but Microsoft knows the complaints are not hollow and are accompanied by a reason of what MS is missing or doing wrong.  Of course, MVPs are not fallible, but MS understands that.

In the same vein, MVPs understands that there is competition and they do not provide hollow criticisms.  In fact, MVPs understand and appreciate the competition and for certain scenarios may recommend competing products.

MVPs are not always on pedestals.  Okay, there are a few that have a kiss my ring attitude, but they tend not to be the long-term MVPs.  They understand they do not know everything and try to encourage others to present/publish They are the ones at the back of the room asking leading questions to help novice presenters because at one time they were there and know friends in the audience helps.  They prefer to shine the spotlight on the product rather than themselves.  MVPs inspire others to learn.


John Marshall… Visio MVP


Written by johnvisiomvp

March 29, 2018 at 5:45 pm

Posted in MVP, Uncategorized

Highland Fling

leave a comment »

A while back I was intrigued by some Lego mini-figures that one of the US MVP leads had given to some of her MVPs. This started me on a project to create a Visio Lego man. It also started me thinking on what a Visio mini-figure of me would be like. Considering my heritage, my mini-figure had to have a kilt. Kilts/skirts for Lego mini-figures is very easy. It is just a rectangle with a tab with two holes to go over the pins on the top of a Lego block. The rest of the kilt/skirt is just folded around the brick. So, making a kilt should just be a matter of finding an image of a tartan. Unfortunately, I could not find an image of the tartan I wanted with enough squares to make a kilt. No problem, I could just make my own as a Visio custom fill pattern. Initial trials showed that I needed to better understand the pattern. The brute force method meant a lot of shapes that would severely impact performance. If I was going to create a Visio custom fill pattern, I might as well do it in such that the method could be used for other tartans. So I turned to my favourite list handler, Excel. I could use Excel to create the tartan and then use SnagIt to capture the pattern. Something the US lead would approve of.
So, what is a tartan? it is a pattern based on setts, groups of coloured threads in a given order and specific thread counts. The pattern is the same in the horizontal and vertical. What you end up is a set of squares that match when the same colour threads intersect and rectangles of two mixed colours. Since this is a two up to down pattern. the mixed colours form a zigzag pattern.
In addition to being able to handle lists, Excel could handle colours. In the past, I needed to handle lists of colours and Excel was well suited for the task. I could create the fill colour for a cell based on RGB values in the same row. Sort the table and the RGB and the colour sample remained together.
So, I created an Excel workbook with a worksheet of colours, a worksheet to hold possible setts and a pile of values for sets and of course, a worksheet of instructions. The workbook also contains VBA macros to manage the colours and the setts and to create a tartan in an empty worksheet.

You can obtain the workbook from one of the Canadian Excel MVPs, Debra Dalgleish.


John Marshall… Visio MVP

Written by johnvisiomvp

February 12, 2018 at 3:47 am

Posted in Uncategorized

Call me Sheldon – Fun with Flags

leave a comment »

I am always looking for new Visio shapes and I found a collection of Flags  in SVG format. They are not complete and some of them lack the actual embellishments on the flags, but it is a start. Though not quite a one stop shop as this page is, I did find some more detailed SVGs by Googling “FLAG country-name SVG”. This took me to more detailed shapes. So, I started converting/making Visio shapes.

SVG is a good starting point for creating Visio shapes, but it has it’s limits. Some are by the nature of SVG and some are by decisions of the shape creator. So, now I have almost 200 country flags as Visio shapes. So why Visio shapes rather than just clip-art? With real Visio shapes, they can stretch. So you can have a tiny flag or a huge one. Enlarging clip art can result in jaggies. Unfortunately, not all the flags are perfect. This is a rundown of some of the issues.

So, creating a flag is easy. The simplest is the surrender flag, a simple white rectangle. You do not even have to worry about the aspect ratio. … but it is not a country flag. The next easiest would be Japan, a red circle on a white rectangle. It is just a matter of getting the right colour of red, the aspect ratio (height to width) of the flag and the ratio of the diameter of the circle to the width of the flag.

Then comes the two colour flags like Poland, a rectangle split in half. This can easily be created by a red rectangle and a white rectangle. As long as you use a grid line to position the shapes, it is no problem to join them with no overlaps or gaps. There are a number of other two colour flags, but they have embellishments (like Malta) or crosses (like Finland) where in addition to the colour and aspect ratio, you need to know the thickness of the lines in the cross. Another way to create the two colour flag is to create the rectangle of the full flag, create a line bigger than the shape, align them so the line is centered and then fragment. For a simple two colour shape, this is over kill, but the technique is useful on flags with more stripes. This is also a useful technique if the dividing line is at an angle (like Jamaica – though again, you need to know the width of the stripe).

The tricolour flags can be created by the same rectangle and line technique, but this time you will need four parallel lines. So duplicate a line three times align on the edge, distribute the lines and then group. Resize the group so it matches the rectangle and make sure that the line ends extend beyond the rectangle. select both shapes and align. Though not necessary, you should select the top and bottom line from the group and delete them. This is just in case your group and rectangle are not the same size and you do not want slivers. Select the rectangle and grouped lines and fragment. You will now have three adjacent rectangles you can colour. Finish off by grouping the flag.

So far we have yet to use SVG files except to determine colours and aspect ratios.

For flags like the US, do the same thing with the lines. I will talk about the star field later. With multiple strips, you have the option of replacing the individual lines of one colour with a single rectangle of that colour the size of the flag to use as a background. The alternate colour will hide portions of the flag to make it appear that the other colour is striped rather than solid.

So, that is some of the basics for creating flags, but before we look further, we should establish some guidelines. What size should the flags be? Flags have various aspect ratios, so the common property I am going to standardize on is a height of one inch. If they are true Visio shapes, then holding the Ctrl key and stretching should not be a problem. The next guideline is to minimize the shapes. Some flags have crosses, there is no need to have two rectangular shapes to create the crosses. Some flags seem to have bands that appear to be a colour stripe centered on top of another coloured stripe. Kenya is an example of this, a red stripe on top of a white stripe. This can be handled by three separate rectangles or a rectangle on top of a bigger rectangle. Which is my preferred solution. It is one less shape.

The SVG shapes are close, but not perfect. Either by SVG limitations or the designer, there are problems. Some of the shapes distort on stretching, and are made up of more basic shapes than are necessary. Some even have cross shape references that also distort the shape. The first clean up step is to  ungroup the shape, and then ungroup the shape… until there are no more groups, just individual shapes. Following Visio Guys recommendation, the result will be rebuilt as a single group. -> Minimize groups, minimize shapes.

The Union Jack is an interesting shape, and appears in several flags of the Commonwealth. So, I will start with converting one of the SVG versions to a proper Visio shape.  One problem with this SVG version of the shape is that the diagonals are made of 1d shapes, so the ends are not clean. Rather than match the flag outline, the line ends are at 90 degrees to the line. So, all the lines have to be replaced by rectangles and the resulting shape trimmed with a rectangle the size of the flag. When finished, we will have a basic Union Jack that can be used in other flags.

The Union Jack has a width of 60 units and a height of 30 units. Each blue corner is 25 units by 10 units. The white cross is 10 units and the red cross is 6 units. The white diagonals are 6 units. and the red diagonals are 2 units.

One thing that people find out when they try and draw the Union Jack is that the red diagonals are not centered. So, you can actually have a Union Jack upside down. So, if you divide the white diagonals from the top left in to six strips, then the 2nd and 3rd strip will be red. The red strip is not continuous over the center, it switches. When you cross the center, it is the 4th and 5th strip that are red. Going from the bottom left, it is still the 2nd and 3rd strip as red and 4th and 5th after the center.

So, a Union Jack flown properly and not in distress mode will have the large white stripe at the top next to the flag pole. If you can not cheat and use an Australian flag as a reference, all flags  that are meant to be raised on a flagpole will have a toggle on the top side of the flag so it can be attached to the rope that will haul the flag up and the bottom side will have a rope to secure the bottom end of the flag to the rope that hauls the flag up.

The stars in the SVGs were interesting. They were made up of triangles. I did try Union on the triangles, but that left gaps. So, it was a matter of selecting the shapes and doing a fragment. reselect the shapes and do Union. Selecting the shapes may be tricky because it may include the background. You need to start the selection property outside the flag and drag across making sure you do not select unnecessary shapes. When dealing with flags like the US that have a field of stars, you can select the entire star field and do a fragment, reselect and do a union.

Crescents were another group of shapes that were made of a collection of simple shapes. In this case, circles. These can be selected, fragmented and the excess shapes deleted. Remember the idea is to minimize the number of shapes for performance.

So, the collection I have got it is not perfect. I have not verified all the colours used or whether the aspect ratios are correct, but it is a start. I have put a sample of the flags in the MSDN Gallery. I am not sure what I will do with the full collection, but I am hoping at some point Microsoft will have a store for Visio shapes and stencils.


John Marshall… Visio MVP

Written by johnvisiomvp

February 12, 2018 at 3:24 am

Posted in Uncategorized

Colour My World Part II

leave a comment »

Visio does not handle rows well and lists easily in the shapesheet, you can not do basic things like sorting the rows. So I use my favourite list handling app, Excel. When I need to manage large amounts of shape data, I manage it in Excel and then use VBA to remove the old shape data and populate the shape data section from Excel. I also use Excel to handle arrays within the shape data so the various lists remain in sync.

Here is the earlier experience Colour My World

On one of my project I also had to have the shape data in sync with an xml file. With Excel, that was easy. Just add a new column for XML and populate it with a formula that combined the column headers as part of an XML tag with the correct data. It did require a bit of finesse getting an XML column into a single string, but even there I was assisted by a Word VBA macro to remove the extraneous tabs.

In the first blog I talked about handling a handful of colours. I unfortunately got carried away and started to search the net for more colours that had names. The result was that I had more than 4,700 colours. There were a number of RGB values that had several names and several names that had multiple RGB values. There was also a few lists that could stand on their own, Lego colours, HTML colours, Crayola colours, Wikipedia colours, ColorHexa and Pantone Colours. Most of the Pantone colours did not have common names and Crayola has changed the names of some the crayons over the years. I even found some VBA code that would generate a worksheet of the colour spectrum showing the RGB values at various frequencies. Some of the lists also included hyperlinks to more information. The Colours spreadsheet contains a list of all the colours, but not their hyperlinks.

In the search for lists of colours and names, sometimes I got names and RGB values, sometimes they were names and RGH hex values and even times with there was a name and sample but no values. Luckily the colour samples, when copied into a worksheet. The colour samples became the cell fill colour. So I needed a toolbox of VBA code and cell formulas to manage the list.

The aim was to get a worksheet with a column for the colour name, columns for the R,G&B values and a column for the hex of the RG&B value. I also wanted to colour the cells with the hex value as a sample of what the colour looks like.

So I used the following code to extract the RGB values from the cells fill colour.

Function getRGB(rCell As Range, Optional opt As Integer) As Long
' =getRGB(D14,1)=getRGB(D14,2) =getRGB(D14,3)
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
C = rCell.Interior.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256

If opt = 1 Then
getRGB = R
ElseIf opt = 2 Then
getRGB = G
ElseIf opt = 3 Then
getRGB = B
getRGB = C
End If
End Function

If you want to recreate the hex value of the RGB code, use

The following code will set the fill colour of a column based on the R, G and B values in other columns.

Public Sub ColourCells()
Dim LRow As Long
Dim rCell As Range
Dim rng As Range
Dim SH As Worksheet
Dim Threshold As Integer

Set SH = ActiveSheet
With SH
LRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B2:B" & LRow)
End With
For Each rCell In rng.Cells
With rCell
.Offset(0, 3).Interior.Color = RGB(.Value, .Offset(0, 1).Value, .Offset(0, 2).Value)
End With
Next rCell
End Sub

What I noticed was that the text in the colour sample did not show up when the colour was dark. So I added another column called Obverse to indicate that with that colour, the text colour must be black or white. I did change the code in the ColourCells macro to set the value for Obverse and also apply it to the text. The results were okay. The first attempt set the value if the R,G&B values were all less than a certain threshold. Since this was done in code, I will take more time tuning the values.

One of the things I was trying to do with the list was to get the colours sorted in a pleasing manner. Initially I tried to sort by RGB, but there was a banding effect as the values of RG&B changed, especially when a value jumped from 255 to 0. I also tried to match the spectrum as can be seen in the Spectrum worksheet. It is a pleasing result, but it does not map to the named colours. I also tried mapping to the Hue with some results, but again there were banding issues. I even tried a fudge by grouping values together. Again,  there was only slight improvements. So for now I am leaving sorting alone.

I could leave the lists as a mixture of values and formulas, but I was concerned with performance from all the recalculations. So all the columns were copied to new columns  and pasted as values. 

I have asked one of the Excel MVPs to host the workbook. I will update this post when it is live.

John Marshall… Visio MVP

Written by johnvisiomvp

November 14, 2017 at 11:05 am

Posted in Excel, Shapes, Uncategorized, VBA, Visio

Tagged with , ,

Updated Pool Table

leave a comment »

It looks like the original pool table shape had a problem with internationalization. I have updated the shape and the issue has been eliminated.

In the original designed I used lists to control the colour of the cloth. One list was used  to present colour options to the user by displaying names of colours. The second list was a list of the RGB values those colours represented. When the user was presented with the list of colours I would get an index of the colour in that list. I would then use that index to get the RGB value from the RGB list. I had another user cell that contained the RGB value that was retrieved from the RGB list.

The problem appears that though I can place a text string of an RGB value in the foreground colour cell and it worked, but in French, German and Russian* it would be set to black. In the original Visio there were 24 colours numbered from 0 to 23. Later, it was possible to enter any RGB value to get a wide palette of colours. So, when the formula failed it returned a value of zero, which in Visio means black. Testing also showed that entering unquoted RGB values in the Foreground cell in other languages worked. Was it a matter that Visio was applying internationalization rules to colours?

* It is great to have friends who have Visio installed in these languages on their machines.

The workaround I used was to replace the string of RGB values with a list of unquoted RGB values.

I was able to simplify the shape into a single shape with a User cell containing a text string of an RGB value and the Foreground cell pointing to it.

So, if you had this problem with my shape, try downloading an updated version.


John Marshall… Visio MVP

Written by johnvisiomvp

August 4, 2017 at 6:50 pm

Posted in Shapes, Uncategorized, Visio

Tagged with ,

Security Badge update

leave a comment »

I have taken another look at the Security Badge Visio shape I uploaded to the Technet Gallery and though it was passable, I could have done better.

The shoulder and the head in the picture  were two separate shapes so they were joined. As Visio Guy is always stressing the fewer shapes or groups, the better. The White bars, the hole and the picture background were all separate shapes and they were replaced by punching holes in the background. A new shape was added behind the shape so that there was a background visible through the holes.

Changing the background of the badge was always tricky if you changed the shape colour, all the sub shape colours also changed. In this new shape colour I “Guard” the black colour of the silhouette.

Minor point, but I changed the bounding box of the shape. I ungrouped the shape and then regrouped all but the metal clip. I then locked the LockCalcWH in the Protection section of the shape before adding the clip to the group.

The original download of the shape was as a stencil, but I have switched it to a drawing. So you just drag and drop to add the shape to your collection. If there were more shapes, I would have left it as a stencil.

So what can the shape be used for? If you are creating a workflow, you can indicate that a certain security level is required or it can be used to indicate that a badge is being created. It can also be used for a Data Graphic, that can be embellish a shape with different colours of security badges to indicate that a certain security level is required. (Go for it JDP)


John Marshall… Visio MVP

Written by johnvisiomvp

July 25, 2017 at 6:49 pm

Posted in Uncategorized