Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a function (not macro with ontime) to record a snapshot of a changing cell
Cell A1 = current time
Cell A2 = time at which I would like a snapshot Cell A3 = a constantly changing value (like a volatile rand() or a stock price during the day) Cell A4 = once the snapshot time is reached, a non changing value equal to the value of A3 at the snapshot time I wrote a funtion that worked, but only by using module level variables. Because this function is called fom many cells, each cell needed its own variables. This was impractical. Would have needed 2,000 seperate functions. All other functions i have written with if-then statements return 0 after the current time exceeds the snapshot time. I know there is probably a simple solution, but my brain is currently in manual-only calculation mode. Thanks for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a function (not macro with ontime) to record a snapshot of a changing cell
Shannon,
You can want a function, but you won't get one. You need to use macros with ontime to force constant recalculation or you won't have any luck with this. To help you change your mind, I promise that the macro will work will all your cells, and not require that each cell have its own variables. Waiting for your decision, Bernie MS Excel MVP "Shannon" wrote in message om... Cell A1 = current time Cell A2 = time at which I would like a snapshot Cell A3 = a constantly changing value (like a volatile rand() or a stock price during the day) Cell A4 = once the snapshot time is reached, a non changing value equal to the value of A3 at the snapshot time I wrote a funtion that worked, but only by using module level variables. Because this function is called fom many cells, each cell needed its own variables. This was impractical. Would have needed 2,000 seperate functions. All other functions i have written with if-then statements return 0 after the current time exceeds the snapshot time. I know there is probably a simple solution, but my brain is currently in manual-only calculation mode. Thanks for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a function (not macro with ontime) to record a snapshot of a changing cell
Bernie,
Thanks for the reply. Last night I created a function with an module-level array variable (500,6). The funtion dumps a new value to the array if the time is less than or equal to the target snapshot time, else leaves the array alone. Then the funtion returns whatever is in the array. This works, but is pretty slow and is taking up all my cpu processing power. I guess because the array is updating every time one of the 500x6 values changes (about every second), even after the target time has been passed. I'm game to try your macro. Please let me know the next step. Thanks. Shannon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a function (not macro with ontime) to record a snapshot of a changing cell
Shannon,
Assumptions: The time to capture in entered in column B (entered as time only), the value to capture is in column C, and the captured value should be placed in column D (which will be empty until the captured value is place in it). All cells are on the same row, all on just one worksheet, the active worksheet. Copy the code below into a module. Run "StartIt" to start, "StopIt" to stop. Assign the macros to buttons or object for ease of starting and stopping. HTH, Bernie MS Excel MVP Option Explicit Dim NextTime As Date Dim Running As Boolean Sub StartIt() Update End Sub Sub Update() Dim myCell As Range Running = True 'Check every second NextTime = Time + TimeValue("00:00:01") Application.OnTime NextTime, "Update" For Each myCell In Intersect(Range("B:B"), ActiveSheet.UsedRange) If myCell.Value < Time And myCell(1, 3).Value = "" Then myCell(1, 3).Value = myCell(1, 2).Value End If Next myCell End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Shannon" wrote in message om... Bernie, Thanks for the reply. Last night I created a function with an module-level array variable (500,6). The funtion dumps a new value to the array if the time is less than or equal to the target snapshot time, else leaves the array alone. Then the funtion returns whatever is in the array. This works, but is pretty slow and is taking up all my cpu processing power. I guess because the array is updating every time one of the 500x6 values changes (about every second), even after the target time has been passed. I'm game to try your macro. Please let me know the next step. Thanks. Shannon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
Snapshot function, 2003 to 2007 | Excel Worksheet Functions | |||
Record changing cell data into a column or range | Excel Worksheet Functions | |||
how do i record changing cell data (cell is dde linked) | Excel Discussion (Misc queries) | |||
Viewing Snapshot Files with Snapshot Viewer Control on Excel Userform...How? | Excel Programming |