Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     
 

Home >  Excel Solutions  > 

An Excel 2003-2007 Tutorial:

Track Dynamic Data with VBA
Or with Iterative Calculations



"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.

by Charley Kyd
 

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 name ListDDE 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.

The Visual Basic Editor in Microsoft Excel.

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:

A spreadsheet that uses interative calculations to track repeated updates to a cell.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 1 in the active cell B4 to reset the tracking list in this Excel workbook.

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:

Chip Pearson, Pearson Software Consulting, 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.

 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

 

Excel Dashboards

Learn how to create top-quality dashboard reports with Excel.