Helpwith VBA code.

[Bassdrinker]

Code: Select all

Sub countDown()
    MsgBox ("Sub countdown called ")
    countDownComplete = False
    While countDownComplete = False
        countDownValue = Range("D2")
        hours = Left$(countDownValue, 2)
        minutes = Mid$(countDownValue, 4, 2)
        Seconds = Right$(countDownValue, 2)
        totalSeconds = (hours * 3600)
        totalSeconds = totalSeconds + (minutes * 60)
        totalSeconds = totalSeconds + (Seconds)
        MsgBox (hours & " " & minutes & " " & Seconds & " " & totalSeconds)
        If totalSeconds < 5 Then countDownCompleted = True
        Range("T1") = totalSeconds
    Wend
End Sub

Can anyone please help with the above vba code that I am trying to run using Excel 2007?

The idea is simple enough.

Set countdowncomplete to False
While countdowncomplete=Fallse
Read the time left from cell D2
Split the string into hours, minutes and seconds.
Convert hours, minutes and seconds into totalseconds
OPTION - Print totalseconds as a messagebox
if totalseconds is <5 then set countdowncomplete to true
End while loop

There is some odd behaviour that I don't understand. If I run the sub as above it appears to work perfectly although each iteration stops with a messagebox that needs to be clicked. However, if I comment out the call to the messagebox the sheet freezes as though it were being overloaded by the data from GBA.

Has anyone experienced this before?

TIA

Bassdrinker

[GaryRussell]

The count down is running in a tight loop which will use a lot of CPU and while VBA code is running Excel cannot be updated by Betting Assistant.

It is better to avoid a loop and use a timer instead. Put the following code in a module. It calls itself every second using Application.OnTime which allows Excel to continue it's usual processing during each second it is waiting. It is far more CPU efficient.

Code: Select all

Sub countDown()
    'MsgBox ("Sub countdown called ")
    countDownComplete = False
    countDownValue = Sheets("Sheet1").Range("D2").Text
    hours = Left$(countDownValue, 2)
    minutes = Mid$(countDownValue, 4, 2)
    Seconds = Right$(countDownValue, 2)
    totalSeconds = (hours * 3600)
    totalSeconds = totalSeconds + (minutes * 60)
    totalSeconds = totalSeconds + (Seconds)
    'MsgBox (hours & " " & minutes & " " & Seconds & " " & totalSeconds)
    If totalSeconds < 5 Then countDownComplete = True
    Application.EnableEvents = False
    Range("T1") = totalSeconds
    Debug.Print totalSeconds
    Application.EnableEvents = True
    If Not countDownComplete Then
        Application.OnTime Now + TimeValue("00:00:01"), "countDown"
    Else
        'Do something after count down complete
    End If
End Sub