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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply