Excel vb高德天地图坐标互相转换

在excel 宏中vb 实现 高德与天地图（gps）坐标的互相转换

1

2右键插入模块

3实现代码

代码如下，之前没接触过vb可能实现过程有点繁琐,不过能实现功能

Function 天地图坐标转高德Lng(wgLon, wgLat)
  x = 0
  y = 0
  Pi = 3.14159265358979
  a = 6378245#
  ee = 6.69342162296594E-03
  If 是否国外(wgLon, wgLat) Then
    x = wgLon
    y = wgLat
  Else
    dLat = transformLat(wgLon - 105#, wgLat - 35#)
    dLon = transformLon(wgLon - 105#, wgLat - 35#)
    radLat = wgLat / 180# * Pi
    magic = Sin(radLat)
    magic = 1 - ee * magic * magic
    sqrtMagic = Sqr(magic)
    dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
    dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
    y = wgLat + dLat
    x = wgLon + dLon
    End If
天地图坐标转高德Lng = x
End Function

Function 天地图坐标转高德Lat(wgLon, wgLat)
  x = 0
  y = 0
  Pi = 3.14159265358979
  a = 6378245#
  ee = 6.69342162296594E-03
  If 是否国外(wgLon, wgLat) Then
    x = wgLon
    y = wgLat
  Else
    dLat = transformLat(wgLon - 105#, wgLat - 35#)
    dLon = transformLon(wgLon - 105#, wgLat - 35#)
    radLat = wgLat / 180# * Pi
    magic = Sin(radLat)
    magic = 1 - ee * magic * magic
    sqrtMagic = Sqr(magic)
    dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
    dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
    y = wgLat + dLat
    x = wgLon + dLon
  End If
天地图坐标转高德Lat = y
End Function
Function 高德转天地图Lng(wgLon, wgLat)
  x = 0
  y = 0
  Pi = 3.14159265358979
  a = 6378245#
  ee = 6.69342162296594E-03
  If 是否国外(wgLon, wgLat) Then
    x = wgLon
    y = wgLat
  Else
    dLat = transformLat(wgLon - 105#, wgLat - 35#)
    dLon = transformLon(wgLon - 105#, wgLat - 35#)
    radLat = wgLat / 180# * Pi
    magic = Sin(radLat)
    magic = 1 - ee * magic * magic
    sqrtMagic = Sqr(magic)
    dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
    dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
    y = wgLat - dLat
    x = wgLon - dLon
    End If
高德转天地图Lng = x
End Function

Function 高德转天地图Lat(wgLon, wgLat)
  x = 0
  y = 0
  Pi = 3.14159265358979
  a = 6378245#
  ee = 6.69342162296594E-03
  If 是否国外(wgLon, wgLat) Then
    x = wgLon
    y = wgLat
  Else
    dLat = transformLat(wgLon - 105#, wgLat - 35#)
    dLon = transformLon(wgLon - 105#, wgLat - 35#)
    radLat = wgLat / 180# * Pi
    magic = Sin(radLat)
    magic = 1 - ee * magic * magic
    sqrtMagic = Sqr(magic)
    dLat = (dLat * 180#) / ((a * (1 - ee)) / (magic * sqrtMagic) * Pi)
    dLon = (dLon * 180#) / (a / sqrtMagic * Cos(radLat) * Pi)
    y = wgLat - dLat
    x = wgLon - dLon
  End If
高德转天地图Lat = y
End Function


Function 是否国外(lon, lat)
 ss = False
 If lon < 72.004 Or lon > 137.8347 Then
 ss = True
 ElseIf lat < 0.8293 Or lat > 55.8271 Then
 ss = True
 End If
 是否国外 = ss
End Function

Function transformLat(x, y)
    Pi = 3.14159265358979
    a = 6378245#
    ee = 6.69342162296594E-03
    ret = -100# + 2# * x + 3# * y + 0.2 * y * y + 0.1 * x * y + 0.2 * Sqr(Abs(x))
     ret = ret + (20# * Sin(6# * x * Pi) + 20# * Sin(2# * x * Pi)) * 2# / 3#
     ret = ret + (20# * Sin(y * Pi) + 40# * Sin(y / 3# * Pi)) * 2# / 3#
     ret = ret + (160# * Sin(y / 12# * Pi) + 320 * Sin(y * Pi / 30#)) * 2# / 3#
    transformLat = ret
End Function
Function transformLon(x, y)
    Pi = 3.14159265358979
    a = 6378245#
    ee = 6.69342162296594E-03
     ret = 300# + x + 2# * y + 0.1 * x * x + 0.1 * x * y + 0.1 * Sqr(Abs(x))
    ret = ret + (20# * Sin(6# * x * Pi) + 20# * Sin(2# * x * Pi)) * 2# / 3#
    ret = ret + (20# * Sin(x * Pi) + 40# * Sin(x / 3# * Pi)) * 2# / 3#
    ret = ret + (150# * Sin(x / 12# * Pi) + 300# * Sin(x / 30# * Pi)) * 2# / 3#
    transformLon = ret
End Function

4，使用excel函数，效果如下

上述有问题也可直接下载excel使用，最好使用office excel打开，并启用宏

https://download.csdn.net/download/jellywfguo/20007073