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

 

[Norie]

Adam

Which part do you actually need help with?

Is it the general structure for the function?

Or is it converting some of the JavaScript built-in functions/methods eg floor, round etc to VBA?

Or is it something to do with the moon object?

 

[Joe Was]

Try these:


Public Const moonPeriod = 29.53058867
Public Const my1stNewMoon As String = "9/22/2006 07:46:00 am"
'US Eastern Daylight Time!

'Standard Module code, Like: Module1.

' 1: New Moon
' 2: Waxing Crescent Moon
' 3: Waxing 1/2 Moon
' 4: Waxing 3/4 Moon
' 5: Full Moon
' 6: Waning 3/4 Moon
' 7: Waning 1/2 Moon
' 8: Waning Crescent Moon

Public Function MoonPhase(myMDDate As Date) As Integer
'Standard module code, like: Module1.

Select Case MoonDate(myMDDate)

'Day of a New Moon
Case moonPeriod To (moonPeriod * 7 / 8):
MoonPhase = 1

'Day of a Waxing Crescent Moon
Case (moonPeriod * 7 / 8) To (moonPeriod * 1 / 8):
MoonPhase = 2

'Day of a Waxing 1/2 Moon
Case (moonPeriod * 1 / 8) To (moonPeriod * 2 / 8):
MoonPhase = 3

'Day of a Waxing 3/4 Moon
Case (moonPeriod * 2 / 8) To (moonPeriod * 3 / 8):
MoonPhase = 4

'Day of a Full Moon
Case (moonPeriod * 3 / 8) To (moonPeriod * 4 / 8):
MoonPhase = 5

'Day of a Waning 1/2 Moon
Case (moonPeriod * 4 / 8) To (moonPeriod * 5 / 8):
MoonPhase = 6

'Day of a Waning 3/4 Moon
Case (moonPeriod * 5 / 8) To (moonPeriod * 6 / 8):
MoonPhase = 7

'Day of a Waning Crescent Moon
Case (moonPeriod * 6 / 8) To (moonPeriod * 7 / 8):
MoonPhase = 8

Case Else:
MoonPhase = 1

End Select
End Function

Public Function MoonDate(myMDDate As Date) As Single
'Standard module code, like: Module1.
Dim myBaseDate As Date

myBaseDate = CDate(my1stNewMoon)

MoonDate = WhereInMoon((myMDDate - myBaseDate), moonPeriod)

End Function

Public Function WhereInMoon(myMPNum As Variant, myMPDays As _
Variant) As Variant
'Standard module code, like: Module1.

If myMPNum = 1 Then
WhereInMoon = 1
Else
WhereInMoon = myMPNum - myMPDays * Int(myMPNum / myMPDays)
End If

End Function

Sub myMPhaseToDay()
'Standard module code, like: Module1.
Dim myMoonPh%
Dim myMoonStr$

' 1: New Moon
' 2: Waxing Crescent Moon
' 3: Waxing 1/2 Moon
' 4: Waxing 3/4 Moon
' 5: Full Moon
' 6: Waning 3/4 Moon
' 7: Waning 1/2 Moon
' 8: Waning Crescent Moon

myMoonPh = MoonPhase(Date)

Select Case myMoonPh
Case 1
myMoonStr = "New Moon"

Case 2
myMoonStr = "Waxing Crescent Moon"

Case 3
myMoonStr = "Waxing 1/2 Moon"

Case 4
myMoonStr = "Waxing 3/4 Moon"

Case 5
myMoonStr = "Full Moon"

Case 6
myMoonStr = "Waning 3/4 Moon"

Case 7
myMoonStr = "Waning 1/2 Moon"

Case 8
myMoonStr = "Waning Crescent Moon"

Case Else
myMoonStr = "None"
End Select

MsgBox "The Moon - Phase for: " & vbLf & _
Application.WorksheetFunction.Text(Date, "mmm dd, yyyy") & vbLf & _
"is:" & vbLf & vbLf & _
myMoonStr & "!"

End Sub

Sub myMPhaseByDate()
'Standard module code, like: Module1.
Dim myMoonPh%
Dim myDate As Date
Dim myMoonStr$, myMsg$, myTitle$, myDefault$

' 1: New Moon
' 2: Waxing Crescent Moon
' 3: Waxing 1/2 Moon
' 4: Waxing 3/4 Moon
' 5: Full Moon
' 6: Waning 3/4 Moon
' 7: Waning 1/2 Moon
' 8: Waning Crescent Moon

myMsg = "Enter the ""mm/dd/ccyy"" Date," & vbLf & _
"that you want the Moon - Phase for:"
myTitle = "InputBox Demo"
myDefault = Date

myDate = InputBox(myMsg, myTitle, myDefault)

myMoonPh = MoonPhase(myDate)

Select Case myMoonPh
Case 1
myMoonStr = "New Moon"

Case 2
myMoonStr = "Waxing Crescent Moon"

Case 3
myMoonStr = "Waxing 1/2 Moon"

Case 4
myMoonStr = "Waxing 3/4 Moon"

Case 5
myMoonStr = "Full Moon"

Case 6
myMoonStr = "Waning 3/4 Moon"

