1. The following code is able to produce 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 produce 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 name 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 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 vba macro which gives me if map chart drawn successfully or not.