user-defined function variables

Question

I wrote my very first user-defined function (UDF) yesterday, as shown below (Function Distance). It returns the correct value, but I do not understand why some Excel functions such as SIN and COS are not preceeded by the code 'Application.[function]'

For example, I have to use Application.Radians and Application.ACOS to invoke the calculations for radians and arc cosine, but this is apparently not required for SIN and COS. I do not understand why.

Thanks!

Joseph

---

Function Distance(uLat1, uLat2, uLon1, uLon2)

R = 3440.065 'average radius of Earth in nautical miles

Lat1 = Application.Radians(uLat1)

Lat2 = Application.Radians(uLat2)

Lon1 = Application.Radians(uLon1)

Lon2 = Application.Radians(uLon2)

Distance = Application.ACOS(Sin(Lat1) * Sin(Lat2) + Cos(Lat1) * Cos(Lat2) * Cos(Lon2 - Lon1)) * R

End Function

Answer 1

I don't know if the calculation in your function is correct, but I know your function return wrong results.

Rename the first function that you posted to "Distance1" and the second one "Distance2" and enter in this cells this values/formulas and format column C with "0.000000000000" (12 decimal places or more).

A1 43.617

A2 37.117

A3 13.517

A4 13.367

C1 =Distance1(A1,A2,A3,A4)

C2 =Distance2(A1,A2,A3,A4)

As you see the result of the functions is not the same. Why?

This depends on the datatype single. If you replace "Single" to "Double" in Distance2 the result is the same.

Another question is why does the first function work? Normally this function can't be work, because the application object has not a RADIANS nor a ACOS property!

The application object in excel is "intelligent", if you call a property that this object didn't has, it looks if an object within the application object has this property and return the result of it.

BTW, if you call the CELL/RANGE property of the application object it returns always the CELL/RANGE property of the ACTIVESHEET object.

There is no difference if you use the Application or the WorksheetFunction object, but I suggest to use the WorksheetFunction object, because you get the intellisense if you type in a . after that object.

But now let us see what excel calculates with this 4 values:

C3 =3440.065*ACOS(SIN(RADIANS(A1))*SIN(RADIANS(A2))+COS(RADIANS(A1))*COS(RADIANS(A2))*COS(RADIANS(A4)-RADIANS(A3)))

Funny, isn't it? Don't worry about that, excels calculation is wrong and everybody (should) know that.

Back to your UDF. Your UDF get the parameters BYREF and as VARIANT and returns a VARIANT. I suggest not ot that, because a VARIANT accepts everything, i.E. a cell with an error, strings... but you want values!

It's better to declare the type of the parameters as you want it and it's better to pass them as BYVAL, because you can modify them inside the function and use it as space.

Function DistanceW(ByVal Latitude1 As Double, ByVal Latitude2 As Double, _

    ByVal Longitude1 As Double, ByVal Longitude2 As Double) As Double

  'Returns the approximate distance between two sets of spatial coordinates

  Const R = 3440.065    'average radius of Earth in nautical miles

  With WorksheetFunction

    Latitude1 = .Radians(Latitude1)

    Latitude2 = .Radians(Latitude2)

    Longitude1 = .Radians(Longitude1)

    Longitude2 = .Radians(Longitude2)

    DistanceW = R * .Acos(Sin(Latitude1) * Sin(Latitude2) + _

        Cos(Latitude1) * Cos(Latitude2) * Cos(Longitude2 - Longitude1))

  End With

End Function

C4 =DistanceW(A1,A2,A3,A4)

As you see the result is correct, but if you intend to use your function in other applications I suggest that you never use the WorksheetFunction object, because other applications didn't have it!

In this way you have to do the calculation only with VBA.

Function DistanceV(ByVal Latitude1 As Double, ByVal Latitude2 As Double, _

    ByVal Longitude1 As Double, ByVal Longitude2 As Double) As Double

  'Returns the approximate distance between two sets of spatial coordinates

  Const R = 3440.065    'average radius of Earth in nautical miles

  Latitude1 = Rad(Latitude1)

  Latitude2 = Rad(Latitude2)

  Longitude1 = Rad(Longitude1)

  Longitude2 = Rad(Longitude2)

  DistanceV = R * ArcCos(Sin(Latitude1) * Sin(Latitude2) + _

      Cos(Latitude1) * Cos(Latitude2) * Cos(Longitude2 - Longitude1))

End Function

Private Function Pi() As Double

  'Gibt die Zahl Pi zurück

  Pi = Atn(1) * 4

End Function

Private Function Rad(ByVal X As Double) As Double

  'Wandelt Grad in Radiant

  Rad = X * Pi / 180

End Function

Private Function ArcCos(ByVal X As Double) As Double

  'Liefert den Arcus-Cosinus eines Winkels

  If Abs(X) > 1 Then

#If RaiseError Then

    Err.Raise 6, , "ArcCos(x): x > 1"

#Else

    X = 1 / X

#End If

  End If

  If Abs(X) <> 1 Then

    ArcCos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)

  Else

    ArcCos = IIf(X = 1, 0, Atn(1) * 4)

  End If

End Function

C5 =DistanceV(A1,A2,A3,A4)

As you can see the result is nearly the same, but if try to calculate the difference with

C6 =C4-C5

you see there isn't one.... in a sheet. .-)

In summary: Always use Double as data type and format cells never with more than 10 decimal places.

Andreas.

Answer 2

VBA itself is distinct from the various environments (Excel, Word and other applications which use it as a scripting/macro language).   So, VBA has some built-in functions (including for example Sin() and Cos()) but others must be "borrowed" from the hosting application)

In Excel,

Application.FunctionName  

is just a shortcut for

Application.WorksheetFunction.FunctionName

...though there are some difference in how the call works depending on which form you use.

http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

Tim

Answer 3

Am 25.11.2010 14:20, schrieb Joseph G:

Thank you very much! I learned a lot.

You're welcome.

I have a more complicated issue. May I call on you later for assistance?

Of course, but ask always here in the forum. If I don't know the solution, maybe somebody else knows.

I liked the way you explained the solution.

Thank you very much.

Andreas.

Answer 4

Thank you very much! I learned a lot.

I have a more complicated issue. May I call on you later for assistance? I liked the way you explained the solution.

Joseph

Answer 5

Thanks. I understand now.

I tweaked my UDF to make some improvements. If you notice anything amiss or in bad form, please advise. Hopefully, I'm ready to move from VBA Kindergarden to VBA First Grade. :-)

...or maybe VBA pre-school to Kindergarden. :-(

Option Explicit

Function Distance(Latitude1, Latitude2, Longitude1, Longitude2)

Dim Lat1 As Single, Lat2 As Single, Lon1 As Single, Lon2 As Single

Dim R As Single

Dim DistanceParameter As Single

Lat1 = Application.WorksheetFunction.Radians(Latitude1)

Lat2 = Application.WorksheetFunction.Radians(Latitude2)

Lon1 = Application.WorksheetFunction.Radians(Longitude1)

Lon2 = Application.WorksheetFunction.Radians(Longitude2)

R = 3440.065 'average radius of Earth in nautical miles

DistanceParameter = Application.WorksheetFunction.Acos(Sin(Lat1) * Sin(Lat2) + Cos(Lat1) * Cos(Lat2) * Cos(Lon2 - Lon1))

'This returns the approximate distance between two sets of spatial coordinates

Distance = DistanceParameter * R

End Function