Javascript to Excel VBA?
Hi
I don't know what this function do, but i think there seems to be some
wrong conversions in it.
1) JavaScript function atan2 is not as same as in Excel. one difference
is the order in arguments.
so, you should change
sigma = Application.WorksheetFunction.Atan2(sinSigma, cosSigma)
to
sigma = Application.WorksheetFunction.Atan2(cosSigma,sinSi gma)
there are some more differences between two, but for simplicity i ignore
those.
2)JavaScript is case sensitive but not in VBA. so,
a = 1 + uSq / 16384 * (4096 + uSq * (-768 + uSq * (320 - 175 * uSq)))
b = uSq / 1024 * (256 + uSq * (-128 + uSq * (74 - 47 * uSq)))
deltaSigma = b * sinSigma * (cos2SigmaM + b / 4 * (cosSigma * (-1 + 2
* cos2SigmaM * cos2SigmaM) - b / 6 * cos2SigmaM * (-3 + 4 * sinSigma *
sinSigma) * (-3 + 4 * cos2SigmaM * cos2SigmaM)))
s = b * a * (sigma - deltaSigma)
these are not equivarant to the corresponding expressions in JavaScript.
assume variable A in JavaScript as aup and variable B as bup in VBA,
then above expressions can be rewritten like
aup = 1 + uSq / 16384 * (4096 + uSq * (-768 + uSq * (320 - 175 * uSq)))
bup = uSq / 1024 * (256 + uSq * (-128 + uSq * (74 - 47 * uSq)))
deltaSigma = bup * sinSigma * (cos2SigmaM + bup / 4 * (cosSigma * (-1 +
2
* cos2SigmaM * cos2SigmaM) - bup / 6 * cos2SigmaM * (-3 + 4 * sinSigma *
sinSigma) * (-3 + 4 * cos2SigmaM * cos2SigmaM)))
s = b * aup * (sigma - deltaSigma)
i have no idea about how variable ht1, ht2 in VBA have a relation with
expressions in JavaScript when converting and there might be some other
wrong conversions that i've missed in it.
keizi
"Ron J" wrote in message
ups.com...
Hello,
I am trying to convert a JavaScript function into an Excel VBA. The
JavaScript function is as follows:
p1 and p2 contain the latitude and longitude in radians
LatLong.distVincenty = function(p1, p2) {
var a = 6378137, b = 6356752.3142, f = 1/298.257223563; // WGS-84
ellipsiod
var L = p2.lon - p1.lon;
var U1 = Math.atan((1-f) * Math.tan(p1.lat));
var U2 = Math.atan((1-f) * Math.tan(p2.lat));
var sinU1 = Math.sin(U1), cosU1 = Math.cos(U1);
var sinU2 = Math.sin(U2), cosU2 = Math.cos(U2);
var lambda = L, lambdaP = 2*Math.PI;
var iterLimit = 20;
while (Math.abs(lambda-lambdaP) 1e-12 && --iterLimit0) {
var sinLambda = Math.sin(lambda), cosLambda = Math.cos(lambda);
var sinSigma = Math.sqrt((cosU2*sinLambda) * (cosU2*sinLambda) +
(cosU1*sinU2-sinU1*cosU2*cosLambda) *
(cosU1*sinU2-sinU1*cosU2*cosLambda));
if (sinSigma==0) return 0; // co-incident points
var cosSigma = sinU1*sinU2 + cosU1*cosU2*cosLambda;
var sigma = Math.atan2(sinSigma, cosSigma);
var sinAlpha = cosU1 * cosU2 * sinLambda / sinSigma;
var cosSqAlpha = 1 - sinAlpha*sinAlpha;
if (cosSqAlpha==0) return Math.abs(a*L).toFixed(3); // two points
on equator
var cos2SigmaM = cosSigma - 2*sinU1*sinU2/cosSqAlpha;
var C = f/16*cosSqAlpha*(4+f*(4-3*cosSqAlpha));
lambdaP = lambda;
lambda = L + (1-C) * f * sinAlpha *
(sigma +
C*sinSigma*(cos2SigmaM+C*cosSigma*(-1+2*cos2SigmaM*cos2SigmaM)));
}
if (iterLimit==0) return NaN // formula failed to converge
var uSq = cosSqAlpha * (a*a - b*b) / (b*b);
var A = 1 + uSq/16384*(4096+uSq*(-768+uSq*(320-175*uSq)));
var B = uSq/1024 * (256+uSq*(-128+uSq*(74-47*uSq)));
var deltaSigma =
B*sinSigma*(cos2SigmaM+B/4*(cosSigma*(-1+2*cos2SigmaM*cos2SigmaM)-
B/6*cos2SigmaM*(-3+4*sinSigma*sinSigma)*(-3+4*cos2SigmaM*cos2SigmaM)));
var s = b*A*(sigma-deltaSigma);
s = s.toFixed(3); // round to 1mm precision
return s;
}
To the best of my knowledge, I have the following VBA code:
Public Const Pi = 3.14159265358979
Public Function Radians(x)
Radians = Pi * x / 180#
End Function
Function SDistance(lat1, lat2, ht1, long1, long2, ht2) ' in meters
' Calculate geodesic distance (in m) between two points specified by
latitude/longitude
' using Vincenty inverse formula for ellipsoids. The
latitude/longitude
are in
' decimal degrees.
a = 6378137 ' Part of the WGS-84
b = 6356752.3142 ' ellipsiod datum
f = 1 / 298.257223563
lat1 = Radians(lat1) 'Convert decimal degrees
lat2 = Radians(lat2) 'to radians since Excel trig
long1 = Radians(long1) 'functions use radians by
long2 = Radians(long2) 'default
L = long2 - long1
U1 = Atn((1 - f) * Tan(lat1))
U2 = Atn((1 - f) * Tan(lat2))
sinU1 = Sin(U1)
cosU1 = Cos(U1)
sinU2 = Sin(U2)
cosU2 = Cos(U2)
lambda = L
lambdaP = 2 * Pi
iterLimit = 20
Do While ((Abs(lambda - lambdaP) 1 / (10 ^ 12)) And (iterLimit
0))
iterLimit = iterLimit - 1
sinLambda = Sin(lambda)
cosLambda = Cos(lambda)
sinSigma = ((cosU2 * sinLambda) * (cosU2 * sinLambda) + _
(cosU1 * sinU2 - sinU1 * cosU2 * cosLambda) * (cosU1 * sinU2 -
sinU1 * cosU2 * cosLambda)) ^ (1 / 2)
cosSigma = sinU1 * sinU2 + cosU1 * cosU2 * cosLambda
sigma = Application.WorksheetFunction.Atan2(sinSigma, cosSigma)
sinAlpha = cosU1 * cosU2 * sinLambda / sinSigma
cosSqAlpha = 1 - sinAlpha * sinAlpha
cos2SigmaM = cosSigma - 2 * sinU1 * sinU2 / cosSqAlpha
c = f / 16 * cosSqAlpha * (4 + f * (4 - 3 * cosSqAlpha))
lambdaP = lambda
lambda = L + (1 - c) * f * sinAlpha * (sigma + c * sinSigma *
(cos2SigmaM + c * cosSigma * (-1 + 2 * cos2SigmaM * cos2SigmaM)))
Loop
uSq = cosSqAlpha * (a * a - b * b) / (b * b)
a = 1 + uSq / 16384 * (4096 + uSq * (-768 + uSq * (320 - 175 *
uSq)))
b = uSq / 1024 * (256 + uSq * (-128 + uSq * (74 - 47 * uSq)))
deltaSigma = b * sinSigma * (cos2SigmaM + b / 4 * (cosSigma * (-1 +
2
* cos2SigmaM * cos2SigmaM) - b / 6 * cos2SigmaM * (-3 + 4 * sinSigma *
sinSigma) * (-3 + 4 * cos2SigmaM * cos2SigmaM)))
s = b * a * (sigma - deltaSigma)
s = Round(s, 3) 'round to 1mm precision
If (cosSqAlpha = 0) Then
s = Abs(a * L) 'two points on equator
s = Round(s, 3)
End If
htall = ht1 - ht2
sqval = htall ^ 2 + s ^ 2
SDistance = s
If (sinSigma = 0) Then SDistance = 0 'co-incident points
If (iterLimit = 0) Then SDistance = "Error" 'formula failed to
converge
End Function
Unfortunately, I am not getting the same result with the JavaScript
function. Can anyone help?
Thanks!
|