Flags of the World

I was recently asked how to add a country flag to Visio Org Chart shapes automatically.  Well, as I am currently working with world data at the moment, I have taken up the challenge.  Firstly, the CIA have an excellent source of information, called the World Factbook ( https://www.cia.gov/library/publications/the-world-factbook/ ).  You can find all sorts of information about every country in the world, including, of course, their flags.  There are over 240 countries at the moment, and there are various codes used by different systems to identify them.  Even the name may not be the same, for example, do I live in UK, United Kingdom, Great Britain, United Kingdom of Great Britain and Northern Ireland or just plain old England?  Answers on a post card, please, addressed to …..?

Alternatively, a country can be identified accurately with a code, but you need to know which system is being used.  United Kingdom can be identified by GB in the Iso 2 character system, or 826 in the Iso numeric system, or UK in the FIPS 10 system.  The CIA World Factbook utilises the FIPS 10 code, so consequently, all of the country related web pages and images are coded with this system.

I have already created an Access database with all this information, so I decided to use this knowledge to create a Flags of the World Visio Master.

image

I wrote some VBA to download the gif files from CIA website, and imported them into a specially prepared group shape.  Each image shape was automatically named with the FIPS 10 code, and the Width and Height formulae were written to be 0 if the parent group shape does not represent relevant country.

image

The ShapeSheet of the parent shape was loaded with semi-colon separated lists of country related data, mainly pulled from the CIA website.  The Prop.Country Shape Data row is a fixed list so that the user can select a country by its name.  The other Shape Data rows are all guarded because their values are pulled via the Index of selected country name, from the User defined cells.

image

ShapeSheet of the GB sub-shape, below, showing that the Width and Height is 0, if the shape Name is not equal to the Prop.Fips10 value in the parent shape.  Notice that the Width and Height formulae maintain the original aspect ratio of the flag image.

image

This provides a Flags of the World Master, but I also created a Flags of the World Small Master because the first one uses the large flag images from the CIA website, but the second one uses the small image files, and thus uses less file size.

image

I have also added context sensitive hyperlinks to the CIA website, Maps Live, Google Maps and Google Earth, and one to bVisual’s website….

OK, now we are halfway there … we actually want to apply the map icon to any shape that has a Shape Data value that is a country name, but we don’t know the name of this Shape Data row in advance….

Visio 2007 Professional Data Graphics has Icon Sets, but you can only have a maximum of 5 icons in each set … certainly not enough for over 240 countries.  So, why not use an Icon Set as a placeholder for a country flag, then replace the Icon Set image with the flag for the country whose value is in the Shape Data row specified by the Icon Set Graphic Item?

That is exactly what I did in the screen-shot below:

image

I duplicated an existing Icon Set (Trend Arrow 1) in the Drawing Explorer, renamed it as FlagPlaceholder, then amended it to have one image sub-shape, called Flag.

image

Thus the Icon Set becomes available in the Data Graphics editor, and you can select the Shape Data row that contains the name of the country.  You only need to have one qualifying value, so I entered does not contain * , because I assume that there is no country in the world with that character in its name.

The attached stencil, FlagsOfTheWorld.vss http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/FlagsOfTheWorld.zip , includes the macro UpdateFlags, which will then change the flag for each selected shape.

image

So, when the macro is run, the test shapes now show the flags for each country….

image

Option Explicit
‘David Parker
‘July 2008
‘No warranties at all with this code
‘Use at you own risk

Public Sub UpdateFlags()
Dim shp As Visio.Shape
Dim subshp As Visio.Shape
Dim imgshp As Visio.Shape
Dim flagExists As Boolean
Dim cel As Visio.Cell
Dim celPrecedent As Visio.Cell
Dim aryPrecedents() As Visio.Cell
Dim country As String
Dim aryCountries() As String
Dim aryCodes() As String
Dim mstFlag As Visio.Master
Dim code As String
Dim idx As Integer
Dim filePath As String
Dim flagShp As Visio.Shape
Dim shpWidth As Double
Dim shpHeight As Double
Dim shpAspectRatio As Double
Dim flagWidth As Double
Dim flagHeight As Double
Dim flagAspectRatio As Double
Dim cloneShp As Visio.Shape
Dim cloneMst As Visio.Master

On Error Resume Next
    Set mstFlag = ThisDocument.Masters("Flags Of The World")
    If mstFlag Is Nothing Then
        Exit Sub
    Else
        aryCountries = Split(mstFlag.Shapes(1).Cells("Prop.Country.Format").ResultStr(""), ";")
        aryCodes = Split(mstFlag.Shapes(1).Cells("User.Fips10").ResultStr(""), ";")
    End If
    ‘Need a temporary writeable file need
    ‘You may need to change this but it needs to be gif extension

    filePath = Replace(Replace(ThisDocument.FullName, ".vss", ".gif"), ".vsd", ".gif")
    ‘Get the current shape(s)
    For Each shp In Visio.ActiveWindow.Selection
        If Not shp.DataGraphic Is Nothing Then
            For Each subshp In shp.Shapes
                If subshp.CellExists("User.msvCalloutType", Visio.visExistsAnywhere) <> 0 Then
                    If subshp.Cells("User.msvCalloutType").ResultStr("") = "Icon Set" Then
                        ‘We know that this shape is an Icon Set
                        If subshp.Name = "FlagPlaceholder" Then
                            ‘We know that the FlagPceholder exists
                            ‘Get the shape aspect ratio

                            shpWidth = subshp.Cells("Width").ResultIU
                            shpHeight = subshp.Cells("Height").ResultIU
                            shpAspectRatio = shpWidth / shpHeight
                            For Each imgshp In subshp.Shapes
                                If imgshp.Name = "Flag" Then
                                    ‘Found the Flag shape
                                    Set cel = subshp.Cells("User.msvCalloutIconNumber")
                                    If UBound(cel.Precedents) = 1 Then
                                        ‘Get the value in the precedent cell
                                        aryPrecedents() = cel.Precedents
                                        Set celPrecedent = aryPrecedents(1)
                                        country = celPrecedent.ResultStr("")
                                        idx = getLookup(country, aryCountries)
                                       If idx > -1 Then
                                            ‘We found a match for the country name
                                            code = aryCodes(idx)
                                            If Len(Dir(filePath)) > 0 Then
                                                ‘Clear the temp file
                                                Kill filePath
                                            End If
                                            Set cloneShp = mstFlag.Shapes(1).Duplicate
                                            cloneShp.Cells("Prop.Country").FormulaU = "=""" & country & """"
                                            cloneShp.Shapes(code).Export filePath
                                            cloneShp.Delete
                                            If Len(Dir(filePath)) > 0 Then
                                                ‘Remove the previous flag
                                                ‘Temporarily unlock the group

                                                subshp.Cells("LockGroup").FormulaU = 0
                                                imgshp.Delete
                                                Set flagShp = subshp.Import(filePath)
                                                flagShp.NameU = "Flag"
                                                flagShp.Name = "Flag"
                                                ‘Get the flag aspect ratio
                                                flagWidth = flagShp.Cells("Width").ResultIU
                                                flagHeight = flagShp.Cells("Height").ResultIU
                                                flagAspectRatio = flagWidth / flagHeight
                                                If shpAspectRatio > flagAspectRatio Then
                                                    ‘Maximise height of flag
                                                    flagShp.Cells("Height").FormulaU = "=" & shp.NameID & "!Height"
                                                    flagShp.Cells("Width").FormulaU = "=Height * " & flagAspectRatio
                                                Else
                                                    ‘Maximise width of flag
                                                    flagShp.Cells("Width").FormulaU = "=" & subshp.NameID & "!Width"
                                                    flagShp.Cells("Height").FormulaU = "=Width / " & flagAspectRatio
                                                End If
                                                flagShp.Cells("PinY").FormulaU = "=" & subshp.NameID & "!Height*0.5"
                                                flagShp.Cells("PinX").FormulaU = "=" & subshp.NameID & "!Width*0.5"
                                                ‘Reset the group lock
                                                subshp.Cells("LockGroup").FormulaU = 1
                                                If Len(Dir(filePath)) > 0 Then
                                                    ‘Clear the temp file
                                                    Kill filePath
                                                End If
                                            End If
                                        End If
                                    End If

                                    flagExists = True
                                    Exit For
                                End If
                            Next
                        End If
                    End If
                End If

                If flagExists = True Then
                    ‘Move on to the next shape in the selection
                    Exit For
                End If

            Next subshp
        End If
    Next shp
End Sub

Private Function getLookup(ByVal value As String, ByVal aryIn As Variant) As Integer
Dim ary() As String
    ary() = aryIn
Dim i As Integer
Dim found As Boolean
    For i = 0 To UBound(ary)
        If UCase(value) = UCase(ary(i)) Then
            found = True
            Exit For
        End If
    Next

    If found = True Then
        getLookup = i
    Else
        getLookup = -1
    End If
End Function

Note : The following web site was invaluable for helping me download the flag image files from the CIA web-site : http://www.vb-helper.com/howto_download_url_to_file.html

Interesting related blog: http://dbwhisperer.blogspot.com/2008/07/getting-geography-data-from-visio.html

Download files : http://cid-3350d61bc93733a9.skydrive.live.com/self.aspx/Blogs/FlagsOfTheWorld.zip

Posted in Visio. 2 Comments »

2 Responses to “Flags of the World”

  1. preston Says:

    fantastic! love it. Having trouble switching flags though. Im a coding noob but quite the fan of visio. care to help?
    thanks in advance

  2. Gilles Says:

    Very interesting. I’ve tried to adapt to my needs. Everything is fine except that the code does not delete / remove the previous flag: imgshp.Delete does not seem to work.

    I have added a few other things in subshp such as
    Set vsoShape = subshp.DrawLine(1, 1, 5, 5)
    but vsoShape.Delete does not seem to work either !

    I tried to delete directly through subshp.Shapes(1).Delete

    But nothing within subshp seems to delete !

    Can somebody help me out ?

    Thanks in advance,

    Gilles


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

davecra.wordpress.com/

Microsoft Office 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: