Struggling with use of global Boolean

ArielZusya · #1 (**permalink**) 05-04-09, 14:48

Sorry for what is sort of a double post but it's been almost a week since I posted a followup question in this thread and I was worried that I was leaning on the one person who was helping me out over there a bit too much and so I thought I would post that question by itself here in the hopes that someone could answer what I think is a simple question.

Here's the situation... I've created an adjustable countdown timer application in Excel and want to impliment a "pause" button. It was suggested that I create a global boolean variable that I can check pause state against (True or False) and have the program act accordingly. I love the elegance of this seemingly simple solution but the trouble is I don't really know how to impliment it (I'm still a total noob when it comes to vba). Here's what I've got so far--in ThisWorkbook I've got:

Code:

Sub Workbook_Open()
        Application.Caption = "Ariel's Timer Application"
        Sheets("Timer").Select
        ActiveWindow.WindowState = xlMinimized
        frmTimer.Show
End Sub

then in frmTimer I've got:

Code:

Private Sub cmdAddMin_Click()
    frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) + 1
    frmTimer.lblAmount.Caption = CInt(frmTimer.lblAmount.Caption) + 1
    If frmTimer.cmdSubtractMin.Visible = False Then
        frmTimer.cmdSubtractMin.Visible = True
    End If
End Sub

Private Sub cmdContinue_Click()
    varPauseOn = False
    frmTimer.cmdContinue.Visible = False
    frmTimer.cmdReset.Visible = False
    frmTimer.cmdPause.Visible = True
    StartTimer
End Sub

Private Sub cmdPause_Click()
    varPauseOn = True
    frmTimer.cmdPause.Visible = False
    frmTimer.cmdContinue.Visible = True
    frmTimer.cmdReset.Visible = True
End Sub

Private Sub cmdReset_Click()
    frmTimer.cmdReset.Visible = False
    frmTimer.cmdAddMin.Visible = False
    frmTimer.cmdSubtractMin.Visible = False
    frmTimer.cmdContinue.Visible = False
    frmTimer.cmdStart.Visible = True
    frmTimer.lblAmount.Visible = True
    frmTimer.txtAmount.Value = ""
    frmTimer.lblTimeMin.Caption = ""
    frmTimer.lblTimeSec.Caption = ""
End Sub

Private Sub cmdStart_Click()
    varPauseOn = False
    ' when the start button is clicked, hide the start button
    frmTimer.cmdStart.Visible = False
    ' hide the text box and set the amount label to match
    frmTimer.txtAmount.Visible = False
    frmTimer.lblAmount.Visible = True
    frmTimer.lblAmount.Caption = frmTimer.txtAmount.Value
    frmTimer.cmdPause.Visible = True
    If frmTimer.lblTimeMin.Caption = "" Then
        If frmTimer.lblTimeSec.Caption = "" Then
            If frmTimer.txtAmount.Value > 0 Then
                ' set the caption of lblTimeMin to the amount of time
                ' specified in txtAmount and set the lblTimeSec to zero
                frmTimer.lblTimeMin.Caption = frmTimer.txtAmount.Value
                frmTimer.lblTimeSec.Caption = 0
            Else
            End If
        Else
        End If
    Else
    End If
    ' start the countdown
    StartTimer
End Sub

Private Sub cmdSubtractMin_Click()
    frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) - 1
    frmTimer.lblAmount.Caption = CInt(frmTimer.lblAmount.Caption) - 1
    If frmTimer.cmdSubtractMin.Visible = True Then
        If frmTimer.lblTimeMin.Caption = 0 Then
            frmTimer.cmdSubtractMin.Visible = False
        End If
    End If
End Sub

finally in modTimer I've got:

Code:

Dim varPauseOn As Boolean


Public Sub StartTimer()
    If varPauseOn = False Then
        ' runs the RunTimer 1 second from now
        Application.OnTime DateAdd("s", 1, Now), "RunTimer"
    Else
    End If
End Sub

