Running Macros in Embedded Excel Objects within PI ProcessBook: Best Practices and Solutions

Integrating Excel-based analytics with PI ProcessBook displays is a common requirement in many operations environments. Users often embed Excel spreadsheets as OLE objects within ProcessBook to leverage advanced calculations and custom macros. However, running Excel macros seamlessly—especially during display updates—poses challenges, such as window flicker and performance issues. Here we explore practical solutions to trigger Excel macros reliably from within ProcessBook, avoiding disruptive user experience.

Common Approaches for Running Excel Macros in ProcessBook

1. Activating the Excel OLE Object

One standard method to trigger a macro within an embedded spreadsheet is by programmatically activating (and deactivating) the OLE object:

'to activate:
ThisDisplay.OLEObjects.Item(1).Active

'to return focus:
ThisDisplay.OLEObjects.Item(1).Close

While effective for triggering calculations or macros, this can cause visible flicker as focus switches between ProcessBook and Excel. Rapid updates intensify this issue.

2. Disable Redraw to Prevent Flicker

Flicker can be mitigated by controlling screen redraw during Excel operations:

Application.Redraw = False
'... perform Excel OLE macro or calculation
Application.Redraw = True

By batching updates and only refreshing the user interface after calculations are complete, you keep the display smooth.

3. Staggered or Rate-Limited Updates

If your calculations are heavy or occur too often, consider updating every nth display update or after a set interval (e.g., every 20 seconds). Implementing a strobe or counter in your VBA routines allows finer control:

Static strobe As Integer
strobe = (strobe + 1) Mod 4
If strobe = 0 Then
    Call CalculateExcel
End If

This limits computation frequency, balancing performance and responsiveness.

4. Transferring Values Between ProcessBook and Excel

To synchronize user inputs between the ProcessBook display and the embedded sheet, update cell values directly from ProcessBook VBA:

Dim ExcelSheetOLEObject As Object
Set ExcelSheetOLEObject = Application.ActiveDisplay.OLEObjects.Item(1).Object
ExcelSheetOLEObject.worksheets(1).range("B34").Value = TextBox1.Value

You can then trigger a recalculation or macro as needed.

5. Direct Macro Invocation—Caveats

Some users attempt to invoke Excel macros directly via Application.Run, but this often fails in the OLE/embedded context due to macro security or referencing issues. Instead, rely on manipulating the OLE object as above.

6. Optimizing Excel Macros

A few best practices inside your Excel VBA code can further reduce flicker and boost speed:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'... macro code ...
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

This prevents unnecessary redraws and recalcs. Always restore application states to avoid leaving Excel in manual mode.

---

Sample ProcessBook/Excel Integration Code

Here’s a typical setup in ProcessBook VBA:

ThisDisplay Module:

Private Sub Display_DataUpdate()
    Static strobe As Boolean
    If strobe Then Call CalculateExcel
    strobe = Not strobe
End Sub

Sub CalculateExcel()
    Application.Redraw = False
    Dim ExcelSheetOLEObject As Object
    Set ExcelSheetOLEObject = Application.ActiveDisplay.OLEObjects.Item(1).Object
    ExcelSheetOLEObject.worksheets("PitchScan").calculate
    Set ExcelSheetOLEObject = Nothing
    Application.Redraw = True
End Sub

Excel Worksheet (e.g., in PitchScan):

Private Sub Worksheet_Calculate()
    ' Update charts or process logic here
End Sub

---

Tips for Robustness

• Always handle potential errors, especially when Excel is busy or running slow.
• Test your update frequency—avoid taxing both ProcessBook and Excel with excessive calculations.
• Consider user-triggered updates for infrequent recalculations if real-time updates are not needed.

Conclusion

Embedding Excel sheets in PI ProcessBook is powerful when combined with robust VBA automation. By managing focus, batching updates, rate-limiting, and optimizing both ProcessBook and Excel macros, you can provide advanced calculations without frustrating flicker or lag. For further reading, review the OSIsoft PI ProcessBook VBA documentation and Excel macro best practices.