help translating Javascript into Excel VBA

ajscott911

New Member
Joined
Sep 19, 2006
Messages
20
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. :banghead:

Any help would be greatly appreciated.

Adam

Code:
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
Code:
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,830
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Sep 19, 2006
Messages
20
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
Code:
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

New Member
Joined
Sep 19, 2006
Messages
20

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,
Joe, may I ask you
seems to me there is a logic problem in part of the code
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 :confused:
nor 1 nor 2 is listed
Code:
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
Code:
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 ...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top