If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|