none
Help with VBA - referring to public functions and global variables RRS feed

  • Question

  • I am currently trying to add a live clock to different cells in some of my sheets. I placed this code in a module

    Global Time As String
    Global Refresh As Date
    
    Public Function CalcTime()
    
    Time = Format(Now, "hh:mm AM/PM")
    
    Call SetTime
    
    End Function
    
    Public Function SetTime()
    
    Refresh = Now + TimeValue("00:00:01")
    
    Application.OnTime Refresh, "CalcTime"
    
    End Function

    I then tried to go to Sheet3(Home) and pasted this code into it, as I am trying to put the value of the auto-refreshing string "Time" into Range("B2"). However, this did not succeed.

    Private Sub Worksheet_Activate()
    
    Range("B2").Value = Time
    
    End Sub

    Hence, I am slightly confused - how should I code it so that the string "Time" is universally accessible and referred to?

    Many thanks for helping this Excel VBA noob.

    Monday, November 11, 2013 2:29 PM

Answers

  • No. To do that:

    1) Copy the following code into a standard module:

    Private dtmNext As Date
    
    Sub StartClock()
        Range("B2").Value = Now
        dtmNext = DateAdd("n", 1, Now) ' n = minute
        Application.OnTime dtmNext, "StartClock"
    End Sub
    
    Sub StopClock()
        On Error Resume Next
        Application.OnTime dtmNext, "StartClock", , False
    End Sub

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Activate()
        StartClock
    End Sub
    
    Private Sub Worksheet_Deactivate()
        StopClock
    End Sub

    And finally, copy the following code into the ThisWorkbook module:

    Private Sub Workbook_Activate()
        StartClock
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        StopClock
    End Sub
    
    Private Sub Workbook_Deactivate()
        StopClock
    End Sub
    
    Private Sub Workbook_Open()
        StartClock
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AnsonMiu Wednesday, November 13, 2013 1:04 PM
    Monday, November 11, 2013 4:54 PM

All replies

  • You don't need the variables and functions for this purpose. Simply set the number format of cell B2 the way you want, and use

    Private Sub Worksheet_Activate()
        Range("B2").Value = Now
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 11, 2013 2:36 PM
  • But if I use this method, will I be able to achieve a "live clock" that updates, every minute per say?
    Monday, November 11, 2013 3:08 PM
  • No. To do that:

    1) Copy the following code into a standard module:

    Private dtmNext As Date
    
    Sub StartClock()
        Range("B2").Value = Now
        dtmNext = DateAdd("n", 1, Now) ' n = minute
        Application.OnTime dtmNext, "StartClock"
    End Sub
    
    Sub StopClock()
        On Error Resume Next
        Application.OnTime dtmNext, "StartClock", , False
    End Sub

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Activate()
        StartClock
    End Sub
    
    Private Sub Worksheet_Deactivate()
        StopClock
    End Sub

    And finally, copy the following code into the ThisWorkbook module:

    Private Sub Workbook_Activate()
        StartClock
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        StopClock
    End Sub
    
    Private Sub Workbook_Deactivate()
        StopClock
    End Sub
    
    Private Sub Workbook_Open()
        StartClock
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AnsonMiu Wednesday, November 13, 2013 1:04 PM
    Monday, November 11, 2013 4:54 PM