Excel/Track Dynamic Data with VBA
I have a fuction in excel that generated by press F9 and I want to record this value. I tried using this code
''Because this program is in the DDE worksheet, it runs
''each time a value changes
Private Sub Worksheet_Change(ByVal Target As Range)
''Do something only if the value changes in cell A1
If Target.Address = "$A$1" Then
''Look at the full list below the Target title
''Look at the cell at the bottom of the list
With .Offset(.Rows.Count, 0).Resize(1, 1)
''Enter the current time in the cell
.Value = Now
''Enter the new value to the right of the time
.Offset(0, 1).Value = Target.Value
But it only work for manually typing value in A1 cell. Is there anyother way to do this? I use Excel 2013.
flashray_13 at hotmail dot com
Before the new events were introduced in Excel 97, there was an old way to do this. Here is the help on the SetLinkOnData method:
Excel Developer Reference
Sets the name of a procedure that runs whenever a DDE link is updated.
expression A variable that represents a Workbook object.
Name Required/Optional Data Type Description
Name Required String The name of the DDE/OLE link, as returned from the LinkSources method.
Procedure Optional Variant The name of the procedure to be run when the link is updated. This can be either a Microsoft Excel 4.0 macro or a Visual Basic procedure. Set this argument to an empty string ("") to indicate that no procedure should run when the link is updated.
This example sets the name of the procedure that runs whenever the DDE link is updated.
Visual Basic for Applications
The code shown is run once to establish the link between the cell with the DD link and the macro to run ("my_Link_Update_Macro") This macro to be called on the DDE update should be in a general module, not a sheet module.
I have found in my testing many years ago that the cell has not updated when the macro fires. So I put application.Calculate in the macro to get the updated value. Just a heads up.