help translating Javascript into Excel VBA

[ajscott911]

Hi guys,

I'm trying to make a vba equivelent of a javascript code and I'm running into problems every time I turn my head. can someone take a look at this and either point me in the right direction or come up with something that will work?

Long story short, I've been asked to make a script/code that will determine the moon phase percentage based on a 84 game day cycle. I've managed to make something that will translate the date/time from UTC to local so the UTC functions are not a problem, it's getting the calculations to spit out workable numbers that's causing the majority of my headaches.

Any help would be greatly appreciated.

Adam

function getMoonInfo(now) {
    var moonDays = 0;
    var moon = new Object();

    moonDays = (Math.floor((now - Mndate.getTime()) / msGameDay)) % 84;

    if (moonDays < 0){
        moonDays = 84 + moonDays;
    }

    moonpercent = - Math.round((42 - moonDays) / 42 * 100);

    if (moonpercent >= -10 && moonpercent <= 5)  {
        moon.phase = "NewMoon";
        moon.shortName = "NM";
        moon.name = "New Moon";
    }
    else if (moonpercent > 5 && moonpercent < 40) {
        moon.phase = "WXC";
        moon.shortName = "WXC";
        moon.name = "Waxing Crescent";
    }
    else if (moonpercent >= 40 && moonpercent <= 55) {
        moon.phase = "FQM";
        moon.shortName = "FQM";
        moon.name = "First Quarter Moon";
    }
    else if (moonpercent > 55 && moonpercent < 90) {
        moon.phase = "WXG";
        moon.shortName = "WXG";
        moon.name = "Waxing Gibbous";
    }
    else if (moonpercent >= 90 || moonpercent <= -95)  {
        moon.phase = "FullMoon";
        moon.shortName = "FM";
        moon.name = "Full Moon";
    }
    else if (moonpercent > -95 && moonpercent < -60) {
        moon.phase = "WNG";
        moon.shortName = "WNG";
        moon.name = "Waning Gibbous";
    }
    else if (moonpercent >= -60 && moonpercent <= -45) {
        moon.phase = "LQM";
        moon.shortName = "LQM";
        moon.name = "Last Quarter Moon";
    }
    else if (moonpercent > -45 && moonpercent < -10) {
        moon.phase = "WNC";
        moon.shortName = "WNC";
        moon.name = "Waning Crescent";
    }
    moon.percent = Math.abs(moonpercent);

    return moon;
}

Declared Variables

msgameday = 24*60*60*1000/25  //number of earth milliseconds in a game day
// moon date is used to determine the current phase of the moon.
// Use UTC functions to allow calculations to work for any timezone
Mndate = new Date();
Mndate.setUTCFullYear(2004, 0, 25); // Set date to 2004-01-25
Mndate.setUTCHours(2, 31, 12, 0);    // Set time to 02:31:12.0000

 

[Joe Was]

Yes, fit the calendar date into the number of days in the moon period and return one of 8 phases [each phase is 1/8 the moon period].

 

[erik.van.geit]

I'll check it out for you
but now offline for 2 days

God bless you!
Erik

 

[erik.van.geit]

would this be correct ?
seems logic to me

   A              B                    C  D                    
 1 moonPeriod     29,53058867             moonphases           
 2 my1stNewMoon   22/09/06 7:46           New Moon             
 3                                        Waxing Crescent Moon 
 4 date & time    moonphase               Waxing 1/2 Moon      
 5 19/10/06 19:52 Waning Crescent Moon    Waxing 3/4 Moon      
 6 20/10/06 19:52 New Moon                Full Moon            
 7 21/10/06 19:52 New Moon                Waning 3/4 Moon      
 8 22/10/06 19:52 New Moon                Waning 1/2 Moon      
 9 23/10/06 19:52 Waxing Crescent Moon    Waning Crescent Moon 
10 24/10/06 19:52 Waxing Crescent Moon                         
11 25/10/06 19:52 Waxing Crescent Moon                         
12 26/10/06 19:52 Waxing Crescent Moon                         
13 27/10/06 19:52 Waxing 1/2 Moon                              
14 28/10/06 19:52 Waxing 1/2 Moon                              
15 29/10/06 19:52 Waxing 1/2 Moon                              
16 30/10/06 19:52 Waxing 1/2 Moon                              
17 31/10/06 19:52 Waxing 3/4 Moon                              

moon
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
B5:B17  =INDEX($D$2:$D$9,MOD(ROUND(MOD($A5-$B$2,$B$1)/$B$1*8,0),8)+1)

[Table-It] version 06 by Erik Van Geit

for Belgium
my1stNewMoon = 9/22/2006 13:45:00 pm

 

[Joe Was]

No I don't think it is Erik.

The Moon month is 29.531 Earth days and an Earth month is 28, 30 or 31 Earth calendar days. So to fix any Earth month-day to the Lunar month and that day's moon phase requires a dynamic function. And, each moon phase needs to be an equal portion of the moon month. The 3/4 moon is not 2 days less than any other phase, it must equal the same portion of the moon month as any other phase.

Thanks for giving it a go!

 

[erik.van.geit]

Hi, Joe and everybody who's watching,

I found a site with moonphases till end 2008
entered several dates in the sheet which seemed all correct ...
of course only 8 phases ...

then entered the first new moon (Europe) I could find
29/01/06 15:15
and the last one
27/12/08 13:22
this formula MOD($A5-$B$2,$B$1)/$B$1 gave me a result of 0,993915992 (while 0 or very close to 1 is ideal)
the sheet shows me that "27/12/2008 17:40:43" would be full moon (result 3,26115E-10)

so calculation is wrong for about 1.5 hour per year

or ? ...

best regards,
Erik