• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

markwilliams12

New Member
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]
Code:
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?
 
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.. :p
 
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?
 
Mark

Try changing your lines

Code:
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]
 
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?
 
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
 
Back
Top