Create: Using Report Macro

Estimated reading time: 9 minutes


ReportMacro() is an Excel formula provided by Interject. It allows Interject to interact with publicly defined VBA functions on a report. ReportMacro 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.

If you are following the Training Labs, this report file can be found in the Report Library at Training Labs > Lab 6 Special Features > Lab 6.3 Using Report Macro.

Setting up the Worksheet

The ReportMacro 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 forward to the next section. 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.

Using Report Macro

Step 1: Select Visual Basic.

Step 2: 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 = 200

End Sub

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

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

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

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

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

Step 8: Pull the data. You will notice that the ReportMacro 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 a shared module as it will not work with a Workbook or Sheet module.

Interject will error out the ReportMacro 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