LiveOffice event handlers in VBA

Recently I’ve faced with an activities of a report formatting in LiveOffice.

Everything is ok when the reports are fixed-size. But when It’s not the case It needs an additional processing in VBA.

The next step-by-step tutorial explains how to intercept the processing of LiveOffice class events.

There is an class which represents LiveOffice Toolbar. Let’s look at it.

Run MS Excel (I use one 2010) then go to VBA editor (Alt+F11).

Go to “Tools\References” and find the “crystal_addin_framework 1.0 Type Library”. Tick it and press “OK”.

Than restrict the classes by selecting in dropdown menu of “Object Browser” (“View\Object Browser” or F2) the item “CRYSTAL_ADDIN_FRAMEWORKLib” and select the class “CrystalAddin”.

Look at right side of the window. There you can see their members and the events among them. Get two of them “BeforeFunction” and “AfterFunction”.

Now move on VBA coding.

In our VBAProject insert a Class Module “Class1” and define a variable “loevent” of class “CrystalAddin” like this

Public WithEvents loevent As CrystalAddin

Select variable “loevent” from first dropdown list and event “BeforeFunction” from the second one. And the next definition of the subroutine appears.

Private Sub loevent_BeforeFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters)
End Sub

Repeat the selection and create a definition of “AfterFunction” event handler subroutine.

Private Sub loevent_AfterFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters)
End Sub

You can place into these subroutins any code as you like or place a breakpoint to explore the abilities of the LiveOffice object handlers.

I wrote the following

Private Sub loevent_AfterFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters)
'  MsgBox ("AfterFunction")
  If functionName = "RefreshAllViews" Or functionName = "Refresh_View" Then
    Call Transform
  End If
End Sub

The function names “RefreshAllViews” and “Refresh_View” respective to the buttons “Refresh All Object” and “Refresh Object” on LiveOffice Toolbar.

To make it work it needs to define a new variable of our new Class1. Double click to “ThisWorkbook” object and place code here like this

Dim lo As New Class1
Private Sub Workbook_Open()
 Set lo.loevent = Application.COMAddIns("CrystalOfficeAddins.CrystalComAddin.7").Object
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Set lo.loevent = Nothing
End Sub

The variable “lo” is an instance of our Class1. In subroutine “Workbook_Open” the event handler is activated and in the next subroutine is disactivated.

If you need some handlings after opening the document you can experiment with event “AfterDocumentLoad” of the “CrystalAddin” class or something else.

It will be your homework 😉

Well, it can be done by the event WindowActivate of the Workbook instance. See example below.

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
  Static runOnce As Boolean
 
  If Not runOnce Then
    Call Transform
    runOnce = True
  End If
End Sub

Well, that’s all folks 🙂

Kind regards