I am looking to calculate Lat and Long coordinates of whale sightings using:
1)My Lat, Long
2)True bearing
3)Distance
This can be done via online calculators, but its one at a time and clunky. I have a lot of data coming over the course of weeks I'm out on a boat, so a function I can click and drag using columns of the above three inputs would be amazing and save so much time. For info, t he website I usually use is geomidpoint.com/destination
I was given VBA code and I have been attempting to read and teach myself how to use it, but I'm not getting very far. Its very much beyond my capabilities at the moment - I can tell you the Latin nomenclature of a species I'm looking at from a blurry photograph at 800 meters, but not so good with anything in Excel beyond countifs.
I have copied the code at the end. I can get as far as copying this into a new module after opening VBA, pressing Run and giving it a name. After that I'm lost. Questions are:
1)Is there anything in the code I need to edit to be specific to my worksheet (worksheet names, column titles, etc)
2)How do I actually use it? I assumed it would be to type =NameofModule, as I thought what it was doing was creating a customised excel function for me... but that might be a fool's hope, I'm not sure. The online guides tend to go as far as press run and then leave the rest up to the imagination for noobs like myself.
Basically I need a walkthrough first time round. I have been using main sheets and then secondary sheets for calculations and graphing data so made a simplified value only sheet as an example. Any and all help is hugely appreciated!
Edit: Likely also a code error as I got the error message 'Compile error: Expected: end of statement' with the first 'Const' from the first line highlighted. Microsoft tells me this is likely misuse of parenthesis.
Function VincentyDestination(lon1 As Double, lat1 As Double, bearing As Double, distance As Double) As String Const a As Double = 6378137 ' WGS84 semi-major axis in meters Const f As Double = 1 / 298.257223563 ' WGS84 flattening Const b As Double = (1 - f) * a ' WGS84 semi-minor axis in meters
Dim phi1 As Double, lambda1 As Double, alpha1 As Double, s As Double
Dim sinAlpha1 As Double, cosAlpha1 As Double, tanU1 As Double, cosU1 As Double, sinU1 As Double
Dim sigma1 As Double, sinAlpha As Double, cos2Alpha As Double, u2 As Double
Dim A As Double, B As Double, sigma As Double, sigmaP As Double
Dim cos2SigmaM As Double, sinSigma As Double, cosSigma As Double, deltaSigma As Double
Dim phi2 As Double, lambda2 As Double, C As Double, L As Double
phi1 = WorksheetFunction.Radians(lat1)
lambda1 = WorksheetFunction.Radians(lon1)
alpha1 = WorksheetFunction.Radians(bearing)
s = distance
sinAlpha1 = Sin(alpha1)
cosAlpha1 = Cos(alpha1)
tanU1 = (1 - f) * Tan(phi1)
cosU1 = 1 / Sqr(1 + tanU1 ^ 2)
sinU1 = tanU1 * cosU1
sigma1 = Atn2(tanU1, cosAlpha1)
sinAlpha = cosU1 * sinAlpha1
cos2Alpha = (1 - sinAlpha ^ 2)
u2 = cos2Alpha * (A ^ 2 - B ^ 2) / (B ^ 2)
A = 1 + (u2 / 16384) * (4096 + u2 * (-768 + u2 * (320 - 175 * u2)))
B = (u2 / 1024) * (256 + u2 * (-128 + u2 * (74 - 47 * u2)))
sigma = s / (B * A)
sigmaP = 2 * WorksheetFunction.Pi()
While Abs(sigma - sigmaP) > 0.000000000001
cos2SigmaM = Cos(2 * sigma1 + sigma)
sinSigma = Sin(sigma)
cosSigma = Cos(sigma)
deltaSigma = B * sinSigma * (cos2SigmaM + (B / 4) * (cosSigma * (-1 + 2 * cos2SigmaM ^ 2) - (B / 6) * cos2SigmaM * (-3 + 4 * sinSigma ^ 2) * (-3 + 4 * cos2SigmaM ^ 2)))
sigmaP = sigma
sigma = s / (B * A) + deltaSigma
Wend
phi2 = Atn2(sinU1 * cosSigma + cosU1 * sinSigma * cosAlpha1, (1 - f) * Sqr(sinAlpha ^ 2 + (sinU1 * sinSigma - cosU1 * cosSigma * cosAlpha1) ^ 2))
lambda2 = Atn2(sinSigma * sinAlpha1, cosU1 * cosSigma - sinU1 * sinSigma * cosAlpha1)
C = (f / 16) * cos2Alpha * (4 + f * (4 - 3 * cos2Alpha))
L = lambda2 - (1 - C) * f * sinAlpha * (sigma + C * sinSigma * (cos2SigmaM + C * cosSigma * (-1 + 2 * cos2SigmaM ^ 2)))
lambda2 = lambda1 + L
VincentyDestination = Format(WorksheetFunction.Degrees(phi2), "0.000000") & ", " & Format(WorksheetFunction.Degrees(lambda2), "0.000000")
End Function`
Bookmarks