wilmott.com

I have some real simple sample code shown below that executes every time a user changes a cell in the worksheet (the code is a property of a single sheet). Let say I have some real-time feeds in the spreadsheet (say reuters or bloomberg), is there a worksheet event that would detect each time one of the feed values changes (not just when the user manually changes a value, but when the feed is automatically updated)?*******************************Private Sub Worksheet_Change(ByVal Target As Range) If Range("ask").Value >= Range("high").Value Then Application.EnableEvents = False Range("high").Value = Range("ask").Value Application.EnableEvents = True End If If Range("bid").Value <= Range("low").Value Then Application.EnableEvents = False Range("low").Value = Range("bid").Value Application.EnableEvents = True End IfEnd Sub*******************************In the above example, there are 4 range names, bid, ask, high, low - the sub updates the high and low based upon the current bid and ask. The check is currently performed each time the user updates a cell in the worksheet. I want it to execute each time the real time feeds update.

What method are you using to bring in the real time feed?

in this example, I am using the reuters "RTGET" function, lets say "=rtget("IDN", "JPY=", "BID")or in bloomberg, it woulc/could be a "BLP" function

i dont know if there is a way you can get this to work but even if you did I can see it becoming a mess if you have a lot of instruments in your sheet

It looks like you want to keep track of the highest and lowest values seen so far.Instead of trying to trap the change events, you can make a formula that will return the highest value seen. This way you don't break the Excel calculation model, and your recalculations are triggered perfectly by the Reuters or Bloomberg RTD updates.The trick is to compare the Caller (which has the current high-water mark) with the new value of the cell you are watching. Reading Application.Caller.Value leads to a circular reference, but you can use Application.Caller.Text instead.Try this VBA function - it's just a crude start but might give you some ideas:Function Highest(watched As Double) As Double Dim oldHighText As String Dim oldHigh As Double oldHighText = Application.caller.Text If oldHighText = "" Then oldHigh = -1000000.0 Else oldHigh = CDbl(oldHighText) End If If watched > oldHigh Then Highest = watched Else Highest = oldHigh End If End Function--Govert--Excel-Dna - Free and easy .NET for Excel

You could also tryPrivate Sub Worksheet_Calculate()or for Bloomberg, get it to return 'high' datatypes. Try 'HIGH' for same day, or maybe 'INTERVAL_HIGH' if you have a particuar time period in mind. You can use overrides to set your time period.