Here is a basic example. Dedicate a worksheet for your DDE link(s) and record the data in a separate worksheet. Create a new workbook with two sheets. "DDE_Link" and "Data". Copy and paste the code or download the example.
falanks_DDE_Example.zip
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');" ><TD><font size="2" face=Courier New> <font color="#008000">'if you wish to automatically begin recording information when the workbook</font>
<font color="#008000">'is opened; place this procedure into the "ThisWorkbook" class module.</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
BeginRecordingUpdates
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#008000">'the remainder of this code goes into a standard module</font>
<font color="#008000">'simply run "BeginRecordingUpdates" to start recording</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Const</font> MyLinksName <font color="#0000A0">As</font> <font color="#0000A0">String</font> = "MT4|BID!USDJPYm"
<font color="#0000A0">Private</font> <font color="#0000A0">Const</font> RecordToSheetName = "Data"
<font color="#0000A0">Sub</font> BeginRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"
ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Sub</font> RecordUpdates()
<font color="#0000A0">Dim</font> r <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
<font color="#0000A0">With</font> ThisWorkbook.Sheets(RecordToSheetName)
r = Application.WorksheetFunction.CountA(.Columns(1)) + 1
<font color="#0000A0">If</font> r > 65535 <font color="#0000A0">Then</font> StopRecordingUpdates
.Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value
.Cells(r, 2).Value = Now
<font color="#0000A0">End</font> <font color="#0000A0">With</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Sub</font> StopRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, ""
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>
<SPAN>
<button onclick='document.all("TomsCode2").value=document.all("TomsCode2").value.replace(/<br \/>\s\s/g,"");window.clipboardData.setData("Text",document.all("TomsCode2").value);'>Copy to Clipboard</BUTTON>
falanks_DDE_Example.zip</SPAN>
<textarea style="position:absolute;visibility:hidden" name="TomsCode2" wrap="virtual">
'if you wish to automatically begin recording information when the workbook
'is opened; place this procedure into the "ThisWorkbook" class module.
Private Sub Workbook_Open()
BeginRecordingUpdates
End Sub
'the remainder of this code goes into a standard module
'simply run "BeginRecordingUpdates" to start recording
Private Const MyLinksName As String = "MT4|BID!USDJPYm"
Private Const RecordToSheetName = "Data"
Sub BeginRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"
ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents
End Sub
Sub RecordUpdates()
Dim r As Long
With ThisWorkbook.Sheets(RecordToSheetName)
r = Application.WorksheetFunction.CountA(.Columns(1)) + 1
If r > 65535 Then StopRecordingUpdates
.Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value
.Cells(r, 2).Value = Now
End With
End Sub
Sub StopRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, ""
End Sub
</textarea>