Function returns value to cell as Single Function is defined to return Double

[pcoiner]

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2.
Is this a known bug?



Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
Dim temp1 As Double
Dim temp2 As Double
Dim temp3 As Double
Dim temp4 As Double

temp1 = Math.Cos(WorksheetFunction.radians(90 - lat1)) * Math.Cos(WorksheetFunction.radians(90 - lat2))
temp2 = Math.Sin(WorksheetFunction.radians(90 - lat1)) * Math.Sin(WorksheetFunction.radians(90 - lat2)) * Math.Cos(WorksheetFunction.radians(long1 - long2))
temp3 = WorksheetFunction.Acos(temp1 + temp2)
temp4 = temp3 * 6371# * 1000#
Haversine = temp4
End Function
Public Function Haversine2(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Single
Dim temp As Double
Dim temp1 As Double
Dim temp2 As Double
Dim temp3 As Double
Dim temp4 As Double

temp1 = Math.Cos(WorksheetFunction.radians(90 - lat1)) * Math.Cos(WorksheetFunction.radians(90 - lat2))
temp2 = Math.Sin(WorksheetFunction.radians(90 - lat1)) * Math.Sin(WorksheetFunction.radians(90 - lat2)) * Math.Cos(WorksheetFunction.radians(long1 - long2))
temp3 = WorksheetFunction.Acos(temp1 + temp2)
temp4 = temp3 * 6371# * 1000#
Haversine2 = temp4
End Function

Public Sub Call_it()
Dim x As Double
Dim y As Double
x = Haversine(38.32936982, -104.7080829, 38.329384, -104.708088)
y = Haversine2(38.32936982, -104.7080829, 38.329384, -104.708088)
x = x
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

When used on a worksheet I get 1.6388373364904 for Haversine and 1.63883733749389 for Haversine2. What results do you get?

 

[Jonmo1]

Me too.

Also put in a break point at the x = x line.
When it gets there..here is the contents of my locals window
: x : 1.6388373364904 : Double
: y : 1.6388373374939 : Double

I would guess that you have your cell's formatted to only 8 decimals or less.
So they are rounded in the cell to 8 decimals, therefor the values 'appear' to be the same.

 

[pcoiner]

Welcome to MrExcel.

When used on a worksheet I get 1.6388373364904 for Haversine and 1.63883733749389 for Haversine2. What results do you get?

Hi Andrew
Same/That is correct.
If you call Haversine from a cell you get 1.63883733749389
if you do this =ACOS(COS(RADIANS(90-A9)) *COS(RADIANS(90-A14)) +SIN(RADIANS(90-A9)) *SIN(RADIANS(90-A14)) *COS(RADIANS(B9-B14))) *6371 * 1000
you get 1.6388373364904.
There in lies my rub

 

[pcoiner]

Hi Jonmo1
Formatting is Number with 15 decimal places.
[TABLE="width: 202"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1.638837336490400[/TD]
[/TR]
[TR]
[TD]1.636085299376870

[/TD]
[/TR]
</tbody>[/TABLE]
I'm telling you the function passes back a Single not a Double

 

[Andrew Poulsom]

Hi Andrew
Same/That is correct.
If you call Haversine from a cell you get 1.63883733749389
if you do this =ACOS(COS(RADIANS(90-A9)) *COS(RADIANS(90-A14)) +SIN(RADIANS(90-A9)) *SIN(RADIANS(90-A14)) *COS(RADIANS(B9-B14))) *6371 * 1000
you get 1.6388373364904.
There in lies my rub

No, for Haversine in a cell I get 1.6388373364904.

 

[pcoiner]

Hi Andrew
I might have misread your reply.
When in the Visual Basic editor I get the correct answer.
When in a cell of a worksheet When I call Haversine I get the same answer as Haversine2 from the Visual Basic editor.

 

[pcoiner]

Hi Andrew
No matter what I try formatting etc. I can't get the correct answer from a cell!

 

[pcoiner]

Hi Andrew
Any Suggestions.......................got a staff meeting will be right back.
thanks in advance

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D
1	lat1	38.329369820	1.6388373364904	1.63883733749389
2	long1	-104.708082900
3	lat2	38.329384000
4	long2	-104.708088000
Sheet9

Cell Formulas
Range	Formula
C1	=Haversine(B1,B2,B3,B4)
D1	=Haversine2(B1,B2,B3,B4)