Exploring the Analysis functions – Part 1

The Analysis Add-in exposes a number of documented and undocumented functions, but how can we find out more about the functions that exist, and the arguments that they’ll accept? Be warned, this gets technical.

Excel has, since the very early days (think Excel 4 Macros and REGISTER commands, rather than VBA Declare Functions), allowed external functions to be registered and used within Excel. The history and technical aspects of these capabilities is beyond the scope of this document, but we’ll be relying on built-in VBA commands and some sparse documentation to discover more about the Analysis functions…..

The following procedure will output the Analysis functions and parameter signatures to the Immediate window in the VBE….

Sub EnumSAPFunctions()

  ‘The default path to the Analysis DLL, in Windows XP, and Analysis 1.4

  Const SBO_DLL As String = “c:\program files\sap businessobjects\analysis\BiXLLFunctions.dll”

  Const INDEX_DLL_PATH As Byte = 1

  Const INDEX_FUNCTION_NAME As Byte = 2

  Const INDEX_FUNCTION_ARGUMENTS As Byte = 3

  Dim aFunctions() As Variant

  Dim iFunctionCounter As Integer

  ‘Fetch an array of registered functions

  aFunctions = Application.RegisteredFunctions

  ‘Check we got an array

  If IsArray(aFunctions) Then

    ‘Loop over each function

    For iFunctionCounter = LBound(aFunctions, 1) To UBound(aFunctions, 1)

      ‘Check the function is from the SBO/Analysis DLL

      If StrComp(aFunctions(iFunctionCounter, INDEX_DLL_PATH), SBO_DLL, vbTextCompare) = 0 Then

        ‘Output the function name and the parameter/argument string

        Debug.Print aFunctions(iFunctionCounter, INDEX_FUNCTION_NAME), _

                    aFunctions(iFunctionCounter, INDEX_FUNCTION_ARGUMENTS)

      End If

    Next iFunctionCounter

  End If

End Sub

As of Analysis 1.4, this produces the following output (tidied and sorted alphabetically below, into a table, for ease of reading):

You’ll notice that the function names are suffixed with “_v12” indicating that they’re for use with Excel Version 12 (Excel 2007) or later (because they have argument types that are only valid in 2007 or later). You’ll also notice that there are some functions that aren’t mentioned in the Analysis documentation, like ApplyStyleForRanges_v12, and FormatNumberForRanges_v12.

The second column might seem a little more cryptic… It details the stuctures/types of the return value type, and the number and type of arguments that each function can accept. You can find more information about these argument structure codes here, but in short, “J” means the value is a signed 32-bit integer, “U” means the value can be “Values, arrays and range references” and “C%” means the argument must be a “Null-terminated Unicode wide-char string”. The first structure code indicates the return type, while subsequent structure codes indicate the argument types.

For example, the SAPGetInfoLabel function is registered as follows: SAPGetInfoLabel_v12, UC%

The structure signature, “UC%C%” is interpreted as follows:

U – The return value will be of type “Values, arrays and range references”

C% – The first and only parameter is a “Null-terminated Unicode wide-char string”

See Part 2 for information on discovering more about the Analysis functions.