asked on

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
https://www.experts-exchange.com/questions/26629410/Help-with-installing-VBA-code-in-Excel-please-already-have-the-code.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

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbanik

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.

gbanik

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

Steve_Brady

ASKER

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.?

Steve_Brady

ASKER

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

cyberkiwi

3rd one.
Quote the names because they are strings

SOLUTION

gbanik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve_Brady

ASKER

...one more (in 60 seconds or less):

What's the difference between dots (periods), commas, and plus or minus signs in VBA code?

gbanik

cyberkiwi ...i think it wouldnt work without the file extension.

gbanik

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

Steve_Brady

ASKER

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

gbanik

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 ;)

Rory Archibald

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.

gbanik

@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 ;)

Rory Archibald

I was addressing the OP's comment about parentheses for formulas, not VBA. :)

Steve_Brady

ASKER

multiple good responses