Exploring the Analysis functions - Part 1

[Former Member]

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):

ApplyStyleForRanges_v12	JUUUUUU
BExGetData_v12	UC%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%
CallbackAfterWorkbookClosed_v12	J
CallbackFddIconClicked_v12	J
CallbackHeaderCellDropDownIconClicked_v12	J
CallbackSaveWorkbook_v12	J
CallbackWorkbookSaved_v12	J
FormatNumberForRanges_v12	JUUUUUU
SAPAddMessage_v12	JC%C%C%
SAPCallMemberSelector_v12	UC%C%C%UC%
SAPExecuteCommand_v12	JC%C%C%
SAPExecutePlanningFunction_v12	JC%
SAPExecutePlanningSequence_v12	JC%
SAPGetCellInfo_v12	UUC%C%
SAPGetData_v12	UC%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%
SAPGetDimensionDynamicFilter_v12	UC%C%C%
SAPGetDimensionEffectiveFilter_v12	UC%C%C%
SAPGetDimensionInfo_v12	UC%C%C%C%
SAPGetDimensionStaticFilter_v12	UC%C%C%
SAPGetDisplayedMeasures_v12	UC%
SAPGetInfoLabel_v12	UC%
SAPGetMeasureFilter_v12	UC%
SAPGetMember_v12	UC%C%C%
SAPGetProperty_v12	UC%C%
SAPGetSourceInfo_v12	UC%C%
SAPGetUniformScaling_v12	UC%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%C%
SAPGetVariable_v12	UC%C%C%
SAPGetWorkbookInfo_v12	UC%
SAPListOf_v12	UC%C%C%
SAPListOfDimensions_v12	UC%C%C%
SAPListOfDynamicFilters_v12	UC%C%
SAPListOfEffectiveFilters_v12	UC%C%
SAPListOfMembers_v12	UC%C%C%C%C%C%
SAPListOfMessages_v12	UC%C%
SAPListOfStaticFilters_v12	UC%C%
SAPListOfVariables_v12	UC%C%C%
SAPLogon_v12	JC%C%C%C%C%
SAPMoveDimension_v12	JC%C%C%C%
SAPSetFilter_v12	JC%C%UC%
SAPSetFilterComponent_v12	UC%C%C%C%
SAPSetPlanParameter_v12	JC%C%UC%
SAPSetRefreshBehaviour_v12	JC%C%
SAPSetVariable_v12	JC%UC%C%

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.