Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Help: Javascript to Excel VBA?

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!

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 126
Default 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!


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Javascript to Excel VBA?

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! :-)

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 126
Default Javascript to Excel VBA?

Hi Ron
glad to hear it working.

keizi

"Ron J" wrote in message
ups.com...
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! :-)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Excel cell through Javascript vinu Excel Discussion (Misc queries) 0 December 11th 09 10:04 PM
Converting a row or column in excel to a Javascript array ncikusa Excel Discussion (Misc queries) 0 April 4th 06 10:04 PM
Open a webpage using Javascript in Excel [email protected] Excel Programming 5 January 17th 06 10:05 PM
Javascript functions in Excel output page Ant276 Excel Programming 0 November 11th 04 11:28 PM
Possible to write Excel macros in JavaScript? Christopher M. Balz Excel Programming 0 December 16th 03 11:27 PM


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"