Skip to Content
Author's profile photo Igor Klushnev

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).

Snap2.png

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

MSExcel_VBA_Tools_References.png

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”.

MSExcel_VBA_ObjectBrowser.png

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

MSExcel_VBA_ClassModule.png

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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very good information! This is exactly what I need to do, but I've encountered an error.

      When this runs in my case I get 'Type mismatch error' on the portion below.

      Set lo.loevent = Application.COMAddIns(1).Object

      Running this in Office 2010 with SAP BO Live Office 14.0.4.770. I've added the crystal_addin_framework 1.0 Type Library as instructed.

      I'm not sure where to start to fix this, any suggestions?

      Author's profile photo Igor Klushnev
      Igor Klushnev
      Blog Post Author

      Hi Lee,

      I've also faced with such error when I get installed some other COM addons and LiveOffice addon may not be in the first place in addons array. Therefore I have changed the reference to "CrystalOfficeAddins.CrystalComAddin.7" as you can see in this tutorial.

      Kind regards,

      Igor