I have a worksheet that calculates some astronomical positions according to
the current time of day. Of course as time progresses, the numbers change. I
found a few websites that provide a copy and paste routine in VB format but
I can't seem to get them to update the time every minute without manually
doing a refresh. It's only one cell that needs updating every minute and the
rest of the calculations should automatically follow from that. I am using
Excel 2002. I know some programming languages but VB isn't one of them.
Also, if anyone can point me to a small subroutine to do this, I'd be
appreciative.

Thanks for your reply.
--
David Farber
L.A., CA

Try this:

Sub test()
First:
Application.Wait (Now() + TimeSerial(0, 1, 0))
Application.Calculate
GoTo First
End Sub

The only issue is that it pauses all execution - you can't even enter data
on a worksheet during macro run.
--
Conrad S
#Don't forget to click "Yes" if this post was helpful!#


"David Farber" wrote:

I have a worksheet that calculates some astronomical positions according to
the current time of day. Of course as time progresses, the numbers change. I
found a few websites that provide a copy and paste routine in VB format but
I can't seem to get them to update the time every minute without manually
doing a refresh. It's only one cell that needs updating every minute and the
rest of the calculations should automatically follow from that. I am using
Excel 2002. I know some programming languages but VB isn't one of them.
Also, if anyone can point me to a small subroutine to do this, I'd be
appreciative.

Thanks for your reply.
--
David Farber
L.A., CA

Try this in a Normal module

Private mdtNextUpdate As Date ' at top of module

Sub auto_Open()
UpdateNow
End Sub

Sub Auto_Close()
StopUpdate
End Sub

Sub StopUpdate()
Application.OnTime mdtNextUpdate, "UpdateNow", Schedule:=False
End Sub

Sub UpdateNow()

ThisWorkbook.Worksheets("Sheet1").Range("A1").Calc ulate

mdtNextUpdate = Now + TimeSerial(0, 1, 0)
Application.OnTime mdtNextUpdate, "UpdateNow"

End Sub

Run UpdateNow to get things going. The auto_Open/Close routines should
start/stop the timer when the workbook opens/closes.

Obviously change the sheet name and cell address as required. Might be an
idea to Name the cell instead of using its address in case it gets moved.

Regards,
Peter T

"David Farber" wrote in message
...
I have a worksheet that calculates some astronomical positions according to
the current time of day. Of course as time progresses, the numbers change.
I found a few websites that provide a copy and paste routine in VB format
but I can't seem to get them to update the time every minute without
manually doing a refresh. It's only one cell that needs updating every
minute and the rest of the calculations should automatically follow from
that. I am using Excel 2002. I know some programming languages but VB isn't
one of them. Also, if anyone can point me to a small subroutine to do this,
I'd be appreciative.

Thanks for your reply.
--
David Farber
L.A., CA

I guess the issue here will be whether or not you need to work on the
computer whilst the procedure is running. There are a few ways to do what
you want providing you are happy to let machine run the procedure.

In the ThisWorkBook Open module you can call the following procedure. I
have included a counter which you can configure - here it set to 5 minutes.

Sub getTime()

Dim Rng As Range
Dim count As Integer

Set Rng = ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)

count = 0
Do
If Second(Now) = 0 Then
Rng.Value = Format(Now, "hh:mm:ss")
count = count + 1
MsgBox count
End If
Loop Until count = 5
End Sub

"David Farber" wrote in message
...
I have a worksheet that calculates some astronomical positions according
to the current time of day. Of course as time progresses, the numbers
change. I found a few websites that provide a copy and paste routine in VB
format but I can't seem to get them to update the time every minute
without manually doing a refresh. It's only one cell that needs updating
every minute and the rest of the calculations should automatically follow
from that. I am using Excel 2002. I know some programming languages but VB
isn't one of them. Also, if anyone can point me to a small subroutine to
do this, I'd be appreciative.

Thanks for your reply.
--
David Farber
L.A., CA

David,
I use the code below to update my worksheet every 2 seconds. It
has a start and a stop button and I have a cell change color to let me know
if it is running or not. You just need to create 2 buttons and associate the
start and stop macros.

I have this in a general module:

Dim KeepRefreshing As Boolean
Sub StopRefresh()
Range("A2").Select
With Selection.Interior
.ColorIndex = 3 'Red
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E2").Select 'Parks the cursor out of the way
KeepRefreshing = False
End Sub
Sub AutomaticRefresh()
If KeepRefreshing Then
Application.OnTime Now + TimeValue("00:00:02"), "RefreshThisSheet"
End If
End Sub
Sub StartAutoRefresh()
Range("A2").Select
With Selection.Interior
.ColorIndex = 50 'Green
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E2").Select 'Parks the cursor
KeepRefreshing = True
RefreshThisSheet
End Sub
Sub RefreshThisSheet()
' This function refreshes the sheet, and then calls the
AutomaticRefresh
' function, which will call it back in a couple of seconds
Application.ScreenUpdating = False
Application.CalculateFull
AutomaticRefresh
End Sub

"David Farber" wrote:

I have a worksheet that calculates some astronomical positions according to
the current time of day. Of course as time progresses, the numbers change. I
found a few websites that provide a copy and paste routine in VB format but
I can't seem to get them to update the time every minute without manually
doing a refresh. It's only one cell that needs updating every minute and the
rest of the calculations should automatically follow from that. I am using
Excel 2002. I know some programming languages but VB isn't one of them.
Also, if anyone can point me to a small subroutine to do this, I'd be
appreciative.

Thanks for your reply.
--
David Farber
L.A., CA

"David Farber" wrote:

I have a worksheet that calculates some astronomical positions according
to
the current time of day. Of course as time progresses, the numbers
change. I
found a few websites that provide a copy and paste routine in VB format
but
I can't seem to get them to update the time every minute without manually
doing a refresh. It's only one cell that needs updating every minute and
the
rest of the calculations should automatically follow from that. I am
using
Excel 2002. I know some programming languages but VB isn't one of them.
Also, if anyone can point me to a small subroutine to do this, I'd be
appreciative.

Thanks for your reply.
--
David Farber
L.A., CA




"Conrad S" wrote in message
...
Try this:

Sub test()
First:
Application.Wait (Now() + TimeSerial(0, 1, 0))
Application.Calculate
GoTo First
End Sub

The only issue is that it pauses all execution - you can't even enter data
on a worksheet during macro run.
--
Conrad S
#Don't forget to click "Yes" if this post was helpful!#



I copy and pasted the lines beginning at Sub test() to End Sub into the
Visual Basic "ThisWorkbook (code)" sheet. Then I ran the macro (F5) and
Excel hung. I could not get back to the worksheet. I had to ctrl-alt-del and
end task on the VB page to restore operation. It would not even let me do an
end task on the worksheet. A message came up that said that Excel could not
close now. Never saw a message like that before.

Please remember I am not experienced in this type of programming so I may be
overlooking something very obvious to an experienced user.

Thanks for your reply.

David Farber
L.A., CA

"David Farber" wrote in message
...
I have a worksheet that calculates some astronomical positions according
to the current time of day. Of course as time progresses, the numbers
change. I found a few websites that provide a copy and paste routine in VB
format but I can't seem to get them to update the time every minute
without manually doing a refresh. It's only one cell that needs updating
every minute and the rest of the calculations should automatically follow
from that. I am using Excel 2002. I know some programming languages but VB
isn't one of them. Also, if anyone can point me to a small subroutine to
do this, I'd be appreciative.

Thanks for your reply.
--
David Farber
L.A., CA




"Peter T" <peter_t@discussions wrote in message
...
Try this in a Normal module

Private mdtNextUpdate As Date ' at top of module

Sub auto_Open()
UpdateNow
End Sub

Sub Auto_Close()
StopUpdate
End Sub

Sub StopUpdate()
Application.OnTime mdtNextUpdate, "UpdateNow", Schedule:=False
End Sub

Sub UpdateNow()

ThisWorkbook.Worksheets("Sheet1").Range("A1").Calc ulate

mdtNextUpdate = Now + TimeSerial(0, 1, 0)
Application.OnTime mdtNextUpdate, "UpdateNow"

End Sub

Run UpdateNow to get things going. The auto_Open/Close routines should
start/stop the timer when the workbook opens/closes.

Obviously change the sheet name and cell address as required. Might be an
idea to Name the cell instead of using its address in case it gets moved.

Regards,
Peter T


I copy and pasted the code from Private mtdNextUPdate .... to End Sub. I ran
the macro and it seemed to work on the first try. Then a minute later an
error popped up, "The macro \Path to my worksheet\filename "!UpdateNow'
cannot be found." (where path to my worksheet\filename is my abbreviation
for the where the file on my pc is located.)

Thanks for your reply.

David Farber
L.A., CA

