lazycreeks
Technical User
Abstract: DDE incoming (stock)Time & Price data updates my Excel 2000 spreadsheet up to twice per second, per stock. 7 or 8 stocks take 14 to 15
columns. 2000 trades can occur in 5 min., but using 7 or 8 two-col. arrays, each 2000 rows deep consumes over 3 megs filesize. Instead of 15 x 2000, or 30k array formulae, I want to use but two per stock - one for trade time, and the other for the corresponding trade price to drive the worksheet chart. My macro Sub copies each new DDE trade time and price (Values)inY8:Z8 down 1 row (to Y9:Z9),copies the same values to the row above the incoming DDE data (Y7:Z7), then calls the insert function to Y9:Z9 (the cells just below the DDE data where the newest DDE data has just been copied), and lastly deletes the 2 cells, 2000 rows below. Insert pushes the whole col. down, ready for the next iteration. Neat, eh? Not quite...
PROBLEM: Macro code exits the Sub if the DDE time (Y8)
displaying is the same as that copied into the cell above (Y7)on the last iteration, and this works flawlessly manually calling that macro. But using Private Sub Worksheet_Calculate() entered into the worksheet to trigger the macro always produces an endless loop after the first iteration - probably because the Worksheet_Calculate calls the macro code, containing 2 copy commands triggering recalculation twice before the macro has completed 1 iteration. Then the whole screen just flutters in endless looping between the Worksheet_Calculate call and the macro code that exits when it finds the two times now equal.
How can I get incoming DDE data to drive the macro only when a new DDE trade time replaces the previous trade time? I'm at my wit's end on this one.
Thnx in advance for the solution to my problem.
columns. 2000 trades can occur in 5 min., but using 7 or 8 two-col. arrays, each 2000 rows deep consumes over 3 megs filesize. Instead of 15 x 2000, or 30k array formulae, I want to use but two per stock - one for trade time, and the other for the corresponding trade price to drive the worksheet chart. My macro Sub copies each new DDE trade time and price (Values)inY8:Z8 down 1 row (to Y9:Z9),copies the same values to the row above the incoming DDE data (Y7:Z7), then calls the insert function to Y9:Z9 (the cells just below the DDE data where the newest DDE data has just been copied), and lastly deletes the 2 cells, 2000 rows below. Insert pushes the whole col. down, ready for the next iteration. Neat, eh? Not quite...
PROBLEM: Macro code exits the Sub if the DDE time (Y8)
displaying is the same as that copied into the cell above (Y7)on the last iteration, and this works flawlessly manually calling that macro. But using Private Sub Worksheet_Calculate() entered into the worksheet to trigger the macro always produces an endless loop after the first iteration - probably because the Worksheet_Calculate calls the macro code, containing 2 copy commands triggering recalculation twice before the macro has completed 1 iteration. Then the whole screen just flutters in endless looping between the Worksheet_Calculate call and the macro code that exits when it finds the two times now equal.
How can I get incoming DDE data to drive the macro only when a new DDE trade time replaces the previous trade time? I'm at my wit's end on this one.
Thnx in advance for the solution to my problem.
![[flowerface]](/data/assets/smilies/flowerface.gif "[flowerface] [flowerface]")
