Update Excel VBA-based time display even when workbook is inactive

Help with installing VBA code in Excel please (already have the code)
11/20/10 09:20 PM, ID: 26629410
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26629410.html

Hello,

This is a new question but relates to the above thread (which may or may not be needed).  It refers to Excel (2007) VBA code that creates a time-of-day display in cell A1 which updates every minute automatically (it's essentially a clock).

Part of the code is in the Workbook folder:
 
Private Sub Workbook_Open()
DoTick
End Sub

Open in new window

and part is in a module:
 
Option Explicit

Public Sub DoTick()
Application.Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:01"), "DoTick"
End Sub

Open in new window


The clock works well as long as the workbook in which it is located is the active window.  If another application or even a different instance of Excel is activated, the clock stops updating.  I suppose that would be OK if when it is reactivated, it would immediately update.  However, it does not.  Moreover, even pressing the re-calculate button does not do it.  The only way I can find to get it updated is to click on cell A1 and press Enter.

Questions:

1)  Is there any way to modify the code so it will keep running (updating) even when behind other windows (i.e. when it is inactive)?

2)  If not, is there a way get it to update immediately whenever that workbook is re-activated?

Thanks
Steve_BradyAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
It seems to keep working for me.. but I would use

Public Sub DoTick()
Worksheets(1).Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:00:01"), "DoTick"
End Sub

Instead of Application, which could go to any sheet...
0
 
gbanikCommented:
I would go one step ahead...

Public Sub DoTick()
Worksheets("WorksheetName").Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:00:01"), "DoTick"
End Sub

That way I am assured of the code running in the background whatever be the order of the opening of worksheets.
0
 
gbanikCommented:
Sorry I meant actually meant Workbook ... so....

Public Sub DoTick()
application.workbooks("WorkbookName").Worksheets(1).Range("A1").Value = Now()
Application.OnTime Now() + TimeValue("00:00:01"), "DoTick"
End Sub
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Steve_BradyAuthor Commented:
Thanks for the responses.

>>application.workbooks("WorkbookName").Worksheets(1).Range("A1").Value = Now()

So for this line, if I have a file called xyz.xlsm with a sheet tab titled Data A, would it look like this:

    application.workbooks("xyz").Worksheets(Data A).Range("A1").Value = Now()

or this:

    application.workbooks(xyz).Worksheets(Data A).Range("A1").Value = Now()

or this:

    application.workbooks("xyz").Worksheets("Data A").Range("A1").Value = Now()

or none of the above?

i.e. are parens, quotes, etc. critical and when do you use them?  Also, are caps (case sensitivity) important to use/not use, etc.?


0
 
Steve_BradyAuthor Commented:
>>>>application.workbooks("WorkbookName").Worksheets(1).Range("A1").Value = Now()

Oh, and what if I want it to show up on more than one spreadsheet, but not all Excel files (i.e. how do you include multiple something)?  

e.g. xyz.xlsm, sheets: Data A, Data B but not Data C

or

xyz.xlsm, sheet:  Data A and 123.xlsm, Sheet 1

(I just want to start getting this VBA syntax figured out.)

Thanks
0
 
cyberkiwiCommented:
3rd one.
Quote the names because they are strings
0
 
gbanikConnect With a Mentor Commented:
application.workbooks("xyz.xlsm").Worksheets("Data A").Range("A1").Value = Now()
0
 
Steve_BradyAuthor Commented:
...one more (in 60 seconds or less):

What's the difference between dots (periods), commas, and plus or minus signs in VBA code?
0
 
gbanikCommented:
cyberkiwi ...i think it wouldnt work without the file extension.
0
 
gbanikCommented:
I am not able to find the context of your question. They are all different characters used for disjoint purposes.
Like "+" can be used for addition, but can be used for concatenation also.
"." is a separator used to connect a method/property to its object
"," is normally used to separate parameters of a function/subroutine
"-" is used for subtraction only ... i think.

But again... whats the context???
0
 
Steve_BradyAuthor Commented:
>>But again... whats the context???

I'm pretty comfortable with parens and commas in Excel formulas for example.  And +-*/ are always the four math fxns, and & and "" are pretty straight forward.  Sometimes parens are empty like =NOW() for example which I don't get but most other stuff is pretty intuitive after looking at lots of formulas.

So just want to start getting similar understanding of VBA.  For example, periods always seem to be present, e.g.

    application.workbooks("xyz.xlsm").Worksheets("Data A").Range("A1").Value = Now()

Here the periods seem to enable you to drill down, so to speak.  Is that kind of what they always do?

Also, I started reading a bit about expressions.  That's what the word "application" is right, but what does it mean?  I don't think it's the normal meaning I'm used to (i.e. software program).  

Pretty much just a few random questions.  Not a big deal tho.

Thanks for the help.
0
 
gbanikCommented:
First... the complete VBA is around the Office Object Model, that is, Word, Excel, Powerpoint, etc... consider these as the "Application" objects. Technically speaking it is the
Excel.Application
object

VBA is very tolerant, hence when we write "Application" it interprets it as "Excel.Application" (or for that matter "Word.Application" depending on the context)

Still with me? ok.

According to this object model, everything u see in Excel, be it a Workbook, worksheet, cell, column, row, hyperlink, chart, etc etc etc are objects. These objects are hierarchically linked with each other..... like Application.Workbooks
It means the "Application" object holds the collection of Workbook objects.

There with me? fine.

Now how do you get a workbook out of this Workbooks collection? We "pull" it using the Index like
application.workbooks(1)  ... means we are requesting for the 1st workbook object from the Workbooks collection which is lying on the Application object. Similarly, we can ask for say the worksheet object "Sheet1" from the Worksheets collection
Application.Workbooks(1).Worksheets("Sheet1")
Notice that the "index" can be a number or a name.

If you have read till here, u r ready to explore some good articles, not like this one ;)
0
 
Rory ArchibaldCommented:
Not really on point, but I thought I'd address your comment:
"Sometimes parens are empty like =NOW() for example which I don't get"

If you use a function in a worksheet cell, you have to use parentheses, even if there are no arguments, otherwise Excel will think it is a defined name.
0
 
gbanikCommented:
@rorya, :) I just tried to give a start.

As far as that question is concerned why parenthesis is used in some cases and sometimes u dont, is still a mystery to me right from the early VB days :) ..... nor do I want to know :) ...  What I know for sure is that VB and VBA are very forgiving and everything works ;)
0
 
Rory ArchibaldCommented:
I was addressing the OP's comment about parentheses for formulas, not VBA. :)
0
 
Steve_BradyAuthor Commented:
multiple good responses
0
All Courses

From novice to tech pro — start learning today.