Help: Javascript to Excel VBA?

[Ron J]

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 && --iterLimit>0) {
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!

 

[kounoike]

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,sinSigma)
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]

Hi Keizi,

Thanks! I finally got it working. Before I saw your post, I actually
resorted to adding alert boxes in the Javascript code to compare the
variable values. I also noticed that the Atan2 part gave different
results, but I did not know what the differences were.

Thank you very much!

 

[kounoike]

Hi Ron
glad to hear it working.

keizi