Dynamic map using Excel and VBA

Create an intelligent dynamic interactive map using Excel and VBA.

For many, the ability to generate dynamic, interactive maps directly from their Excel spreadsheets has brought valuable dimensions to their analysis and presentation. Dynamic maps allow you to:

• show on a map how data relates/integrates 
• map key information/results so it becomes more visual and understandable

Example: Rail links proposal

Suppose there is a proposal to build a new monorail link for a theme park. There are ten (10) proposed new links connecting various locations in the park.

Screenshot 1: Map of theme park and proposed links

Setting up a dynamic map in Excel

How to map those rail links in the map as shown in Screenshot 1 to make those proposed links more understandable? More importantly how to set up a dynamic map, e.g. let’s say links 8 to 10 are not included then the links will be invisible?

Step 1: Map the links

Map Links 1 to 10 in the map. This can be done by firstly activating drawing toolbar in your Excel spreadsheet: View -> Toolbar -> Drawing

Screenshot 2: Drawing toolbar in Excel

Then click AutoShapes „³ Lines, you can then start mapping the lines using a straight line, curve or freeform. Screenshot 1 illustrates the example of the mapped links.

Step 2: Name the link

Name each link, i.e. Link 1 to Link 10 by clicking the respective line and typing in the name box which is located in the upper left of the formula box. For example the link from Fantasyland to Frontierland in the screenshot below was named Link 5.

Screenshot 3: Name each links

Step 3: Create a link switch

Create a link switch to enable the user to include or exclude certain links. In this example we custom format the switch as binary (1, 0) with 1 = “Included” and 0 = “Excluded”. Data validation of 1, 0 is then used for each switch. If you would like to learn more about custom formats similar to this switch and data validation, refer to our other tutorials.

Step 4: Create range names

Create range name for the Links Switch (Column F) as well as for the Links (Column B). For example the links switch is named LinksSwitch and the Links are named Links.

Screenshot 4: Create range name

VBA code for dynamic map

Press Alt+F11 to open the Microsoft VB editor in Excel and create a new module. Refer to Screenshot 5 for the code. The bullet points below set out the logic of the VBA macro

• The macro loops through the set-up range and activates/hides the links
• The macro should be run every time the link has been activated or de-activated
• The first part of the macro is to set-up the variables, i.e. Links and LinksSwitch
• The next part is to set the loop over the Links, i.e. Link 1 to 10 (i = 1 to 10)
• If the link is included (=1), then the link will be displayed. In this example, refer to sub-macro called DisplayLink which sets the included links to be displayed in red
• If the link is excluded (=0), then hide the link. In this example, refer to sub-macro called HideLink which sets the excluded links to be displayed in grey.

The last step would be to create the button for the macro and assign the macro to it. For instance, how the map in Screenshot 1 will change if Links 8 to 10 (in the middle of the theme park) are excluded? All you need to do is to turn off the link switch and hit the macro button. The result is shown in Screenshot 6...

Sub LinksMap()
  Dim ActiveLink As String
  Dim i As Integer
 
  Dim rngLinks As Object
  Dim rngLinksSwitch As Object
 
  Set rngLinks = Range("Links")
  Set rngLinksSwitch = Range("LinksSwitch")
 
  'Loop over all Links in the set-up range. This could be improved by using a For Each statement.
  For i = 1 To 10
       
    'pick up the name of the active link
    ActiveLink = rngLinks(i).Value
   
    'Only run the test if the links has a name
    If ActiveLink <> "" Then
       
      'If the Link is "on" (=1) then apply 'bring it to front' to the 'on'-link
      If rngLinksSwitch(i) = 1 Then
        Call DisplayLink(ActiveLink)
      Else
        'Bring the 'off'-link to front
        Call HideLink(ActiveLink)
      End If
   
    End If
 
  Next i
ActiveSheet.Shapes("Map").Select

End Sub


Sub DisplayLink(LinkName As String)

    ActiveSheet.Shapes("Link " & LinkName).Select
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 10

End Sub


Sub HideLink(LinkName As String)

    ActiveSheet.Shapes("Link " & LinkName).Select
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 23

End Sub

You can see that the red lines in the middle of the park are now hidden (turned to grey).

Screenshot 6: Dynamic map with the included links