reseeding starting values

[2mc]

I use recalculation. A cell has a formula in it that references the previous value in the cell. Periodically, I want to reseed the "previous" value within that cell. Can this be done?

Thanks,
Matt

 

[Damon Ostrander]

Hi Matt,

I'm not sure whether by "recalculation" you are referring to Automatic Calculation, or an iterating circular reference, but assume it is the latter since you mentioned the cell value is a function of its previous value. I don't know of a way to set just this one cell to a previous value using worksheet functions or formulas short of using the Undo function, but of course this will reset all cell on the sheet to their previous values. However, it is fairly easy to do if you are willing to turn your formula into a user-defined function (UDF) that includes code for saving and restoring a previous value when requested by, say, running a separate macro that sets a restore flag. Are you interested in such a solution? If so, let us know what your cell function is.

 

[2mc]

Hi Damon,

Thanks for your reply. As you saw from my reworded question, I did indeed mean "iteration" instead of "recalculation." I must have had a brain "hiccup."

I have streaming data coming into Excel via a DDE link. I want to create a highest high value and a lowest low value for each minute. This is accomplished easily enough using iteration set to 1. But, when the minute is over I need the "high" cell to have a "previous" value of zero and the "low" cell to have an astronomically high number as its "previous" value. This will cause the new incoming data for the new minute to evaluate correctly for that minute.

I hope I am being clear. And, thanks again for your help.

Matt
This message was edited by 2mc on 2002-11-04 16:44

 

[Damon Ostrander]

Hi again Matt,

Yes, that makes it clear. I think.

Here is a means to do this with a reset capability using UDFs. Run the ResetHiLo macro to do the reset. Use =Hi(ref) to give the high value and =Lo(ref) to give the low value, where ref is the cell containing the DDE feed.

Dim PreviousHi
Dim PreviousLo

Sub ResetHiLo()
PreviousHi = Empty
PreviousLo = Empty
End Sub

Function Hi(X)
If IsEmpty(PreviousHi) Then
Hi = X
ElseIf X > PreviousHi Then
Hi = X
Else
Hi = PreviousHi
End If
PreviousHi = Hi
End Function

Function Lo(X)
If IsEmpty(PreviousLo) Then
Lo = X
ElseIf X < PreviousLo Then
Lo = X
Else
Lo = PreviousLo
End If
PreviousLo = Lo
End Function

If you don't know how to install a VBA macro, it's really
quite easy. Just follow these steps:

1) Go to the Visual Basic Editor (VBE). Do this from Tools >
Macro > Visual Basic Editor (or simply keyboard Alt-TMV)

2) In the VBE create a new Macro Module: Insert > Module.
An empty code window pane will appear (Alt-IM).

3) Paste the code into this window. The macro or function is
now available for use from Excel. If it is a Function type
macro you can immediately use it as an Excel function. If
it is a Sub (subroutine) type macro you can run it from
the Excel Tools > Macro menu (Alt-TMM).

 

[2mc]

Damon,

Wow! And, wow!! Ok, one more time, wow!

I think that will do the trick. I so very much appreciate it.

I have another related question. Perhaps you may know the answer.

In the scenario I mentioned in the previous posts, I will be receiving streaming data. Accompanying the data is a time stamp.

I would like to keep a historical record of the highs and lows for each minute. I would like it in descending chronological order. I think I have it worked out with one exception.

When the time stamp of the incoming data changes to another minute, I would like a macro to run that copies the range down 1 row, copies the just built highs and lows to the "first" row of the historical record, and then to perform the code you wrote that clears the "PreviousHi" and "PreviousLo."

My question is this: when the macro fires, will the DDE stream stop, enabling the macros to perform the copying and clearing functions before any more data comes in?

Thanks again.

Matt

 

[Damon Ostrander]

Hi again Matt,

It would not be difficult to add a history of previous hi/lo values and "bump" them down as you describe. This would, of course, be best done with a macro, and I would recommend triggering the macro with the worksheet's Calculate event, since this event is triggered each time a new stock value comes in via DDE. Screen updating (Application.ScreenUpdating) should be turned off while the macro runs, and back on afterwards so that the updates can be done all at once when the macro completes--otherwise the run speed is likely to be significantly impacted.

Regarding your question about DDE updating interfering with the macro (or vice-versa), this depends on how often new data arrive. Unless the updating is occurring more often that once per second I shouldn't think it would be a problem. I would expect the hi/lo history macro to require only a small fraction of a second.

If you would like to describe the structure of your worksheet (e.g., what cells contain the DDE feed, what cells contain the Hi/Lo calls, and what ranges of cells should contain the time/hi/lo value history) I could perhaps provide some specific code. Also, do you want the history cleared when you reset the hi/lo values? I would think it might be convenient in the hi/lo history table to just have one table of date-time vs. hi/lo value with, for example, hi values indicated by blue text and lo values indicated by red text. That way it would not be necessary to have two separate tables.

Damon

 

[2mc]

Damon,

I'm having problems getting this to work. I pasted the code in VBA as you directed. I use the HI() and LO() UDFs in the cells as directed.

The only thing I did on my own was try to write a macro that triggers the ResetHiLo() macro you wrote.

I have iteration set to 1. I then tried to write a macro to trigger whenever the cell with the time changed to a different minute. (The DDE trade time only has hours and minutes - it does not have seconds. So, it only changes once every minute.)

[Note: G2 holds the "trade time" DDE link]

I wrote:

if G2<> G2
ResetHiLo()
end if

It doesn't work. How can I get the Hi and Lo to reset to "empty" at the beginning of each minute?

Many thanks for your help.

Matt
This message was edited by 2mc on 2002-11-12 17:00