How to execute custom VBA logic upon Velixo Refresh completion

Created by Damien Zwillinger, Modified on Tue, 22 Oct at 11:08 AM by Harry Lewis

Applies to:

Velixo Classic

Important: Visual Basic for Applications macros are not supported by Excel Online.

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.

See Introduction to Velixo's VBA functions for general usage information.

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

* IMPORTANT *

The callback (aka RefreshCompleted function) MUST be in a:

VBA module

It will not work if you add it in the code for a worksheet or in ThisWorkbook module.

Click Insert > Module in VBA to create a new module and place the function in that 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.