When you deal with shape objects in Excel and need to do certain automation, it is critical to know the coordinates of the objects, especially for maps. The coordinates are calculated from the top left end of an Excel Worksheet. The very top left end of cell A1 will be (0, 0) for X and Y coordinates. As you move your cursor or mouse point to the right and lower position, the X and Y coordinates will increase in values.
Most of the maps that we see are not proportional to the latitude and longitude. Most of the maps are “flattened” to give an easier view and thus distorted the indication of latitude and longitude. If we try to use the latitude and longitude coordinates to calculate the X and Y coordinates of the map that you put in Excel, the chance is that an object you place on the map will be off. More distortion will be seen the more northern the map is (the smaller the latitude is). Please see the comparison of the below 2 maps. The left one is scaled more accurately with the latitude and longitude. The right one is flattened even though the drawing of the map has already taken into account some of the distortions.
Therefore, a better way is to locate the X and Y coordinates manually on the map. We can use “wks.Shapes([shape name]).left” and “wks.Shapes([shape name]).top” functions. The “wks.Shapes([shape name]).left” function gives the location of X coordinate and the “wks.Shapes([shape name]).top” gives the location of Y coordinate.
Below codes will do the work. The shape that I use is a “donut”.
Sub getLocation()
Dim wks As Worksheet
Dim XCoor As Double, YCoor As Double
Set wks = Sheets(“Position”)
XCoor = wks.Shapes(“Donut 2”).Left
YCoor = wks.Shapes(“Donut 2”).Top
Range(“S5”).Value = Round(XCoor, 2)
Range(“T5”).Value = Round(YCoor, 2)
End Sub
