Find the Coordinates of a Shape Object

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

map coordinate logo
pic1 map coordinate

More Posts

Excel Vlookup Function

The VLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a table and return a corresponding