Lab Create: Using Report Macro

Estimated reading time: 8 minutes

Overview:

Report Macro is an excel formula provided by INTERJECT. It allows INTERJECT to interact with publicly defined VBA functions on a report. Report Macro is useful for specialized reports where a customized action needs to be taken. This action can be built into a VBA Macro based on the complex needs of the report. Then, ReportMacro() is used to execute the custom action at the right point within the reporting process.

Using Report Macro

The report macro formula acts in conjunction with another INTERJECT report formula such as a ReportRange() . It is useful when a workbook requiring VBA macros must be used. ReportMacro() is an ordered formula, which means that INTERJECT will perform executions on all report formulas placed above or to the left of the ReportMacro() formula before it is executed.

Step 1: Open the Customer Aging Report. (If this report is unfamiliar, click here for a business use case review on the Customer Aging Report.) This time, we want to make sure a value is set to 200 every time the report is pulled.


Step 2: To begin, unfreeze the panes.


Step 3: If you see the Developer Ribbon, skip this step and the next one. However, if you do not see the Developer Ribbon select File


Step 4: Now, select Options.


Step 5: Select the Customize Ribbon tab in the Main Tabs section, select the check box next to Developer, then select OK.


Step 6: Select the Developer Ribbon.


Step 7: Select Visual Basic.


Step 8: To create a new module, right click in the Projects window, place your cursor over Insert, and select Module.


The example code is provided here.


Public Function MyCustomFunction()

  Call Place_Value(ActiveSheet.Range("L31"))

End Function  

 ' Clears the value in the cell then sets it to equal 200 

 Sub Place_Value(sTarget As Range)

  sTarget.ClearContents 

    sTarget = 200 

End Sub

Step 9: Insert your code into the editing window, then select Debug, then compile VBAProject and exit out of the VBA window.


Step 10: In cell D8, Insert the label Macro 1: and align it to the right.


Step 11: In cell E8 insert =ReportMacro() and select fx.


Step 12: In the parameter OnpullSaveOrBoth, input Pull. In the parameter OnClearRunOrBoth, input Both.


Step 13: For the MacroNameToRun parameter, input the exact name of the public function that you created. In this case, you will use MyCustomFunction.


Step 14: Pull the data. You will notice that the report macro was ordered after the report range action. This is due to INTERJECT formula event ordering.


Requirements for VBA

ReportMacro() requires a public function without any parameters, as it is the initial interaction point with INTERJECT. Inside of that initial function, any function can be run, including calls to subroutines and other functions, but the initializing macro must be a public function.


ReportMacro() also requires the VBA macro to be in written in shared module, and it will not work with a Workbook or Sheet module.


INTERJECT will error out the report macro formula upon completion of the INTERJECT event associated with the macro formula, however the INTERJECT event will complete.


Enabling VBA Hooks

Due to heavy load on user sessions, four of the six VBA hooks that INTERJECT uses will be deprecated. This feature should only be enabled when a VBA macro is required for custom actions in specialized reports.

VBA Hooks To Be Deprecated When Used
Interject_SaveComplete Upon completion of an INTERJECT save event
Interject_PullComplete Upon completion of an INTERJECT pull event
Interject_ClearPullComplete Upon completion of an INTERJECT clear event
Interject_ClearSaveComplete Upon completion of an INTERJECT clear event on the save form
VBA Hooks To Be Supported When Used
Interject_WorkbookOpen_Drill When a drill opens a different workbook from the initial workbook
Interject_WorkbookOpen_Library When a workbook is opened from the report library.

Enabling INTERJECT VBA hooks is currently done by including a VBA Module with a public subroutine (not function) that matches the name hook above without any parameters. If the subroutine is found, it will be fired at the appropriate time, based on the type of VBA hook used. Using a VBA hook does not require a ReportMacro() formula in order to function, because the VBA hook is sent by INTERJECT upon the completion of certain events.


' Simple Example of using a VBA hook

Public Sub Interject_PullComplete()

  If ActiveSheet.Range("L31") > 199 Then

    ActiveSheet.Range("L31") = 150

  End If 

End Sub