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.

Private Sub Worksheet_Change(ByVal Target As Range)

  On Error GoTo ExitOut
  Application.EnableEvents = False
  
    If Not IsEmpty(Target) And Target.Address = "$A$1" Then
      If IsEmpty(Range("B1")) Then
         Range("B1") = Target
      Else
         Range("B1").End(xlDown).Offset(1, 0) = Target
      End If
    End If
  
ExitOut:
  Application.EnableEvents = True
  Err.Clear
  
End Sub

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.

Private Sub Worksheet_Change(ByVal Target As Range)

  On Error GoTo ExitOut
  Application.EnableEvents = False
  
    If Not IsEmpty(Target) And Target.Address = "$A$1" Then
      If IsEmpty(Range("B1")) Then
         Range("B1") = Target
      Else
         Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Target
      End If
    End If
  
ExitOut:
  Application.EnableEvents = True
  Err.Clear
  
End Sub

[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]

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?