This article has 24 Comments

  1. Try resetting all the variables every hour or so. Unfortunately I am caught with other assignments at this point.

  2. Hi and thanks for another way of doing this. The previous code I used accomplished the same task using all VBA but unfortunately that had a memory leak which progressively used more and more resources until it froze.

    I thought this cell based solution would work better. Unfortunately it also freezes after it’s been running for about 4 to 8 hours depending on which machine I run it on. The charting seems to run out of resources.

    I’m using Win7 & Excel 2007.

    Anyone been able to run this continuously? Or know how to stop it freezing when running for a long time? Thanks.

  3. Thanks for the reply George..!

    I’ve found the solution for this. Without press button macro will run after open the file. for this you need add macro code in this workbook

    Private Sub Workbook_Open()
    Call StartPause
    End Sub

    the above code will call your macro. but if anyone entry any data in spreadsheet then macro will pause run. in order to fix this issue I’ve found a resolution..

    Sheet1

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
    ActiveCell.Interior.ColorIndex = 36
    End Sub

    Thisworkbook

    Private Sub Workbook_Open()
    Range(“A2”).Value = Time
    Range(“A2”).Interior.ColorIndex = xlNone
    Columns(“A:A”).AutoFit
    Reset_Clock
    End Sub

    Module1

    Sub Reset_Clock()
    Application.OnTime Now() + TimeValue(“00:00:01”), “Update_Clock”
    End Sub

    Sub Update_Clock()
    If Range(“A2”).Interior.ColorIndex = 36 Then
    Exit Sub
    End If
    Range(“A2”).Value = Range(“A2”).Value + 1 / 86400
    Reset_Clock
    End Sub

  4. You cannot do that with this model, not to my knowledge.

  5. Cool trick..
    But the problem is, macro disable if add any entry in the spreadsheet’s cell.s again want to run the start button. Could you please let me know how to fix this?

  6. when i am done i will let you know.
    best regard.

  7. If you google “excel clock VBA” you will find a free model somewhere. I try to stay away from VBA as much as I can. Besides, using the “record” macro feature you can easily figure out how to do that.

  8. Hi , I want to make an world analog clock in vba so please how to do it . I realy need it in my college project.

  9. If I could, I wouldn’t use any VBA. Thanks for commenting.

  10. And I thought that I’m an advanced VBA Dev.
    This one made me believe I’m not so advanced after all.

    Proves to show that a little VBA with very advanced Excel skills can be quite amazing.

  11. Hi George, I am not an expert but i do not think that you VBA is at basic level only…..keep up the good job and thanks again….cheers, Ash

  12. Hi Ash, unfortunately nothing that I know can be done to keep the clock running while you type something in the cell. My VBA is very basic. I am trying to keep as much of the game within cell formulas as possible. Cheers, George

  13. Hi George, I am only a noob when it comes to VBA and I was quite amazed when i discovered this website, thats really awsome. For the Analog clock, if i write anything in a cell, the clock stops! Can anything be done to rectify that please?

    Thank you
    Regards,
    Ash

  14. Thanks Ankit. stop by from time to time, and try your own ideas in excel. The least VBA or syntax the better. Experiment with concepts before reading or asking. Trust your own judgement before anyone else’s. 99% of the people are much smarter and capable than they believe. I am planning to post 20-25 times a month. Best regards, George

  15. This is really a wonderful site.
    Before this i had a very limited knowledge about the usages of excel (but having a feeling that i have good command over excel functions & calculations, like many other guys)

    & now, you have opened the horizons for me.
    It’s really awsome. I am really amazed to see all this wonderful calculations.
    This is really a great combination of engineering & excel.

    -Ankit

  16. I authorize you to post photos and information on my blog whenever referencing and linking it. I’d pleased to collaborate with you on these issues.

    I would appreciate it if you wrote a comment on my blog.

    Have a happy New Year.

  17. Hi Pedro, Very nice, I will publish the two links. Both, clock arms and hands are correct. George

  18. Hi George,

    With the help of your formula to plot the analog clocks hands, I’ve designed an spreadsheet that shows the current time into a world map with clocks for countries and cities around the world and now I’d like to share with your readers.

    http://pedrowave.blogspot.com/2011/01/how-to-dynamically-generate-gui.html
    http://pedrowave.blogspot.com/2011/01/time-zones-map-with-world-clocks.html

    Excuse my bad English. What is the correct expression? clock hands or clock arms?

  19. Hi Geo, now thanks to your tutorial, when I already know how to make an analog clock, I’m trying to make a world map with clocks in any timezone.

    When I have finished I’ll let you know.

    I see you already have some followers and you avoid spam comments.

    Best regards,
    Pedro.

Leave a Reply

Your email address will not be published. Required fields are marked *