Public Sub RunTimer()
    'check to see if min is greater than zero
    If frmTimer.lblTimeMin.Caption > 0 Then
        frmTimer.cmdAddMin.Visible = True
        'if it is check to see if sec is greater than zero
        If frmTimer.lblTimeSec.Caption > 0 Then
            ' if it is run the countdown on the sec only
            ' set the caption of lblTimeSec to 1 less than it is now
            frmTimer.lblTimeSec.Caption = CInt(frmTimer.lblTimeSec.Caption) - 1
            ' do it again
            StartTimer
        Else
            ' if seconds are 0 but mins are not, decrease min by 1
            frmTimer.lblTimeMin.Caption = CInt(frmTimer.lblTimeMin.Caption) - 1
            ' and set the seconds to 59
            frmTimer.lblTimeSec.Caption = 59
            ' do it again
            If frmTimer.lblTimeMin.Caption > 0 Then
                frmTimer.cmdSubtractMin.Visible = True
            End If
            StartTimer
        End If
    Else
        frmTimer.cmdSubtractMin.Visible = False
        ' if min is equal to zero check secs
        If frmTimer.lblTimeSec.Caption > 0 Then
            frmTimer.lblTimeSec.Caption = CInt(frmTimer.lblTimeSec.Caption) - 1
            ' do it again
            StartTimer
        Else
            frmTimer.cmdAddMin.Visible = False
            ' otherwise, show the start button, reset the labels and turn the
            ' text box back on
            frmTimer.cmdStart.Visible = True
            frmTimer.txtAmount.Visible = True
            frmTimer.lblAmount.Visible = False
            frmTimer.txtAmount.Value = frmTimer.lblAmount.Caption
            frmTimer.lblTimeMin.Caption = ""
            frmTimer.lblTimeSec.Caption = ""
        End If
    End If
End Sub

Essentially everything works but the pause functionality--it clicks but the timer doesn't pause. Like I said, I'm sure this is a problem with a simple solution but I just don't know what I'm doing. I've managed to fumble my way through most of it but now I'm stuck. I'm using Office 2007 (though I don't think I'm using any code specific to that version) and if it's helpful I'd be happy to post a copy of the file. Let me know. Your help is greatly appreciated! Thanks!

savbill · #2 (**permalink**) 05-05-09, 13:08

Looks like the problem is with the variable scope. A module level variable in the Timer module "Dim varPauseOn As Boolean" is not visible in the Form module (or any other modules) unless it is declared Public. "Public varPauseOn As Boolean".

Concerning your configuration of the timer code, this most likely will be limited and cause problems because Excel will not run 2 procedures at the same time. So if your timer attempts to fire while another macro is running it will be delayed or cause an error.

To avoid this problem you could open the timer in it's own separate Excel application instance. You can make the form appear as a separate window by minimizing the App window while the form is displayed. This however is a bit tricky to pull off.

A better option would be to write the program in a different platform so there would not be any interference with editing or running macros in Excel at the same time the Timer is running. My first choice would be AutoHotKey for this. You can check it out at AutoHotKey.com There is bit of a learning curve but you'll find lots of help, examples and sample code in the user forum plus there are many tools and very good help included with the program. Plus it is open source so it should be able to fit your budget. HotKey scripts can be compiled into an EXE file so once you've created the program it is easy to distribute it to your users or run a compiled script from a shared location.

ArielZusya · #3 (**permalink**) 05-05-09, 13:47

Aha! I knew it needed to be global just didn't know how to make that happen. Per your suggestion, I changed dim to public and everything worked! thanks so much for your help on this. As for building it in another tool--I'm sure there are a lot of better option out there. I wrote it in excel for two reasons. 1) my employer has a strict policy against installing any programs on their computers (even stuff I make myself) but is silent as to stuff written in programs they installed on the computer. 2) believe it or not, I wrote it not to be used with a more complicated application but instead just to have the timer. I work for the courts in my home state and often a judge will need to limit the amount of time a person has to make an argument but often loses track of time in the process--consequently the issue with the process being interrupted doesn't apply. At any rate--thanks so much for your help! In case anyone reads this thread and wants to play with the final product I've attached what I did (a legacy 97-2003 version and 2007 version are in the zip file). Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech