Solved

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

Posted on 2010-11-22
16
489 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Steve_Brady
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34191995
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
 
LVL 13

Expert Comment

by:gbanik
ID: 34192081
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
 
LVL 13

Expert Comment

by:gbanik
ID: 34192094
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
 

Author Comment

by:Steve_Brady
ID: 34192435
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
 

Author Comment

by:Steve_Brady
ID: 34192488
>>>>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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192491
3rd one.
Quote the names because they are strings
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 250 total points
ID: 34192501
application.workbooks("xyz.xlsm").Worksheets("Data A").Range("A1").Value = Now()
0
 

Author Comment

by:Steve_Brady
ID: 34192515
...one more (in 60 seconds or less):

What's the difference between dots (periods), commas, and plus or minus signs in VBA code?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:gbanik
ID: 34192518
cyberkiwi ...i think it wouldnt work without the file extension.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34192611
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
 

Author Comment

by:Steve_Brady
ID: 34192890
>>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
 
LVL 13

Expert Comment

by:gbanik
ID: 34193113
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34195475
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
 
LVL 13

Expert Comment

by:gbanik
ID: 34195606
@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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34195826
I was addressing the OP's comment about parentheses for formulas, not VBA. :)
0
 

Author Closing Comment

by:Steve_Brady
ID: 34208886
multiple good responses
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now