EXCEL

N

Nad

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
 
G

Gary''s Student

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, procedure:=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, _
procedure:=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
 
M

Mike H

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, Procedure:="Recalc", Schedule:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopClock
End Sub

Mike
 
Top