Check if Map Chart drawn successfully or not

1) The following code is able to draw a Map Chart successfully.

    Public Sub Macro1()
    
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        ActiveSheet.Shapes(i).Delete
    Next i
    
    ActiveSheet.Range("A1").Value = "Region"
    ActiveSheet.Range("A2").Value = "Germany"
    ActiveSheet.Range("B1").Value = "Header"
    ActiveSheet.Range("B2").Value = "99"
    
    ActiveSheet.Range("A1:B2").Select
    
    ActiveSheet.Shapes.AddChart2(497, xlRegionMap, 111, 222, 600, 300, False).Select
    
    End Sub

2) The following code is NOT able to draw a Map Chart successfully because there is no country named like Xermany.

    Public Sub Macro2()
    
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        ActiveSheet.Shapes(i).Delete
    Next i
    
    ActiveSheet.Range("A1").Value = "Region"
    ActiveSheet.Range("A2").Value = "Xermany"  'There is no country named like Xermany
    ActiveSheet.Range("B1").Value = "Header"
    ActiveSheet.Range("B2").Value = "99"
    
    ActiveSheet.Range("A1:B2").Select
    
    ActiveSheet.Shapes.AddChart2(497, xlRegionMap, 111, 222, 600, 300, False).Select
    
    End Sub

3) You will see the following message at the top of the Map Chart when you run the Macro2.

Map charts work best with geographical data such as state/province and country/region in separate columns. Check your data and try again.

4) I am looking for a vba macro which gives me if Map Chart drawn successfully or not.

Edit: Please note that I am using Standard Office 2021 not Office-365.

Short answer: You can’t detect that warning from VBA.

---

The message that appears on Map Charts, the one that says “Map charts work best with geographical data…”, is only a visual overlay. It is not part of the chart object model at all. Excel does not give you any property that tells you whether Bing understood the geography.

As far as VBA is concerned, the chart is created successfully even if the map is wrong. So the short answer is that you cannot detect that warning through VBA. I might be wrong, but I have not seen anything in the object model that exposes it.

Since there is no other alternative, I came up with this cool trick. Feel free to experiment with it.

---

This was tested in Office 365 Subscription.

The trick is to use the Geography data type to test the country name before creating the map. This works the same way as selecting a cell and choosing Geography from the Data tab. Once Excel converts the text to a Geography type, you can check the LinkedDataTypeState property to see if Bing accepted it or if it needs clarification.

One interesting thing I found while testing is that Excel behaves differently depending on how your list is arranged. If all your country names are in one vertical list, Excel sends the entire list to Bing in one batch. In that mode, Bing tries to standardize the names. That is why “Korea” suddenly becomes “South Korea” without showing the question mark. Bing thinks it knows what you meant and simply rewrites it.

If your entries are scattered, or if you convert them one at a time, Excel sends each lookup individually. In that mode, “Korea” is treated as ambiguous, and you get the proper question mark icon. So the correct approach in VBA is to convert one cell at a time rather than the entire range at once.

Here is a simple example that checks each country individually:

Let's say our data looks like this in Sheet1

Now paste this code in a module and run it.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim aCell As Range
    Dim Ar As Variant
    Dim i As Long
    
    Set ws = Sheet1
    Set rng = ws.Range("A1:A13")
    
    Ar = rng.Value2
    
    For i = LBound(Ar) To UBound(Ar)
        With Sheet1
            .Range("D2").Value = Ar(i, 1)
            .Range("D2").ConvertToLinkedDataType ServiceID:=536870912, _
                LanguageCulture:="en-IN"
            DoEvents
        
            Select Case .Range("D2").LinkedDataTypeState
                Case 1: .Range("B" & i).Value = "VALID"
                Case 2: .Range("B" & i).Value = "NEEDS DISAMBIGUATION"
                Case 3: .Range("B" & i).Value = "BROKEN LINK"
                Case 4: .Range("B" & i).Value = "FETCHING"
                Case Else: .Range("B" & i).Value = "NOT A LINKED DATA TYPE"
            End Select
            .Range("D2").Clear
        End With
    Next i
End Sub

You can then check if the country is ok or not before calling the main macro.

In Action

Now you may think why "India" or other countries which look correct are incorrect. Here is why...

There are two options.

Feel free to experiment with the code and tweak it to your needs.