Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-22
16
Medium Priority
?
538 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 1000 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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

971 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