第一个VBA程序，经纬度计算距离

//用于寻找两个点重合的位置

//定义arccos函数，因为VBA不能直接使用EXCEL函数
Function acos(x As Double) As Double
    If x >= -1 And x < -0.5 Then acos = Atn(Sqr(1 - x * x) / x) + 4 * Atn(1)
    If x >= -0.5 And x <= 0.5 Then acos = -Atn(x / Sqr(1 - x * x)) + 2 * Atn(1)
    If x > 0.5 And x <= 1 Then acos = Atn(Sqr(1 - x * x) / x)
End Function

//按照经纬度计算两点距离公式
Function distance(ax As Double, ay As Double, bx As Double, by As Double)
    distance = 111.12 * acos(Sin(ay) * Sin(by) + Cos(ay) * Cos(by) * Cos(ax - bx))
End Function

//计算比较后最近的两个点的距离
Function mindis(ax As Double, ay As Double, bx As Range, by As Range)

    Dim mix As Double
    mix = distance(ax, ay, bx(1), by(1))
    mindis = mix
    For i = 1 To 2977
        mix = distance(ax, ay, bx(i), by(i))
        If mix < mindis Then
            mindis = mix
        End If
    Next
End Function

//返回离得最近的那个点的位置
Function minpoi(ax As Double, ay As Double, bx As Range, by As Range)

    Dim mix As Double
    minpoi = 1
    mix = distance(ax, ay, bx(1), by(1))
    temp = mix

    For i = 1 To 2977
        temp = distance(ax, ay, bx(i), by(i))
        If temp < mix Then
            mix = temp
            minpoi = i
        End If
    Next
End Function