Using Callbacks
Analysis offers different callbacks that are executed with certain events. Callback 'Workbook_SAP_Initialize' is always executed. The other callbacks listed below must be registered with the API method 'SAPExecuteCommand' before being used for the first time. To deregister the callbacks, you can also use 'SAPExecuteCommand'.
Every callback can be registered only once in a workbook. If you register one of the callbacks for a second time, the first registration is overwritten.
Callback 'Workbook_SAP_Initialize' has to be defined in the 'ThisWorkbook' section of the VBA editor. The other callbacks should be defined in a module. If they are defined in the 'ThisWorkbook' or a sheet section, they have to be fully referenced during registration. In the 'ThisWorkbook' section, for example, you have to use ThisWorkbook.<MacroName> as a reference.
Use this callback to define the initialization logic of the workbook. This callback is similar to the 'Workbook_Open' event offered by Microsoft Excel, but it is executed after the Microsoft Excel event and after the Analysis standard initialization. If you open a workbook in Microsoft Excel and then activate Analysis, the Excel event is executed when opening the workbook, and the Analysis standard initialization and the callback are executed after the activation of Analysis.
The callback has to be defined as a subroutine without input parameters.
Example
' defined in ThisWorkbook
Public Sub Workbook_SAP_Initialize()
' register callbacks
Call Application.Run("SAPExecuteCommand", "RegisterCallback", "AfterRedisplay", "Callback_AfterRedisplay")
Call Application.Run("SAPExecuteCommand", "RegisterCallback", "BeforePlanDataSave", "Callback_BeforePlanDataSave")
Call Application.Run("SAPExecuteCommand", "RegisterCallback", "BeforePlanDataReset", "Callback_BeforePlanDataReset")
Call Application.Run("SAPExecuteCommand", "RegisterCallback", "BeforeMessageDisplay", "Callback_BeforeMessageDisplay")
Call Application.Run("SAPExecuteCommand", "RegisterCallback", "BeforeFirstPromptsDisplay", "Callback_BeforeFirstPromptsDisplay")
End Sub
The 'AfterRedisplay', 'BeforPlanDataSave' and 'BeforePlanDataReset' callbacks are registered during workbook initialization and can therefore be used in the VBA project.
Use this callback to define the logic that should take place every time the workbook is redisplayed. For example, you can automatically adapt the formatting in the workbook after every redisplay.
The callback has to be defined as subroutine without input parameters. The callback is called only when 'Pause Refresh' on workbook level is false or the user explicitly requested a 'Refresh All'. It is also called only when the active workbook has been changed.
Example
' defined in a module
Public Sub Callback_After Redisplay()
ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value = "Last redisplay: "
ThisWorkbook.Worksheets("Sheet1").Cells(1, 2).Value = Now()
End Sub
With this callback, the text "Last redisplay: " is displayed in cell A1 on sheet 1, and in cell A2, the date and time of the last redisplay are displayed. The information is updated after every redisplay.
Example
With the following code, you can get the crosstabs and data sources that have been changed since the last user action and that therefore now have been updated in the sheets.
An array is returned like for 'SAPListOf' with technical name, name and data source.
lResult= Application.Run("SAPGetProperty", "CHANGEDDATASOURCES")An array is returned like for 'SAPListOf' with technical name and name.
Use this callback to define the logic that should be executed every time a user saves plan data. For example, you can execute a planning function (SAPExecutePlanningFunction).
The callback has to be defined as a function returning a Boolean value without input parameters. If the function returns false, the save will not be executed.
Example
' defined in a module
Public Function Callback_BeforePlanDataSave() As Boolean
Dim lResult As Integer
lResult = Application.Run("SAPExecutePlanningFunction", "PF_1")
If lResult <> 1 Then
' planning function execution failed, cancel save
Call MsgBox("Planning Function (PF_1) execution failed. Data will not be saved.", vbCritical, "Error")
Callback_BeforePlanDataSave = False
Else
Callback_BeforePlanDataSave = True
End If
End Function
Every time a user selects the Save button to save planning data, planning function PF_1 is executed. If the planning function can be executed, the planning data is saved. If the execution fails, a message box with the text "Planning Function (PF_1) execution failed. Data will not be saved." is displayed.
Use this callback to define the logic that should be executed every time a user resets plan data to the previously saved state. For example, you define that the user is asked if the data really should be reset.
The callback has to be defined as a function returning a Boolean value without input parameters. If the function returns 'false', the reset will not be executed.
Example
' defined in a module
Public Function Callback_BeforePlanDataReset() As Boolean
Dim lAnswer As VbMsgBoxResult
lAnswer = MsgBox("Do you really want to reset planning data?", vbYesNo, "Reset")
If lAnswer = vbYes Then
Callback_BeforePlanDataReset = True
Else
Callback_BeforePlanDataReset = False
End If
End Function
Every time a user enters planning data and selects 
Back 
To previous saved state 
, a message box with the text "Do you really want to reset planning
data?" is displayed. If the user selects 'Yes', the entered data is reset to the
previous saved state. If the user selects 'No', the reset is not executed.
Use this callback to specify which messages are displayed in the message dialog.
This callback is called whenever the messages dialog is displayed. You can provide some actions that affect which messages will be displayed. Two typical actions are to suppress one or several messages with SAPSuppressMessage or to add one or several messages with SAPAddMessage.
Example
Public Sub Callback_BeforeMessageDisplay()
Dim messageList As Variant
Dim messages As Variant
Dim lRet As Variant
Dim messageCount As Variant
Dim i As Integer
messageList = Application.Run("SAPListOfMessages", ,"True")
messages = GetAsTwoDimArray(messageList) ' see "Using Analysis functions"
messageCount = UBound(messages, 1)
For i = 1 To messageCount
If messages(i, 5) = "INFORMATION" Then
lRet = Application.Run("SAPSuppressMessage", messages(i, 1))
End If
Next i
End Sub
The function SAPListOfMessages with parameter True lists the messages with detailed information. The details are always listed in a determined sequence.
At position 5, the message severity is listed. The example shows that all messages with severity INFORMATION will be suppressed in the message dialog.
Use this callback to perform operations before the initial display of the prompting dialog. It is triggered when the prompting dialog is called for the first time and when changing the Merge Variables property in the workbook. If the property Force Prompt for Initial Refresh is not selected, variable values are set without displaying the prompting dialog.
You can use this callback to overwrite default values before calling the prompting dialog for the first time, to define a workbook variant in the workbook that automatically fills the variable values or to use dynamic values (e.g. 'today' or 'last week') without defining dedicated variables.
Example
' defined in a module
Public Sub onBeforeFirstPromptsDisplay(dpNames As Variant)
Dim dpName As Variant
For Each dpName In dpNames
If dpName = "DS_1" Then
Call Application.Run("SAPSetVariable", "COUNTRY", "EN", "INPUT_STRING", "DS_1")
End If
Next
End Sub
The variable COUNTRY is set to value EN before the prompting dialog is initially displayed. The input variable dpNames contains all data source aliases which are part of the current refresh processing. In this example, it is only asked for one data source: DS_1.