"David Farber" wrote in message
...
I have a worksheet that calculates some astronomical positions according
to the current time of day. Of course as time progresses, the numbers
change. I found a few websites that provide a copy and paste routine in
VB format but I can't seem to get them to update the time every minute
without manually doing a refresh. It's only one cell that needs updating
every minute and the rest of the calculations should automatically follow
from that. I am using Excel 2002. I know some programming languages but
VB isn't one of them. Also, if anyone can point me to a small subroutine
to do this, I'd be appreciative.

Thanks for your reply.
--
David Farber
L.A., CA


"Danny Boy" <d_a_n_n_y_r_a_t_h_o_t_m_a_i_l_d_o_t_c_o_u_k wrote in message
...
I guess the issue here will be whether or not you need to work on the
computer whilst the procedure is running. There are a few ways to do what
you want providing you are happy to let machine run the procedure.

In the ThisWorkBook Open module you can call the following procedure. I
have included a counter which you can configure - here it set to 5
minutes.

Sub getTime()

Dim Rng As Range
Dim count As Integer

Set Rng = ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)

count = 0
Do
If Second(Now) = 0 Then
Rng.Value = Format(Now, "hh:mm:ss")
count = count + 1
MsgBox count
End If
Loop Until count = 5
End Sub


Yes, I definitely want to be able to use the machine while this is running.
So if this procedure prohibits that, I will not be able to implement it.

Thanks for your reply.

David Farber
L.A., CA

"Mike K" wrote in message
...
David,
I use the code below to update my worksheet every 2 seconds.
It
has a start and a stop button and I have a cell change color to let me
know
if it is running or not. You just need to create 2 buttons and associate
the
start and stop macros.

I would need some directions on how to do this.


I have this in a general module:

Don't know what a general module is.


Dim KeepRefreshing As Boolean
Sub StopRefresh()
Range("A2").Select
With Selection.Interior
.ColorIndex = 3 'Red
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E2").Select 'Parks the cursor out of the way
KeepRefreshing = False
End Sub
Sub AutomaticRefresh()
If KeepRefreshing Then
Application.OnTime Now + TimeValue("00:00:02"), "RefreshThisSheet"
End If
End Sub
Sub StartAutoRefresh()
Range("A2").Select
With Selection.Interior
.ColorIndex = 50 'Green
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E2").Select 'Parks the cursor
KeepRefreshing = True
RefreshThisSheet
End Sub
Sub RefreshThisSheet()
' This function refreshes the sheet, and then calls the
AutomaticRefresh
' function, which will call it back in a couple of seconds
Application.ScreenUpdating = False
Application.CalculateFull
AutomaticRefresh
End Sub

"David Farber" wrote:

I have a worksheet that calculates some astronomical positions according
to
the current time of day. Of course as time progresses, the numbers
change. I
found a few websites that provide a copy and paste routine in VB format
but
I can't seem to get them to update the time every minute without manually
doing a refresh. It's only one cell that needs updating every minute and
the
rest of the calculations should automatically follow from that. I am
using
Excel 2002. I know some programming languages but VB isn't one of them.
Also, if anyone can point me to a small subroutine to do this, I'd be
appreciative.

Thanks for your reply.
--
David Farber
L.A., CA




Mike,

The code you have listed seems customized to do something which will
probably be of great value to me. As I said, I have no programming
experience to speak of in Visual Basic. So you would probably have to add a
few intermediate instructions so I know exactly where to copy and paste this
code, how to get the code to run, and other minor details like where to
enter the cell number that needs to be updated and where to enter the
worksheet name.

Thanks for your reply.

David Farber
L.A., CA

Ensure all the code goes into a "normal" module.

Find and select your project in the top left panel
Rt-click / Insert / Module
Paste all the code I posted, that's the top declaration line and all four
procedures.
Change the address as required
Run UpdateNow

Ensure you only have one routine named UpdateNow in any open project. If you
might want to use the same macro name in multiple projects amemd add this
line

Application.OnTime mdtNextUpdate, ThisWorkbook.Name & "!UpdateNow"

there's an ! exclamation if you prefix with the workbook name

The code should work perfectly for your needs as you described them

Regards,
Peter T

"David Farber" wrote in message
...
"David Farber" wrote in message
...
I have a worksheet that calculates some astronomical positions according
to the current time of day. Of course as time progresses, the numbers
change. I found a few websites that provide a copy and paste routine in
VB format but I can't seem to get them to update the time every minute
without manually doing a refresh. It's only one cell that needs updating
every minute and the rest of the calculations should automatically follow
from that. I am using Excel 2002. I know some programming languages but
VB isn't one of them. Also, if anyone can point me to a small subroutine
to do this, I'd be appreciative.

Thanks for your reply.
--
David Farber
L.A., CA




"Peter T" <peter_t@discussions wrote in message
...
Try this in a Normal module

Private mdtNextUpdate As Date ' at top of module

Sub auto_Open()
UpdateNow
End Sub

Sub Auto_Close()
StopUpdate
End Sub

Sub StopUpdate()
Application.OnTime mdtNextUpdate, "UpdateNow", Schedule:=False
End Sub

Sub UpdateNow()

ThisWorkbook.Worksheets("Sheet1").Range("A1").Calc ulate

mdtNextUpdate = Now + TimeSerial(0, 1, 0)
Application.OnTime mdtNextUpdate, "UpdateNow"

End Sub

Run UpdateNow to get things going. The auto_Open/Close routines should
start/stop the timer when the workbook opens/closes.

Obviously change the sheet name and cell address as required. Might be an
idea to Name the cell instead of using its address in case it gets moved.

Regards,
Peter T


I copy and pasted the code from Private mtdNextUPdate .... to End Sub. I
ran the macro and it seemed to work on the first try. Then a minute later
an error popped up, "The macro \Path to my worksheet\filename "!UpdateNow'
cannot be found." (where path to my worksheet\filename is my abbreviation
for the where the file on my pc is located.)

Thanks for your reply.

David Farber
L.A., CA

"David Farber" wrote in message
...
I have a worksheet that calculates some astronomical positions according
to the current time of day. Of course as time progresses, the numbers
change. I found a few websites that provide a copy and paste routine in
VB format but I can't seem to get them to update the time every minute
without manually doing a refresh. It's only one cell that needs updating
every minute and the rest of the calculations should automatically
follow from that. I am using Excel 2002. I know some programming
languages but VB isn't one of them. Also, if anyone can point me to a
small subroutine to do this, I'd be appreciative.

Thanks for your reply.
--
David Farber
L.A., CA




"Peter T" <peter_t@discussions wrote in message
...
Try this in a Normal module

Private mdtNextUpdate As Date ' at top of module

Sub auto_Open()
UpdateNow
End Sub

Sub Auto_Close()
StopUpdate
End Sub

Sub StopUpdate()
Application.OnTime mdtNextUpdate, "UpdateNow", Schedule:=False
End Sub

Sub UpdateNow()

ThisWorkbook.Worksheets("Sheet1").Range("A1").Calc ulate

mdtNextUpdate = Now + TimeSerial(0, 1, 0)
Application.OnTime mdtNextUpdate, "UpdateNow"

End Sub

Run UpdateNow to get things going. The auto_Open/Close routines should
start/stop the timer when the workbook opens/closes.

Obviously change the sheet name and cell address as required. Might be
an idea to Name the cell instead of using its address in case it gets
moved.

Regards,
Peter T


I copy and pasted the code from Private mtdNextUPdate .... to End Sub. I
ran the macro and it seemed to work on the first try. Then a minute later
an error popped up, "The macro \Path to my worksheet\filename
"!UpdateNow' cannot be found." (where path to my worksheet\filename is my
abbreviation for the where the file on my pc is located.)

Thanks for your reply.

David Farber
L.A., CA




"Peter T" <peter_t@discussions wrote in message
...
Ensure all the code goes into a "normal" module.

Find and select your project in the top left panel
Rt-click / Insert / Module
Paste all the code I posted, that's the top declaration line and all four
procedures.
Change the address as required
Run UpdateNow

Ensure you only have one routine named UpdateNow in any open project. If
you might want to use the same macro name in multiple projects amemd add
this line

Application.OnTime mdtNextUpdate, ThisWorkbook.Name & "!UpdateNow"

there's an ! exclamation if you prefix with the workbook name

The code should work perfectly for your needs as you described them

Regards,
Peter T


Peter,

That worked out perfectly! Could you please refer me to a beginning
reference manual where I can learn to write clever things like that?

Thanks for your reply.

--
David Farber
L.A., CA

A bit more about OnTime

http://www.cpearson.com/excel/OnTime.aspx

Some suggested learning resources

http://groups.google.co.uk/group/mic...t=0&scoring=d&

Regards,
Peter T


"David Farber" wrote in message
news:YULel.104738 Peter,

That worked out perfectly! Could you please refer me to a beginning
reference manual where I can learn to write clever things like that?

Thanks for your reply.

--
David Farber
L.A., CA