Determine number of seconds between now and top of the hour

[gailb]

I have some code in ppt vba that runs a presentation for a pre-described number of seconds. Instead of a set number of seconds, how can I get the current time minus whatever the top of the next hour? So, example, the current time is 09:55:01. I'd like the number of seconds to be the current time minus 10:00:00. I guess that's 259 seconds. That would replace the 120 in the code below.

Code: Select all

Sub OnSlideshowPageChange()
    If SlideShowWindows(1).View.SlideShowName = "Custom Show 1" Then
        If B_Loop_2On = False Then
            lngStart = Timer
            B_Loop_2On = True
        Else
            If Timer > lngStart + 120 Then SlideShowWindows(1).View.GotoNamedShow ("Custom Show 2")
        End If
    End If
End Sub

[HansV]

Change 120 to

86400 * (TimeSerial(Hour(Now) + 1, 0, 0) - Now)

[gailb]

Thanks Hans. How can I make that a variable such as, myTime, and then replace the 120 with myTime?

[HansV]

Code: Select all

Sub OnSlideshowPageChange()
    Dim lngSeconds As Long
    If SlideShowWindows(1).View.SlideShowName = "Custom Show 1" Then
        If B_Loop_2On = False Then
            lngStart = Timer
            B_Loop_2On = True
        Else
            lngSeconds = 86400 * (TimeSerial(Hour(Now) + 1, 0, 0) - Now)
            If Timer > lngStart + lngSeconds Then SlideShowWindows(1).View.GotoNamedShow ("Custom Show 2")
        End If
    End If
End Sub

[gailb]

Perfect. Thanks again Hans.

[gailb]

Hi Hans,

After giving this a go, it's not working so well for me. If you have time, could you take a look!

I'm just trying to start the presentation with slide 1, then hit the button in the bottom left which will start the Custom Show 1. At the top of the hour, the presentation will go to Custom Show 2.

Title Slide.pptm

[HansV]

I'm sorry, I find PowerPoint VBA incomprehensible. I hope that someone else can help you with this.

[SpeakEasy]

it's not working so well for me.

In what way is it not working for you?

[gailb]

When I use 86400 * (TimeSerial(Hour(Now) + 1, 0, 0) - Now), it goes to slide show 2 after about 8 seconds, not the top of the hour.

[HansV]

My suggestion was incorrect (Now returns date+time, not just time).
I don't really understand the logic of your code, but this should be better:

Code: Select all

Public B_Loop_2On   As Boolean
Public lngNextHour  As Long
Public myTimer      As Long

Sub init()
    B_Loop_2On = False
    SlideShowWindows(1).View.GotoNamedShow ("Custom Show 1")
End Sub

Sub OnSlideshowPageChange()
    If SlideShowWindows(1).View.SlideShowName = "Custom Show 1" Then
        If B_Loop_2On = False Then
            lngNextHour = 86400 * TimeSerial(Hour(Now) + 1, 0, 0)
            B_Loop_2On = True
        Else
            If Timer > lngNextHour Then SlideShowWindows(1).View.GotoNamedShow ("Custom Show 2")
        End If
    End If
End Sub

[snb]

Code: Select all

Sub M_snb()
  MsgBox 3600 - (86400 * Time Mod 3600), , "number of seconds to next full hour"
End Sub

[gailb]

Thank you Hans. It seems to be working now. Thank you snb.