Worksheet change event in Excel

[rhysw]

This is a follow up post to one I had posted earlier (viewtopic.php?f=5&t=10751&p=54413&hilit=multiple+bets+being+placed#p54413), Captain Sensible got me thinking about the way BA updates Excel in each refresh. I found this old post http://www.gruss-software.co.uk/forum/viewtopic.php?t=2866 which discusses the different updated in each refresh.

My question is: if I have a cell (eg AA1) that has a formula in it that reacts to a price change, will the result of the formula be in the cell when the price update happens the (A to P column change) of could I look for a worksheet change event where the target range is equal to AA1, and react accordingly? And how are the refreshes affected if Full Stream is on?

[alrodopial]

as microsoft says:
Worksheet.Change event:
Occurs when cells on the worksheet are changed by the user or by an external link.
This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation

So with calculate event you will have your new formula result

what do you mean by "how are the refreshes affected if Full Stream is on"?
refreshes happen only when you have a price/amount change

[rhysw]

Yes that all make sense now, the calculate event isn't going to work for me as I need to calculate data from the prices. My problem is I want to monitor the price of the favourite in a market and if its price moves 2 or more ticks in 10 seconds then I want to react to it. I'm just trying to capture when it changes, but I think I can do this using VBA rather than excel formulas.

On the price refresh, if I have formulas in cells that are based on the price cells (say cell AA5 has the formula =F5/(F5-1) in it) then on the price refresh, the Target.Columns.Count = 16 one, will AA5 have the updated value in it or will there be some lag?

Oh and I have a completely unrelated question about Betfair's cross matching algo: If I place a back bet at the second best offered lay price, does it fill as much of the order as it can at the better price first? My understanding with it is it consumes the best offered prices first, and if so this would make coding the green up part of my strategy easier as I can just ask for a worse price and let Betfair's servers do the hard work of finding the best green up price for me.

[alrodopial]

On the price refresh, if I have formulas in cells that are based on the price cells (say cell AA5 has the formula =F5/(F5-1) in it) then on the price refresh, the Target.Columns.Count = 16 one, will AA5 have the updated value in it or will there be some lag?

Yes , AA5 will calculate the formula and after that, code inside worksheet change event will run

whenever you place a bet at x odds and there is an offer at better odds you get matched at these better odds