Create: Using Report Macro

Overview

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

 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.

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