Understanding data types

0
votes

First post here! I struggling with what seems to be a very basic aspect of writing macro VBA functions for use within Microsoft Excel.

The function I'm trying to build will take two set of coordinates and find the distance between them. I've been able to input the formula I need and get the answers I want but because this formula is calculating distance around the curvature of the Earth the formula is annoyingly long and needs to be manually altered for each new case. So I've tried to create a new macro function which stores the formula and simply takes the four relevant coordinate points as variables. However I'm now getting the #VALUE Error when I try to use the new function.

The formula I've been using is:

=(3280.84*(6371 *(2*ATAN2(SQRT(1-(SIN(ABS(Destination Latitude-Origin Latitude)*PI()/180/2)^2+COS(Origin Latitude *PI()/180)*COS(Destination Latitude *PI()/180)*SIN(ABS(Destination Longitude-Origin Longitude)*PI()/180/2)^2)),SQRT((SIN(ABS(Destination Latitude-Origin Latitude)*PI()/180/2)^2+COS(Origin Latitude *PI()/180)*COS(Destination Latitude *PI()/180)*SIN(ABS(Destination Longitude-Origin Longitude)*PI()/180/2)^2))))))

This gives me the distance in US feet.

I've tried to translate this into an easier function within the Excel VBA window below:

Public Function LatLngDistance(Lat1 As Single, Lng1 As Single, Lat2 As Single, 
Lng2 As Single)

LatLngDistance = (3280.84 * (6371 * (2 * Application.Atan2(Application.SQRT(1 - (Application.Sin(Abs(Lat1 - Lat2) * Application.Pi() / 180 / 2) ^ 2 + Application.Cos(Lat2 * Application.Pi() / 180) * Application.Cos(Lat1 * Application.Pi() / 180) * Application.Sin(Abs(Lng1 - Lng2) * Application.Pi() / 180 / 2) ^ 2)), Application.SQRT(Application.Sin(Abs(Lat1 - Lat2) * Application.Pi() / 180 / 2) ^ 2 + Application.Cos(Lat2 * Application.Pi() / 180) * Application.Cos(Lat1 * Application.Pi() / 180) * Application.Sin(Abs(Lng1 - Lng2) * Application.Pi() / 180 / 2) ^ 2)))))

End Function

Because I get the #VALUE error when I try to use this function, I'm led to believe that my error is mislabeling the four variables as the wrong data types.

1
vbaexcel
Try calling your function from a VBA Sub instead of from the worksheet - you will get useful error messages that way instead of just #VALUE – Tim Williams
I would highly recommend splitting that function up into separate lines, can you even read that? – Cody Ｇ
Don't forget that the earth is not truly round! – user4039065
Can you provide an example lat/long pairs that give you an error? – coolhand

1 Answers

1
votes

Application.Cos, Application.Sin and Application.SQRT don't work in VBA. Use the built in VBA functions like so

LatLngDistance = (3280.84 * (6371 * (2 * Application.Atan2(Sqr(1 - (Sin(Abs(Lat1 - Lat2) * Application.Pi() / 180 / 2) ^ 2 + Cos(Lat2 * Application.Pi() / 180) * Cos(Lat1 * Application.Pi() / 180) * Sin(Abs(Lng1 - Lng2) * Application.Pi() / 180 / 2) ^ 2)), Sqr(Sin(Abs(Lat1 - Lat2) * Application.Pi() / 180 / 2) ^ 2 + Cos(Lat2 * Application.Pi() / 180) * Cos(Lat1 * Application.Pi() / 180) * Sin(Abs(Lng1 - Lng2) * Application.Pi() / 180 / 2) ^ 2)))))