Analysis Office 1.1 - Using the VBA APIs - Hints for robust implementation

[olaf_fischer]

Introduction

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
      End If
    Next   

End Function

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.

Dim lResult
 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")
 End If 

2. For functions like SAPGetCellInfo, SAPGetProperty, unsuccessful execution is indicated by the return type “Error” – which can be checked using the isError() VBA-function.  

Dim lVar
 lVar = Application.Run("SAPGetCellInfo", ActiveCell, "DIMENSION")
 If IsError(lVar) Then
  MsgBox ("Could not retreive any Information for the Active Cell")
 End If 

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
  Dim result
  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!")
    End If
  End If

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.