Create Map Chart with Excel VBA

Creating a map chart in Excel using VBA requires you to have geographical data (like country names, states, or postal codes) and a method to visualize it on a map. Below is a detailed VBA code that automates the creation of a Map Chart using your data, and I’ll explain each part of the process.

Requirements:

1. You need to have Excel 365 or Excel 2021, as Map Charts are a feature introduced in those versions.
2. Your data should include geographical locations (such as country names, regions, or zip codes) and associated values you want to display on the map.

VBA Code:

Sub CreateMapChart()
    ' Define variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim rng As Range
    Dim chartData As Range   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name   
    ' Define the range of the data
    ' The first column should have geographical data, and the second column should have the corresponding values
    Set chartData = ws.Range("A1:B10") ' Change the range as needed   
    ' Create a new chart object
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=600, Top:=100, Height:=400)   
    ' Set the chart type to Map
    chartObj.Chart.ChartType = xlMap   
    ' Set the data for the chart
    chartObj.Chart.SetSourceData Source:=chartData   
    ' Adjust chart options
    With chartObj.Chart
        ' Set title
        .HasTitle = True
        .ChartTitle.Text = "Geographical Data Map"       
        ' Customize the map's appearance
        .MapChart.MapStyle = xlMapStyleShaded
        .MapChart.RegionType = xlMapRegionCountry      
        ' Add a color scale to represent the values
        .Axes(xlValue).MinimumScale = 0 ' Minimum value for color scale
        .Axes(xlValue).MaximumScale = 100 ' Maximum value for color scale      
        ' Format the data labels (optional)
        .ApplyDataLabels
    End With   
    ' Let the user know the map chart has been created
    MsgBox "Map chart created successfully!"
End Sub

Explanation of Code:

1. Variables:
   • ws: Refers to the worksheet where the data is located.
   • chartObj: Represents the chart object to be created.
   • rng: A placeholder for the range of data, though it’s not used directly here.
   • chartData: The actual range where your geographical data and corresponding values are stored (e.g., countries and sales).
2. Setting the Worksheet and Data Range:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »): Specifies the worksheet containing your data. Change « Sheet1 » to your actual worksheet name.
   • Set chartData = ws.Range(« A1:B10 »): Defines the range for your data. Column A contains geographical locations, and column B contains the corresponding values.
3. Creating the Chart:
   • Set chartObj = ws.ChartObjects.Add(…): Adds a new chart to the worksheet, specifying the position and size of the chart.
   • chartObj.Chart.ChartType = xlMap: Sets the chart type to a Map Chart.
4. Setting Data for the Chart:
   • chartObj.Chart.SetSourceData Source:=chartData: Assigns the defined data range to the map chart.
5. Customizing the Map:
   • chartObj.Chart.HasTitle = True: Enables the chart title.
   • .ChartTitle.Text = « Geographical Data Map »: Sets the title of the map chart.
   • .MapChart.MapStyle = xlMapStyleShaded: Applies a shaded map style for visualization.
   • .MapChart.RegionType = xlMapRegionCountry: Specifies that the map regions are countries. This can be changed to regions, postal codes, etc., depending on your data.
   • .Axes(xlValue).MinimumScale = 0: Defines the minimum value for the color scale.
   • .Axes(xlValue).MaximumScale = 100: Defines the maximum value for the color scale.
6. Adding Data Labels (Optional):
   • chartObj.Chart.ApplyDataLabels: This will display data labels for the values on the map.
7. Finishing Up:
   • MsgBox « Map chart created successfully! »: A message box to inform the user that the map chart has been created.

How to Use:

1. Make sure your data is in the correct format: Column A for geographic names (like countries or regions) and Column B for values (like population, sales, etc.).
2. Go to the VBA editor (press Alt + F11), create a new module, and paste the above code.
3. Modify the worksheet name and data range to match your specific data.
4. Run the macro by pressing F5 in the VBA editor.

This will generate a Map Chart in Excel based on your geographical data, with colors representing the values in the second column.