You are here:

Excel/Track Dynamic Data with VBA

Advertisement


Question
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
     With ThisWorkbook.Names("ListDDE").RefersToRange.CurrentRegion
        ''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
         End With
     End With
  End If
End Sub

But it only work for manually typing value in A1 cell. Is there anyother way to do this? I use Excel 2013.

Best regards,
Tony Than
flashray_13 at hotmail dot com

Answer
Tony Than,

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
Workbook.SetLinkOnData Method
Sets the name of a procedure that runs whenever a DDE link is updated.
Syntax

expression.SetLinkOnData(Name, Procedure)

expression   A variable that represents a Workbook object.

Parameters

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.

Example


This example sets the name of the procedure that runs whenever the DDE link is updated.

Visual Basic for Applications
ActiveWorkbook.SetLinkOnData _
   "WinWord|'C:\MSGFILE.DOC'!DDE_LINK1", _
   "my_Link_Update_Macro"


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.  

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.