Implementing VBA Hooks

VBA hooks allow enhancing a certain functionality in the SAP Integrated Business Planning, add-in for Microsoft Excel with custom Visual Basic for Applications (VBA) code.

There are two main groups of hooks: hooks for planning views and hooks for master data workbooks.

It is possible to extend planning view workbooks with custom VBA implementations for special check purposes. The following hooks are available:

• IBPBEFORESEND

• IBPAFTERREFRESH

The IBPBeforeSend hook is called just before the SAP IBP, add-in for Microsoft Excel sends an update request to the back end from a planning view (after the standard SAP IBP validation). In this hook, you can implement some extra validation on the data in the planning view. The process of validation can be stopped if necessary.

The IBPAfterRefresh hook is called after the user chooses the Refresh button under the Data Input ribbon group. For example, it can provide a message, that informs that refresh had been processed.

It is possible to extend master data workbooks with custom VBA implementations. VBA hooks can be used for both master data and planning objects with key figure data. The following hooks are available:

• IBPMDBEFOREUPDATE

• IBPMDAFTERREFRESH

The IBPMDAfterRefresh hook is called when the master data worksheet is created or refreshed. Using this hook, you can manipulate the layout and appearance of master data worksheets, such as reordering, coloring, hiding columns, and so on.

The IBPMDBeforeUpdate hook is called before the changes in the master data worksheet are sent to the back end. It can be used, for example, to validate that naming conventions have been followed before saving data. This can be very useful when there is a significant number of user-defined attributes in a master data type and several members of the user community are maintaining these fields as part of their routine.

VBA hooks must be first activated so they can be used in a VBA custom code.

Use the following global configuration parameters in parameter group PLAN_VIEW to enable your own Visual Basic for Applications (VBA) scripts:

• ACTIVATE_VBA_HOOKS
• ACTIVATE_MD_VBA_HOOKS

  The default value is NONE. Values MANDATORY (If there is no implementation available, the SAP IBP code stops) and OPTIONAL (If there is no implementation of the VBA hook available, the SAP Integrated Business Planning for Supply Chain code continues running) are possible for this parameter.

Specifically for VBA hooks for master data workbooks, the code must be part of an XLAM add-in. When a master data workbook is saved as a favorite, the SAP IBP, add-in for Microsoft Excel only saves the configuration of the worksheets and generates the workbook on the fly when it is opened next. The Microsoft Excel workbook binary is not saved with the master data favorites, which means that additional sheets and VBA code are not kept.

SAP_IBP_VBA_Hook.xlam can be downloaded from SAP MARKETPLACE, similar to the SAP IBP, add-in for Microsoft Excel or Chart.xlam file. Then the file has to be activated as an Excel Add-in.

Using VBA hooks may have a detrimental effect on performance.

The local machine resources, the amount of data being processed, and the VBA code implementation may impact the overall performance on client machines. You can enable a performance trace on client machines to analyze the execution times of VBA hooks. If the execution time is longer, consider improving the implementation of the parts that are not performing well and also reduce the amount of data to be processed.

In one of the preceding chapters, we discussed generating logs and traces.

Keywords in the trace file PerfTrace_nnnn.gl that are associated with VBA hooks are as follows:

12345678910
IBPBeforeSend
Method [S&OP: IBPVBAHook (SAVE)]
Method [S&OP: IBPVBAHook (CREATE_SIMULATION)]
Method [S&OP: IBPVBAHook (SIMULATE)]
IBPAfterRefresh
Method [S&OP: IBPVBAHook (REFRESH)]
IBPMDAfterRefresh
Method [S&OP: IBPVBAHook (MASTERDATA_AFTER_REFRESH)]
IBPMDBeforeUpdate
Method [S&OP: IBPVBAHook (MASTERDATA_BEFORE_SAVE)]