EXCEL
 

Is it possible to set a value in a cell and then make it continuosly changing
say by adding a number every second etc.
For example, I want to start with a 1 in cell A1 and allow this to
automatically continue to change while the spreadsheet is opened.
Thanks

EXCEL
 

First install the follow macros in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
'
Range("A1").Value = Range("A1").Value + 1
'
StartTimer

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub

Then put an initial value in A1.
Then run StartTimer.

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Gary''s Student - gsnu200845


"Nad" wrote:

Is it possible to set a value in a cell and then make it continuosly changing
say by adding a number every second etc.
For example, I want to start with a 1 in cell A1 and allow this to
automatically continue to change while the spreadsheet is opened.
Thanks

EXCEL
 

Hi,

It is possible but doing this has an overhead, updating this cell every
second may slow your workbook down. Try this. Alt + F11 to open VB editor.
Right click 'ThisWorkbook' and insert module and paste in the first 3 of the
modules below. The double click 'ThisWorkbook' and paste the fourth module
in. Start proceedings off by running the module Recalc()

Change the sheet & cell where you want this to appear

Dim UpDate As Date
Sub Recalc()
Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1
Call UpDateTime
End Sub

Sub UpDateTime()
UpDate = Now + TimeValue("00:00:01")
Application.OnTime UpDate, "Recalc"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime EarliestTime:=UpDate, Procedu="Recalc", Schedule:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopClock
End Sub

Mike

"Nad" wrote:

Is it possible to set a value in a cell and then make it continuosly changing
say by adding a number every second etc.
For example, I want to start with a 1 in cell A1 and allow this to
automatically continue to change while the spreadsheet is opened.
Thanks