DDE-link problem

[nyrn2k]

hi all
i would like some help with DDE-linking.
i want to link my stock portfolio to a spreadsheet to make various calculations on historic performance.
my problem is that it is the same cell thats get updated whenever a change occurs, so that there is no history.

let's say i put the DDE-link in cell A1, i would like B1 to be filled with that value
and then when the value changes i would like B2 to be filled with the new value
and the next time B3, and so on.

can anyone help me?

thanks
nyrn2k

[Leith Ross]

Hello nyrn2k,

Because the link will only update the cell, you need a macro that will trigger when cell "A1" changes to create the history.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

[nyrn2k]

thanks

i followed your instructions but i could only get it to work once when A1 was empty and i filled it with a number.
then when i changed A1 nothing happened...
do i need to do something else aswell, or am i doing something wrong?

i have another question;
how do i make a cell show the last value in a row?

thanks for your help

/nyrn2k

[Leith Ross]

Hello nyrn2k,

That was my error, not yours. I rewrote the code and this works correctly. Delete the previous macro and replace it with this version.

Please Login or Register  to view this content.

[nyrn2k]

thank you

it worked beautifully, exactly what i wanted, when i changed the value of A1 manually.
however, it didn't work when i put a link in A1, then the change didn't trigger...

and also, the new question again:
how do i make a cell show the last value in a row?

/nyrn2k

[DonkeyOte]

A DDE-link will not invoke the Change event, you will need to use the Calculate event but that in itself is open to error (ie the Calculate event can be invoked by all manner of events not simply a DDE update - trapping the DDE update is not as trivial as it first seems)... if you run a search on the forum on DDE you should find a lot of examples - possible workarounds.

see: http://www.excelforum.com/excel-work...matically.html

In the above I make mention of a couple of ideas mentioned to me by Colin Legg (MS Excel MVP) which might prove useful to you...

[nyrn2k]

hi
I don't get it, is what is Calculate event?

The DDE-link works, but it updates only the cell where i put it.

The code Leith posted works for creating a row of history whenever A1 is changed, but only when i change it manually, not with DDE.
How do I make that happen?

and also; how do I link a cell to the last filled cell in a row?

/nyrn2k

[Leith Ross]

Hello nyrn2k,

Here is the code for the Worksheet_Calculate event. Delete the previous macro and add this one to the sheet that has the DDE liink in "A1".

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

[shg]

You might want to disable events ...

[nyrn2k]

now it updates properly,
but if i put formulas in row C referring to the values coming in in row B, row B gets filled up immediately.

is it possible to put the DDE-link in the code? would that solve anything?

[nyrn2k]

thanks for all your help!

how would i make excel do this for multiple different stocks, but only check/update latest price once every ten minutes?