Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Excel cell through Javascript | Excel Discussion (Misc queries) | |||
Converting a row or column in excel to a Javascript array | Excel Discussion (Misc queries) | |||
Open a webpage using Javascript in Excel | Excel Programming | |||
Javascript functions in Excel output page | Excel Programming | |||
Possible to write Excel macros in JavaScript? | Excel Programming |