Submitting your feedback...
Knowledge Base Article
KB01035 - Programmatically Recalculate (Resize) DataLink Functions
Product: PI DataLink
Version(s): 2010 SP1+
 

Use case

You want to programmatically select, resize, and recalculate a PI DataLink array, providing the same functionality as the "Recalculate (Resize) Function" option from the function's right-click menu.

st-widget-{image: Right click menu.png}

Background

When a PI DataLink function writes a function array, it automatically sizes the array to fit the returned data. Functions write the function array when you click OK or Apply on the task pane or when you click Recalculate (Resize) Function on the shortcut menu.

Other methods that recalculate or update a function do not write a new function array. These methods only update array values; the array size remains unchanged. For example, if you specify function inputs with cell references and change the value in a cell, the function will update returned values but the size of the array remains the same. The update might return more or fewer values than the function array on the worksheet can show. Settings control what PI DataLink returns:
  • If an update returns fewer values, PI DataLink either returns blanks or #N/A in the cells without values.
  • If the update returns more values than the function array can show, PI DataLink returns the text Resize to show all values at the bottom of the array unless the preference settings disable the message.
OSIsoft exposes the ability to select and resize PI DataLink functions using VBA.  
  • Prior to PI DataLink 2014, a reference to pidldialogs.xla was necessary to select and resize PI DataLink functions using the VBA subroutines DLResize and DLselect.  
  • Starting in PI DataLink 2014, it is no longer necessary to reference pidldialogs.xla because the ResizeRange and SelectRange methods implemented for the Explore Events and Compare Events functions are exposed to VBA through the AddInUtilities class.

Solution

Two possible solutions are presented in detail:
  • Selecting and resizing every DataLink function in a worksheet with a single button
  • Implementing select and resize using two separate buttons

Once initial substitutions are made to tailor this solution to your particular worksheet, all DataLink arrays will be resized with a single click.  Download the following Excel file for an example implementation.

To adjust the sample code to your particular worksheet:

  1. Open the VBA Editor (Alt+F11) and double-click the ResizeAll module in the Project Explorer pane. If you use your workbook instead of the example file, create a module and paste the sample code into it.
  2. Change the 7 in "Dim allRanges(1To7)As Range" to the total number of DataLink arrays in your spreadsheet.
  3. Update the cell references that populate the allRanges array to represent the top left cells of each PI DataLink array.
 

​Sub ResizeAll()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("PI DataLink")
    Set automationObject = addIn.Object
    'To adjust this subroutine to another Excel worksheet, update the cell references in allRanges to reflect the top left cell of each DataLink array.
    'Then change the array size in the below line:
    Dim allRanges(1 To 7) As Range
    Dim i As Long    

    'Set the values of the allRanges array to the top left cell of each DataLink result array.
    Set allRanges(1) = Range("D2")
    Set allRanges(2) = Range("G2")
    Set allRanges(3) = Range("J2")
    Set allRanges(4) = Range("M2")
    Set allRanges(5) = Range("P2")
    Set allRanges(6) = Range("R2")
    Set allRanges(7) = Range("V2")
    
    For i = LBound(allRanges) To UBound(allRanges)
        'For each DataLink array, select the top left cell, then select all cells in the array, and finally resize the array.
        allRanges(i).Select
        automationObject.SelectRange
        automationObject.ResizeRange
    Next i
End Sub


Note that this macro runs significantly faster when Disable automatic task pane display on click is selected in DataLink settings.

st-widget-{image: DL settings disable auto task pane.png}

Implementing select and resize using two separate buttons

To create two buttons that separately select and resize (recalculate) a PI DataLink function:
  1. Open the VBA Editor (Alt+F11) and add two buttons to your spreadsheet.
  2. Add the VBA code shown below.
    • The first button, which uses "ResizeRange", will resize the function array that occupies cell C1.
    • The second button, which uses "SelectRange", will select the function array that occupies cell E1.


Private Sub CommandButton1_Click()    
    Dim addIn As COMAddIn
    Dim automationObject As Object
    
    Set addIn = Application.COMAddIns("PI DataLink")   
    Set automationObject = addIn.Object
    
    Dim MyRange As Range
    Set MyRange = Range("C1")
    MyRange.Select
    
    automationObject.ResizeRange
End Sub


Private Sub CommandButton2_Click()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("PI DataLink")
    Set automationObject = addIn.Object
    
    Dim MyRange As Range
    Set MyRange = Range("E1")
    MyRange.Select
    
    automationObject.SelectRange
End Sub
 
======

CommandButton1_Click()-- Resizes and Recalcs an array 

CommandButton2_Click()--Selects the data array

PIDLdialogs

First, ensure you have a reference to %pihome%\Excel\pidldialogs.xla. In the VBA editor (Alt+F11), go to Tools | References and verify PIDLdialogs is checked.  If it is not present, browse to the .xla and add it.

st-widget-{image: References -= VBAProject.png}

When successfully added, PIDLdialogs.xla are listed under References in the Project Explorer pane:

st-widget-{image: Project Explorer.png}

DLResize and DLselect

PIDLdialogs.xla's PIDL_main module contains the Public Sub DLResize() and the Private Sub DLselect().  Because DLselect is private, it is not visible within the Object Browser or available to be called from another module in the same way as DLResize.  DLResize is analogous to ResizeRange for DL 2014+; DLselect is analogous to SelectRange.

After the PIDLdialogs reference has been added, DLResize can be seen as a member of PIDL_main inside the Object Browser (F2).

st-widget-{image: Object Browser DLResize.png}

Adapting the code from a previous example, we can see that it functions in much the same way:

Sub ResizeAll()
    'To adjust this subroutine to another Excel worksheet, update the cell references in allRanges to reflect the top left cell of each DataLink array.
    'Then change the array size in the below line:
    Dim allRanges(1 To 7) As Range
    Dim i As Long
    
    'Set the values of the allRanges array to the top left cell of each DataLink result array.
    Set allRanges(1) = Range("D2")
    Set allRanges(2) = Range("G2")
    Set allRanges(3) = Range("J2")
    Set allRanges(4) = Range("M2")
    Set allRanges(5) = Range("P2")
    Set allRanges(6) = Range("R2")
    Set allRanges(7) = Range("V2")
    
    'For each DataLink array, select the top left cell, then select all cells in the array, and finally resize the array.
    For i = LBound(allRanges) To UBound(allRanges)
        allRanges(i).Select
        Application.Run "PIDL_Main.DLselect"
        Call DLResize
    Next i
End Sub

Because DLselect is a private subroutine, Application.Run is used to call it from a module outside of the PIDL_main module.  If DLselect is called in the same manner as DLResize, a "Sub or Function not defined" compile error is returned.

Article ID: KB01035 Created: 2014-04-17
Article Type: How-To Last Updated: 2017-12-01