Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: Struggling with use of global Boolean

    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!

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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.
    Last edited by savbill; 05-05-09 at 14:12.
    ~

    Bill

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Thumbs up That did it!

    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!
    Attached Files Attached Files
    Last edited by ArielZusya; 05-05-09 at 14:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •