VBA常用代码

392、 将一列以文本保存的数字转换为数字
Sub txtToNumber()
Application.ScreenUpdating = False
For Each Rng In Range(“D2:D” & Range(“d65536”).End(xlUp).Row)
If VBA.IsNumeric(Rng) Then
Range(Rng.Address) = Rng + 0
End If
Next Rng
Application.ScreenUpdating = True
End Sub

393、 将一列中的值转换为数值
Sub valueToNumber()
Application.ScreenUpdating = False
For Each Rng In Range(“C2:C” & Range(“A65536”).End(xlUp).Row)
Rng.Value = Rng
Next Rng
Application.ScreenUpdating = True
End Sub
394、 经纬度距离计算函数
Function distance(lon1 As Range, lat1 As Range, lon2 As Range, lat2 As Range)
Dim d As Double
Dim dislon As Double
Dim dislat As Double
Dim dislatp As Double
dislon = lon1 - lon2
dislatp = lat1 + lat2
dislat = lat1 - lat2
d = 111100 * VBA.Sqr((dislon * VBA.Cos(dislatp * 3.14159265358979 / 360)) ^ 2 + dislat ^ 2)
distance = d
End Function