Purpose

You need a way to reliably track when Velixo Reports has finished calculating a background refresh - allowing your VBA code to then execute custom, post-refresh logic

Remarks

A Foreground (or "synchronous") Refresh is useful when you want to ensure the report is refreshed before performing the next action (e.g., before report distribution).

A simple foreground Refresh might look something like this

Dim velixoObj As Velixo_Reports.VBA
Set velixoObj = CreateObject("Velixo.Reports.Vba")

Application.DisplayAlerts = False

velixoObj.Refresh

However:

  • Such a blocking refresh halts the UI completely until all functions return. If a report involves retrieving large amounts of data, such a "freeze" can be unpleasant for the user.

  • You cannot perform a foreground refresh from the Ribbon (a ribbon refresh always takes place in the background in order to keep Excel responsive).

For user-initiated (background) refreshes, we need a simple and reliable way to hook up to the "refresh completed" event from VBA and then execute additional VBA code, with a guarantee that Velixo has completed calculations.

Starting with Velixo Reports v7, you can use the Refresh Callback Registration Mechanism to remedy both problems.

Example

First, you need to register the callback in the ThisWorkbook module:

Private Sub Workbook_Open() 
Dim velixoObj As Velixo_Reports.VBA
Dim noArgs() As Variant

Set velixoObj = CreateObject("Velixo.Reports.Vba")

velixoObj.AddRefreshCompleteCallback ActiveWorkbook, "RefreshCompleted", noArgs
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim velixoObj As Velixo_Reports.VBA
Dim noArgs() As Variant

Set velixoObj = CreateObject("Velixo.Reports.Vba")

velixoObj.RemoveRefreshCompleteCallback ActiveWorkbook, "RefreshCompleted", noArgs
End Sub


And then define the corresponding callback with code that you'd like to perform after the Refresh has completed:

Public Sub RefreshCompleted() 
MsgBox "Refresh Completed -- do your thing here, fiddle with your pivots, filters, transform data etc."
End Sub

Note: the callback needs to be in a VBA module. It will not work if you add it to the code of a sheet or in ThisWorkbook module. Click Insert->Module in VBA to create a new module.


This will now allow you to invoke Refresh asynchronously from VBA:

velixoObj.Refresh True


and also allow custom VBA code to be executed when a user has initiated and finished a Refresh from the ribbon.

Did this answer your question?