Formula uses Case to generate numbers, but when charted shows zeros

[markwilliams12]

I'm tracking my weight and to extend my excel knowledge decided to track Moon Phase as well to see if there is any impact to my weight.


I've got a UDF that I use to determine Moon Phase as shown here:

[pre]

Public Function MoonPhaseNumber(InputDate)
MoonYear = Year(InputDate)
MoonMonth = Month(InputDate)
MoonDay = Day(InputDate)

If (MoonMonth < 3) Then
MoonYear = MoonYear - 1
MoonMonth = MoonMonth + 12
End If

MoonMonth = MoonMonth + 1
c = 365.35 * MoonYear
e = 30.6 * MoonMonth
jd = c + e + MoonDay - 694039.09    'jd is total days elapsed
jd = jd / 29.5305882                'divide by the moon cycle
b = Int(jd)                         'take the integer part of jd
jd = jd - b                         'subtract integer part to leave fractional part of jd
b = Round(jd * 8)                   'scale fraction from 0-8 and round

If b >= 8 Then
b = 0                           '0 and 8 are the same so turn 8 into 0
End If

Select Case b
Case 0 ' New
MoonPhaseNumber = "150"
Case 1 ' Waxing Crescent
MoonPhaseNumber = "152.5"
Case 2 ' Quarter
MoonPhaseNumber = "155"
Case 3 ' Waxing Gibbous
MoonPhaseNumber = "157.5"
Case 4 ' Full
MoonPhaseNumber = "160"
Case 5 ' Waning Gibbous
MoonPhaseNumber = "157.5"
Case 6 ' Last Quarter
MoonPhaseNumber = "155"
Case 7 ' Waning Crescent
MoonPhaseNumber = "152.5"
Case Else ' Error
MoonPhaseNumber = "ERROR"
End Select
End Function

[/pre]
The values return correctly when I call the function. But when I go to Chart the results, the chart shows that all values are zero.


Any ideas on how to chart the results of this formula?

 

[keymaster]

I'm tracking my weight and to extend my excel knowledge decided to track Moon Phase as well to see if there is any impact to my weight.

moon phases?!? really. Sorry if I sound sarcastic..

 

[markwilliams12]

The purpose of my tracking doesn't matter. If you find moon phases bizarre, then consider that my function returns the numbers 1 through 8.


Just looking for a way to chart Numeric data returned from a UDF.


Any thoughts?

 

[Hui]

Mark

Try changing your lines

MoonPhaseNumber = "152"

to

[code]MoonPhaseNumber = 152

You are setting it as text not a number

You should but don't need to add "as variant" to the end of the Function line

eg: Public Function MoonPhaseNumber(InputDate) As Variant[/code]

 

[keymaster]

You should but don't need to add "as variant" to the end of the Function line

eg: Public Function MoonPhaseNumber(InputDate) As Variant

I too guessed that. Mark, can you try those and tell us if it works?

 

[Hui]

It works without the as Variant

The main problem was the MoonPhaseNumber = "152" line which sets the cells as a Text value rather than as a Number

 

[markwilliams12]

Thanks to you all, that did the trick!