Programmatically Recalculating and Resizing PI DataLink Arrays in Excel

When working with PI DataLink in Excel, it's common to set up arrays of DataLink formulas to pull process data directly into your worksheets. However, as your data requirements change—a variable number of rows returned from an event frame search, for example—you may need to programmatically trigger recalculations and resize these arrays to ensure your data stays up-to-date and correctly formatted.

Let’s explore best practices and techniques for programmatically recalculating and resizing DataLink arrays in Excel, drawing on OSIsoft PI community knowledge and official documentation.

The Manual Approach: Right-Click Recalculation

Traditionally, users can right-click on a PI DataLink array and select “Recalculate” to update and resize their data. While efficient for ad-hoc updates, this approach falls short for automated reports or when integrating DataLink functionality in programmatic workflows.

Excel Calculations: Volatile Functions and VBA

One suggestion is to leverage Excel’s recalculation engine:

• Volatile Functions: By adding volatile functions (like =NOW() or =RAND()) into your worksheets or arrays, you can trigger recalculations whenever the workbook recalculates. DataLink functions respond to these changes, refreshing their values. Refer to the DataLink manual’s section on Triggered Recalculation for more details.

• VBA Calculation Commands: To programmatically enforce recalculation across your worksheet, using ActiveSheet.Calculate (or similar methods) in VBA will cause Excel (and therefore DataLink) to recalculate all formulas. Example:

  ActiveSheet.Calculate
  'or to recalculate the whole workbook:
  Application.CalculateFull
  

However, users have noted that this doesn't always cause DataLink arrays to expand or shrink dynamically based on returned results. For that, a more direct integration is needed.

The Power Tool: PI DataLink’s dlresize Function via VBA

PI DataLink supplies an internal function—dlresize—that can be called from VBA to programmatically recalculate and resize the formula arrays. To access this, you’ll need to:

1. Reference the DataLink Add-in: In the VBA Editor, add a reference to PIDLDialogs.xla (C:\Program Files\PIPC\Excel\PIDLDialogs.xla by default).

2. Write Your VBA Macro: Here’s a simple example to recalculate and resize an array in cells B8:G8:

   Private Sub cmdRecalc_Click()
       Sheet1.Range("B8:G8").Select   ' Select array range
       Call dlresize                   ' Recalculate and resize
   End Sub
   

   To apply it to the active worksheet/selection:

   Private Sub cmdRecalc_Click()
       ActiveSheet.Select
       Call dlresize
   End Sub
   

Additional Resources

For further detail (including downloadable code and best practices), OSIsoft’s Knowledge Base Article KB01035 - Programmatically Recalculate (Resize) DataLink Functions is an excellent reference.

Note: The PIDLDialogs.xla file is typically found at C:\Program Files\PIPC\Excel. Ensure you have the correct path for your installation.

Conclusion

Automating the update and resize of PI DataLink data arrays is achievable through built-in Excel recalculation methods and, more powerfully, through VBA macros leveraging PI DataLink’s dlresize functionality. This not only streamlines report updates but also ensures your data presentation remains consistent with evolving data pulls—making your Excel dashboards more robust, flexible, and reliable.