“My A1 cell changes value every minute because it imports data from a DDE server. To keep historical data, I would like to list each value in a worksheet minute after minute. That is, when it’s 8:00 keep cell A1 value. When it’s 8:01 keep cell A1 value, etc.” — G.T.
A variety of software can update a worksheet as you describe. So other people could have the same problem.
I’m going to give you two ways to solve this problem. You can do it with formulas and you can do it with VBA. As a general rule, I don’t encourage users to spend too much time with VBA. But this may be a case where VBA offers the best solution to your problem.
Neither solution, however, relies on a minute-by-minute update. The VBA solution adds to the list whenever cell A1 changes. The formula solution adds to the list whenever Excel recalculates. In both cases, Excel stores both the value and the time the value changed.
The VBA Solution
Activate the worksheet where your cell A1 is updated. (Let’s call it the DDE worksheet.) Enter the label “ListDDE” in your DDE worksheet where you want your list of changes to begin. Select this cell and assign the nameListDDE to it.
- In Excel 2003, choose Insert, Name, Define, or press Ctrl+F3…
- In Excel 2007, choose Formulas, Defined Names, Define Name, or press Ctrl+F3 and choose New…
…and then enter ListDDE as the name, and choose OK.
Press Alt+F11 to activate the Visual Basic Editor, the VBE. In the VBA Project window, you’ll see a list of open workbooks. Find your workbook in the list and find the name of the DDE worksheet in the list beneath the workbook’s name. (You might need to click on one or two “+” signs in the window to see the names of your workbook’s worksheets.)
Double-click on the name of the DDE worksheet in the list. When you do so, the main window of the VBE will display the code sheet for that worksheet.
Copy and paste the following code into that window.
| ”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 |
The comments in green explain every line of the macro. You can copy the comments with the macro.
After you copy the code, your VBE should look something like the following figure, but your VBE probably will include several more windows.
To test this macro, enter any value in cell A1 of the DDE worksheet. Each time you do, the macro should enter the time and the value in the list below your ListDDE label.
The Formula Solution
Excel formulas also can track your changes. But to do so, you need to change your options to Iterative Calculations.
To do so, activate your DDE workbook. Then…
- In Excel 2003, choose Tools, Options. In the Calculation tab, check Iteration and enter a Maximum Iterations value of 1…
- In Excel 2007, choose Office Button, Excel Options (in the bottom right of the dialog.) In the Formulas section, check Enable Iterative Calculation and set the Maximum Iterations value to 1…
…and then choose OK.
Now make the changes shown in the figure below to your DDE worksheet.
Cell A1 contains the value that your DDE program writes. Cell B1 contains text used to label the value.
To assign the text in cell B1 as the name in cell A1, select the range A1:B1 and then…
- In Excel 2003, choose Insert, Name, Create, or choose Ctrl+Shift+F3…
- In Excel 2007, choose Formulas, Defined Names, Create From Selection, or choose Ctrl+Shift+F3…
…make sure only Right Column is checked, and then choose OK.
Enter the text shown in the range A2:A4, and then assign the text as names for the cells to their right. Use the same approach as you did before, but make sure only Left Column is checked.
Now enter the formulas or value for the cells shown:
B2: =NOW()
B3: =IF(Reset,-1,B3+1)
B4: 0
Cell B3 should look strange, because it refers to itself and therefore has a circular calculation. But there’s no error. Excel doesn’t complain because of the Iteration setting.
Here’s the logic of cell B3: If Reset has a non-zero value — and is therefore TRUE — cell B3 returns the value -1. Otherwise, cell B3 adds 1 to its existing value. That is, until it’s reset, this “Counter” cell counts the number of times that you recalculate. And because your workbook recalculates each time it’s updated, the Counter is counting the number of times that the DDE process has updated your workbook.
(This assumes, of course, that you’ve dedicated this instance of Excel to the DDE updating process. This method fails if you’re doing other work that causes Excel to recalculate at other times. That’s the primary reason I included the VBA macro.)
To complete this method, enter the text shown for row 6 and enter the following formula and value for the cells shown:
A7: 0
A8: =A7+1
Now enter these formulas for the cells shown:
B7: =IF(Reset,””,IF($A7=Counter,Time,B7))
C7: =IF(Reset,””,IF($A7=Counter,Value,C7))
These two formulas have the same logic. If Reset is non-zero, the cells return a null string. Otherwise, if the Num value in column A is equal to the Counter value, they return the Time and the Value numbers respectively. But if their value in column A doesn’t equal the Counter value, these two cells return their beginning value.
Copy the range B7:C7 to cell B8. Then copy the range A8:C8 as far down your spreadsheet as you want.
To test this approach, first turn Automatic Calculation on. To do so…
- In Excel 2003, choose Tools, Options. In the Calculation tab, choose Automatic, and then choose OK.
- In Excel 2007, choose Formulas, Calculation, Calculation Options, and then choose Automatic.
Then, reset the table by entering the value 1 in the Reset cell. When you do so, it should look something like this:
Enter 0 in the Reset cell, turning Reset off. Then enter a value in cell A1. And then another value in cell A1. Each time you enter a value, your worksheet recalculates and your table grows by a new row of data…to the limit of the formulas you’ve copied.
To save the log permanently, be sure to copy your results and paste them as values to another workbook. To paste the values, select your target cell and then…
- In Excel 2003, choose Edit, Paste Special, Values..
- In Excel 2007, choose Home, Clipboard, Paste, Paste Special, Values…
…and then choose OK.
Finally, a note about the Iterative Calculation setting. Each time you open the workbook, you need to make sure that Iterative Calculation is turned on. And each time you close this workbook and go on to other work, you need to make sure that Iterative Calculation is turned off.
An Update
A visitor wrote to say that Worksheet_Change wasn’t launching the macro for DDE updates, although it did work for manual updates. I asked my Excel MVP friends about this and got three suggestions:
The late Chip Pearson wrote that like me, he hasn’t worked with DDE for a long time. He suggested using Worksheet_OnData rather than Worksheet_Change.
Jon Peltier, Peltier Technical Services, offered two ideas. Assuming the DDE data was in cell A1, he used to enter a formula like =A1 off to the side, which caused a change that Worksheet_Change would recognize. He also found the Microsoft help topic, SetLinkOnData Method, which offers a more formal approach.
