World Cup Flags

If you're like me, stuck at work while the World Cup rages, you might still be lucky enough to be able to visit FIFA.com and watch the play-by-play. I'm not a big soccer fan but I depend on the realtime play-by-play to tell me what's happening during the game.

You might have also noticed those cool country flags on FIFA's website.

Fortunately, they are downloadable for use in your Excel worksheets and VBA programs. Let's build a small app to display and save them.

If you right-click on the flag images on the FIFA homepage, the URL looks like this (for Algeria):

http://img.fifa.com/imgml/flags/m/alg.gif

The icon-size flags have the following URL:

http://img.fifa.com/imgml/flags/s/alg.gif

On a hunch, I tried this URL to get a large size flag:

http://img.fifa.com/imgml/flags/l/alg.gif

The FIFA homepage also has flags with cool mirror reflections under them. They follow a similar naming scheme, but only medium size images are available in reflected form:

http://img.fifa.com/imgml/flags/reflected/m/alg.png

So to build a string that points to the correct flag, we just need to determine what size we want, and the country abbreviation as well as which version we want (normal or reflected). If we want a reflected image, we can only request medium. Note that the reflected images are PNG, while the normal ones are GIF.

The Userform

The first thing I did was create a String list of World Cup countries:

Public Const COUNTRY_LIST As String = "ALG,ARG,AUS,BRA,CHI,CIV,CMR,DEN,ENG,ESP,FRA," _
     & "GER,GHA,GRE,HON,ITA,JPN,KOR,MEX,NED,NGA,NZL,PAR," _
     & "POR,PRK,RSA,SRB,SUI,SVK,SVN,URU,USA"

Note that you can also get the flags for other countries, provided you know the three-letter abbreviation.

And you can feel free to add more to the app by visiting the FIFA World Cup Archives and adding past teams. You could even add a combo box for the year and have a different set of teams load into the listbox depending on which year you select!

To load the list of countries into a listbox on my userform, I used the following code:

Private Sub UserForm_Initialize()

  On Error GoTo ErrorHandler

  Dim countriesList() As String

  countriesList = Split(COUNTRY_LIST, ",")

  Me.CountriesList.List = countriesList

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

When the userform loads, this code splits the global constant COUNTRY_LIST by comma into a String array. This is passed into a listbox using its List Property.

The flag is displayed when an item from the listbox is selected. The ListBox_Click event builds the URL string based on the selections made on the form. A WebBrowser control is used to display the image, rather than trying to download it and load it into an Image control. That means you can right-click the control when a flag is displayed and save it to a folder!

Private Sub CountriesList_Click()

  On Error GoTo ErrorHandler

  Dim imageSize As String
  Dim Reflected As Boolean
  Dim URL As String

  ' when a country is selected, check size and reflected
  imageSize = GetImageSize
  Reflected = IsReflected

  ' if reflected image is requested, size MUST be medium
  If Reflected Then
    imageSize = "m"
    Me.optMedium.Value = True
  End If

  ' start with global base URL
  URL = BASE_URL

  ' add reflected (if necessary)
  If Reflected Then
    URL = URL & "reflected" & "/"
  End If

  ' add imagesize
  URL = URL & imageSize & "/"

  ' add country abbrevation
  URL = URL & Me.CountriesList.Value

  ' if reflected, add .png extension, else .gif
  If Reflected Then
    URL = URL & ".png"
  Else
    URL = URL & ".gif"
  End If

  ' display image in web browser control
  Me.ImageViewer.Navigate URL

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Here is the finished product:

Download

Download sample workbook