VBA & Macro Problems

How to Log Changing DDE Values Over Time in Excel VBA

Emma Brown Oct 8, 2026 870 views

Question details

The user needs a method to periodically log data that updates automatically from external DDE sources, along with a timestamp for each change.

How to Log Changing DDE Values Over Time in Excel

Product: Excel
Device & OS: not provided
Scenario: Capturing and recording real-time automated data updates from external DDE links into a historical log worksheet.
Observed behavior: The standard Worksheet_Change event fails to detect values that update automatically through DDE links, preventing standard macros from tracking or logging the data changes over time.

Before you start

Ensure that you have enabled macros in your Excel settings and saved your workbook as a Macro-Enabled Workbook (.xlsm) before adding the required VBA timer code.

Solution 1Recommended

Use Application.OnTime to Create a Timer-Based VBA Logger

Create a set of VBA macros to periodically check and record DDE values along with timestamps into a dedicated log sheet.

Since the Worksheet_Change event is only triggered by manual edits, automated DDE updates require a time-based checking mechanism. By utilizing the Application.OnTime method, you can instruct Excel to run a specific logging procedure at regular intervals.

This solution involves creating dedicated procedures to start the timer, log the data, and crucially, stop the timer when you are finished.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

Click 'Insert' from the top menu bar and select 'Module' to create a blank script window for your code.

Create the Timer Procedures

Write VBA procedures named 'StartTimer' and 'StopTimer'. Use 'Application.OnTime' within these macros to schedule when the logging script should run.

Create the Logging Procedure

Create a 'LogData' procedure that reads the values from your DDE-linked cells and copies them to an empty row on your log sheet, alongside the 'Now()' function to record the exact timestamp.

Run the Macro

Close the VBA editor, press 'Alt + F8' in your workbook, select your 'StartTimer' macro, and click 'Run' to begin continuously recording the DDE data.

Use Application.OnTime to Create a Timer-Based VBA Logger

Remember to Stop the Timer: Always ensure you run the 'StopTimer' procedure before closing the workbook. If the timer is not stopped, Excel may attempt to reopen the file automatically when the next scheduled time arrives.

Use WPS Spreadsheet for Macros

Log Automated Data with Macros in WPS Spreadsheet

WPS Office provides robust support for VBA and macros, allowing you to run timer-based scripts to log automated data smoothly and efficiently without compatibility issues.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook within the WPS Spreadsheet application.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'VBA Editor' to open the macro environment.
3. Write the Logging Macro: Insert a new module and write your timer-based VBA code utilizing the Application.OnTime function.
4. Execute the Script: Save your document as a macro-enabled file (.xlsm) and run the script to begin logging your automated data.

Fully compatible with Microsoft Excel (.xlsm) macro filesSupports standard VBA methods including Application.OnTimeLightweight architecture ensures automated scripts run quicklyFree to download and use for advanced spreadsheet automation

Frequently Asked Questions

Why doesn't the Worksheet_Change event trigger for DDE links?

The Worksheet_Change event is strictly triggered by manual user edits or changes caused by other standard macros. Data pushed automatically from external feeds like DDE (Dynamic Data Exchange) bypasses this event, requiring a timer-based workaround.

How do I stop the Application.OnTime macro from running continuously?

You need to create a dedicated 'StopTimer' VBA procedure that calls Application.OnTime with the exact scheduled execution time, but with its 'Schedule' argument set to False.

Can I run this timer macro while working on other Excel files?

Yes, but you must ensure your macro explicitly references the exact workbook and worksheet names (e.g., ThisWorkbook.Sheets("Log")). If you only use ActiveSheet, the macro might attempt to log data onto whatever workbook you are currently viewing, causing errors or data corruption.

What happens if I close Excel without stopping the timer?

If the timer isn't explicitly cancelled before closing, Excel will remain open in the background or attempt to reopen the workbook when the scheduled time arrives. It is best practice to place your stop procedure inside the Workbook_BeforeClose event.

Excel & Data Problems