Case 7
myMoonStr = "Waning 1/2 Moon"

Case 8
myMoonStr = "Waning Crescent Moon"

Case Else
myMoonStr = "None"
End Select

MsgBox "The Moon - Phase for: " & vbLf & _
Application.WorksheetFunction.Text(myDate, "mmm dd, yyyy") & vbLf & _
"is:" & vbLf & vbLf & _
myMoonStr & "!"

End Sub

 

[ajscott911]

I've been trying to convert the java functions to a viable VBA equivelent or at the very least excel math functions but somehow the numbers aren't coming out with anything that should be like when I pump the same numbers though the javascript functions.
I can run this through Java and come out with a proper number

moonDays = (Math.floor((now - Mndate.getTime()) / msGameDay)) % 84; 
............................
moonpercent = - Math.round((42 - moonDays) / 42 * 100);

but the moment I try translating it to VBA or excel math formulas it comes out with a number so low that it might as well stay as 0% moon phaze the full 84 day cycle.

I just don't understand what I'm doing wrong.

I'll investigate Joe's suggestion here in the morning, kinda in the middle of work at the moment and let you know how it goes.

thanks guys. mucho appreciated.

Adam

 

[ajscott911]

This is extremely close to what I need but it does bring me closer than anything I've been able to do myself. thank you so much Joe, this was more than I had ever expected.

Cheers,
Adam

 

[erik.van.geit]

Hi,
Joe, may I ask you
seems to me there is a logic problem in part of the code

    'Day of a New Moon
    Case moonPeriod To (moonPeriod * 7 / 8):
    result = 1
    'Day of a Waxing Crescent Moon
    Case (moonPeriod * 7 / 8) To (moonPeriod * 1 / 8):
    result = 2

I've put this in a little loop, change the constant for testing purposes
changed the last result to 9 instead of 1
see what comes up
nor 1 nor 2 is listed

Public Const moonPeriod = 80

Sub test()
Dim i As Integer

For i = 1 To 80
    Select Case i
    'Day of a New Moon
    Case moonPeriod To (moonPeriod * 7 / 8):
    result = 1
    'Day of a Waxing Crescent Moon
    Case (moonPeriod * 7 / 8) To (moonPeriod * 1 / 8):
    result = 2
    'Day of a Waxing 1/2 Moon
    Case (moonPeriod * 1 / 8) To (moonPeriod * 2 / 8):
    result = 3
    'Day of a Waxing 3/4 Moon
    Case (moonPeriod * 2 / 8) To (moonPeriod * 3 / 8):
    result = 4
    'Day of a Full Moon
    Case (moonPeriod * 3 / 8) To (moonPeriod * 4 / 8):
    result = 5
    'Day of a Waning 1/2 Moon
    Case (moonPeriod * 4 / 8) To (moonPeriod * 5 / 8):
    result = 6
    'Day of a Waning 3/4 Moon
    Case (moonPeriod * 5 / 8) To (moonPeriod * 6 / 8):
    result = 7
    'Day of a Waning Crescent Moon
    Case (moonPeriod * 6 / 8) To (moonPeriod * 7 / 8):
    result = 8
    Case Else:
    result = 9
    End Select
Cells(i, 1) = result
Next i
End Sub

now running this makes the problem more clear

Sub test2()
    Select Case 2
    Case 3 To 1
    MsgBox "3 To 1"
    Case 4 To 5
    MsgBox "4 to 5"
    End Select
End Sub

case "higher" to "lower" is never found

or do I miss something ?

kind regards,
Erik

 

[Joe Was]

You are right. Cannot seem to get the case 8 back to 1 to work, not sure how to fix it?
Thought I had it right, but am missing something, as it works for most other days?

The way it is now Case 2 never happens?

I thought if I set an Earth month to the Lunar month and divided that period by 8 [each case is the next 1/8 of the period] then each case would work, but I am missing something?

Not sure how to fix it.

 

[erik.van.geit]

Hi, Joe,

is this what the code should do ?

temp = "entered date" - "my1stNewMoon" MOD "moonPeriod"

then I'm not sure
if temp / "moonPeriod" < 1/8 then "new moon"
if temp / "moonPeriod" < 2/8 then
...

 

[Joe Was]

These are the two functions that do what your temp is in my version [and are called by the other function "MoonPhase":

Public Function MoonDate(myMDDate As Date) As Single
'Standard module code, like: Module1.
Dim myBaseDate As Date

myBaseDate = CDate(my1stNewMoon)

MoonDate = WhereInMoon((myMDDate - myBaseDate), moonPeriod)

End Function

Public Function WhereInMoon(myMPNum As Variant, myMPDays As _
Variant) As Variant
'Standard module code, like: Module1.

If myMPNum = 1 Then
WhereInMoon = 1
Else
WhereInMoon = myMPNum - myMPDays * Int(myMPNum / myMPDays)
End If

End Function

 

[erik.van.geit]

yes

as far as I understand:
the result is always between zero and 29.53058867 (moonperiod)
correct ?

and the next part ?
is this correct ?

then I'm not sure
if temp / "moonPeriod" < 1/8 then "new moon"
if temp / "moonPeriod" < 2/8 then ...