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

 

[pcoiner]

Example:

Excel 2010

	A	B	C	D
lat1
long1
lat2
long2

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]38.329369820[/TD]
[TD="align: right"]1.6388373364904[/TD]
[TD="align: right"]1.63883733749389[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-104.708082900[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]38.329384000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]-104.708088000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=Haversine(B1,B2,B3,B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=Haversine2(B1,B2,B3,B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I don't get it?
It will not work for me!
I've tried coping it to a new spreadsheet
I get the same answer for both functions no matter what I do??????

 

[pcoiner]

Rebooted PC and copied functions to new spreadsheet now they work in the new spreadsheet but still don't work in the old spread sheet.
I still don't understand what I've done to the old spread sheet to make it not work.
If anyone is interested I'd be more than happy to send you a copy.
I'm afraid that what ever I did I'll up and do it again and be back in the same boat!
Thanks much Andrew
I owe you a beer!

 

[Andrew Poulsom]

You can put your workbook on a share like Box.net if you want.

 

[pcoiner]

I think I have figured out what I did to cause the problem.
When I first wrote the function I did not specify the return type I left it blank.
When I realized I had not specified a type I set it to Double
My theory is that if you don't specify it the default is Single and even after I changed it to double the die was cast so to speak.
I will test this theory tomorrow if I get time.