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