Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause/re-run macro to enable DDE updating
hello, please check the macro i've writen:
Sub Macro1() Dim i As Integer For i = 1 To 100000 If Worksheets("sheet1").Range("PXLAST").Value = Worksheets("sheet1").Range("init").Offset(i, 0).Value Then Else Worksheets("sheet1").Range("PXLAST").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next End Sub *on the PXLAST cell i have a formula that gets updated via a DDE link: =blp("ukx index", "last price") *this returns the real time price of FTSE100 my macro is supposed to copy this value down 100000 times or so whenever there is a change from prev value. *however i can't get the PXLAST cell to get updated while the macro is running, so i wanted to introduce a pause in the programming that would allow the DDE to update before having the macro running again. *my code is also no working properly because the macro will execute the ELSE even if the IF clause is verified. as at now, my macro is copying down 6527.6 all the time. can you help me out with code suggestions? much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause/re-run macro to enable DDE updating
I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change
events, if you have a cell dependant on that cells value, the Calculate event will fire. Run your code there. Not sure about the 100000 rows, unless you have XL2007, but then <apparently calculation time will be long. NickHK "ALVESM" wrote in message ... hello, please check the macro i've writen: Sub Macro1() Dim i As Integer For i = 1 To 100000 If Worksheets("sheet1").Range("PXLAST").Value = Worksheets("sheet1").Range("init").Offset(i, 0).Value Then Else Worksheets("sheet1").Range("PXLAST").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next End Sub *on the PXLAST cell i have a formula that gets updated via a DDE link: =blp("ukx index", "last price") *this returns the real time price of FTSE100 my macro is supposed to copy this value down 100000 times or so whenever there is a change from prev value. *however i can't get the PXLAST cell to get updated while the macro is running, so i wanted to introduce a pause in the programming that would allow the DDE to update before having the macro running again. *my code is also no working properly because the macro will execute the ELSE even if the IF clause is verified. as at now, my macro is copying down 6527.6 all the time. can you help me out with code suggestions? much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause/re-run macro to enable DDE updating
so can you please provide an example. how do i force excel to calculate a
cell's formula? the latest code i'm using is: Sub Macro1() Dim i As Integer For i = 1 To 1000 Do While Worksheets("sheet1").Range("pxlast").Value = Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value Loop Worksheets("sheet1").Range("pxlast").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next End Sub "NickHK" wrote: I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change events, if you have a cell dependant on that cells value, the Calculate event will fire. Run your code there. Not sure about the 100000 rows, unless you have XL2007, but then <apparently calculation time will be long. NickHK "ALVESM" wrote in message ... hello, please check the macro i've writen: Sub Macro1() Dim i As Integer For i = 1 To 100000 If Worksheets("sheet1").Range("PXLAST").Value = Worksheets("sheet1").Range("init").Offset(i, 0).Value Then Else Worksheets("sheet1").Range("PXLAST").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next End Sub *on the PXLAST cell i have a formula that gets updated via a DDE link: =blp("ukx index", "last price") *this returns the real time price of FTSE100 my macro is supposed to copy this value down 100000 times or so whenever there is a change from prev value. *however i can't get the PXLAST cell to get updated while the macro is running, so i wanted to introduce a pause in the programming that would allow the DDE to update before having the macro running again. *my code is also no working properly because the macro will execute the ELSE even if the IF clause is verified. as at now, my macro is copying down 6527.6 all the time. can you help me out with code suggestions? much appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause/re-run macro to enable DDE updating
You wait for the calculate event to fire. Plenty from Google. Here's one:
http://en.allexperts.com/q/Excel-105...-Real-time.htm And Stephen Bullen has some stock price/DDE stuff: http://www.bmsltd.ie/DLCount/DLCount...ile=Chrono.zip http://www.bmsltd.ie/DLCount/DLCount...e=LastChng.zip http://www.bmsltd.ie/DLCount/DLCount...e=Previous.zip NickHK "ALVESM" wrote in message ... so can you please provide an example. how do i force excel to calculate a cell's formula? the latest code i'm using is: Sub Macro1() Dim i As Integer For i = 1 To 1000 Do While Worksheets("sheet1").Range("pxlast").Value = Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value Loop Worksheets("sheet1").Range("pxlast").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next End Sub "NickHK" wrote: I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change events, if you have a cell dependant on that cells value, the Calculate event will fire. Run your code there. Not sure about the 100000 rows, unless you have XL2007, but then <apparently calculation time will be long. NickHK "ALVESM" wrote in message ... hello, please check the macro i've writen: Sub Macro1() Dim i As Integer For i = 1 To 100000 If Worksheets("sheet1").Range("PXLAST").Value = Worksheets("sheet1").Range("init").Offset(i, 0).Value Then Else Worksheets("sheet1").Range("PXLAST").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next End Sub *on the PXLAST cell i have a formula that gets updated via a DDE link: =blp("ukx index", "last price") *this returns the real time price of FTSE100 my macro is supposed to copy this value down 100000 times or so whenever there is a change from prev value. *however i can't get the PXLAST cell to get updated while the macro is running, so i wanted to introduce a pause in the programming that would allow the DDE to update before having the macro running again. *my code is also no working properly because the macro will execute the ELSE even if the IF clause is verified. as at now, my macro is copying down 6527.6 all the time. can you help me out with code suggestions? much appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pause/re-run macro to enable DDE updating
ok then, how do i create a calculate event using my code?
thanks in advance "NickHK" wrote: You wait for the calculate event to fire. Plenty from Google. Here's one: http://en.allexperts.com/q/Excel-105...-Real-time.htm And Stephen Bullen has some stock price/DDE stuff: http://www.bmsltd.ie/DLCount/DLCount...ile=Chrono.zip http://www.bmsltd.ie/DLCount/DLCount...e=LastChng.zip http://www.bmsltd.ie/DLCount/DLCount...e=Previous.zip NickHK "ALVESM" wrote in message ... so can you please provide an example. how do i force excel to calculate a cell's formula? the latest code i'm using is: Sub Macro1() Dim i As Integer For i = 1 To 1000 Do While Worksheets("sheet1").Range("pxlast").Value = Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value Loop Worksheets("sheet1").Range("pxlast").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next End Sub "NickHK" wrote: I don't use DDE, but IIRC whilst DDE does not fire the Worksheet_Change events, if you have a cell dependant on that cells value, the Calculate event will fire. Run your code there. Not sure about the 100000 rows, unless you have XL2007, but then <apparently calculation time will be long. NickHK "ALVESM" wrote in message ... hello, please check the macro i've writen: Sub Macro1() Dim i As Integer For i = 1 To 100000 If Worksheets("sheet1").Range("PXLAST").Value = Worksheets("sheet1").Range("init").Offset(i, 0).Value Then Else Worksheets("sheet1").Range("PXLAST").Copy Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If Next End Sub *on the PXLAST cell i have a formula that gets updated via a DDE link: =blp("ukx index", "last price") *this returns the real time price of FTSE100 my macro is supposed to copy this value down 100000 times or so whenever there is a change from prev value. *however i can't get the PXLAST cell to get updated while the macro is running, so i wanted to introduce a pause in the programming that would allow the DDE to update before having the macro running again. *my code is also no working properly because the macro will execute the ELSE even if the IF clause is verified. as at now, my macro is copying down 6527.6 all the time. can you help me out with code suggestions? much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pause macro | Excel Programming | |||
Pause Macro 2 | Excel Discussion (Misc queries) | |||
Pause Macro | Excel Programming | |||
Pause a macro? | Excel Programming | |||
Pause macro | Excel Programming |