How to Resize PI Compressed Data Function using VBA?

Question

I have many compressed data functions in an Excel workbook that all reference the same two cells as the start time and end time. When I change the start time and end time to cover a larger timeframe with more data, the compressed data functions do not resize automatically and display "Resize to show all values" instead of the full list of data. This can be fixed by right clicking on each function and selecting "Recalculate (Resize) Function" but it is very time consuming to do so.

I have tried using the Full Calculate command to recalculate and resize the Compressed Data functions, but it seems that Full Calculate is unable to reset functions managed by an Excel add-in (PI Datalink in this case):

Sub ResizeDataFunctions()
Application.CalculateFull
End Sub

I have also tried creating a Compressed Data function which is larger than the amount of data I am expecting to receive. The issue with this is that I end up with a number of empty cells, which are still part of the function, that are populated with a single space.

Answer 1

You can use some PI Datalink add-in features like this below. This works but I think there are some better ways out there

        Dim SNarray
        ReDim SNarray(1 To Sheets.Count)
        Dim Pl As Variant
        Dim addIn As COMAddIn
        Dim automationObject As Object
        Set addIn = Application.COMAddIns("PI DataLink")
        Set automationObject = addIn.Object
        Dim allRanges(1 To 7) As Range
        Dim update As Integer
        update = 1
        Application.ScreenUpdating = False
        Set allRanges(1) = Worksheets("Sheet1").Range("a1") 'Change Worksheet Name
        'For each DataLink array, select the top left cell, then select all cells in the array, and finally resize the array.
        allRanges(update).Select
        automationObject.SelectRange
        automationObject.ResizeRange

You can browse through this library I wrote that has some other ways to use vba and datalink you may find interesting/useful/alternative option. https://github.com/itecasolutions/IndustrialAutomationVBA