After handing your nice and helpful custom workbook, using the API of Analysis Office, over to your users/testers, you receive an e-mail with the following screenshot.
An error occurred … was has happened here?
The custom workbook tries to access the API of Analysis Office without success. The first place that you might want to look is the spelling of the macros. Another typical reason is that Analysis Office is not installed and active on the user’s PC. A robust implementation would search for an Analysis Office Add-in and (in case it is not available) provides a comprehensive prompt like “Please install/activate the Analysis Office Add-in”.
Here is another common pitfall. A macro in the custom workbook performs the following harmless(?) statement …
Dim myArray() As String
myArray = Application.Run("SAPGetCellInfo", ActiveCell, "DIMENSION")
It works fine for the developer, but the user/tester sees the following message:
The root cause is that the implementation expects a string array as the return type – which is unfortunately not always the case; such as when an un-successful execution returns the “Error”.
The following code snippets illustrate how to avoid such situations. Using them will improve the robustness of your implementation.
Is there an Analysis Office Add-in available and active?
Before you can access any API of Analysis Office the add-in must be installed and started. The following code snippet searches through the list of available add-ins. If Analysis Office is found, the snippet ensures that is activated.
Public Function SetAOAddinActive() As Boolean
Dim addin As COMAddIn
SetAOAddinActive = False
For Each addin In Application.COMAddIns
If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then
If addin.Connect = False Then addin.Connect = True
SetAOAddinActive = True
Has the API command executed successfully?
Presuming now that Analysis Office Add-in is active, there are two technical kinds of API calls. One returns just an indication of whether the command was successful or not. The others return data (functions returning a result). Depending on the type the check for successful execution looks slightly different.
1. For commands like SAPSetFilter, SAPSetVariable, unsuccessful execution is indicated by a return value unequal to 1.
lResult = Application.Run("SAPSetFilter", "DS_1", "0SOLD_TO__0COUNTRY", "CA;US;DE", "INPUT_STRING")
If lResult <> 1 Then
MsgBox ("Could not set the filter values")
2. For functions like SAPGetCellInfo, SAPGetProperty, unsuccessful execution is indicated by the return type “Error” – which can be checked using the isError() VBA-function.
lVar = Application.Run("SAPGetCellInfo", ActiveCell, "DIMENSION")
If IsError(lVar) Then
MsgBox ("Could not retreive any Information for the Active Cell")
Unsuccessful execution – understand the root cause
Let’s presume that the execution of the SAPSetFilter command was not successful. One common reason is that the data source is not refreshed/active yet. The check might look like this one, which prompts the user to refresh the data source:
' check if data source is active
result = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")
If IsError(result) = False Then
If result = False Then
MsgBox ("The data source 'DS_1' is inactive - please refresh!")
Instead of prompting the user you also could try the refresh of the data source via API and then re-execute the SAPSetFilter statement. From user perspective this would solve the root cause.