Skip to Content
Author's profile photo Dirk Mayrock

VBA: Create embedded help for writing Analysis Office VBA commands – PART 2


in my first part of that series I described how to create a .bas file with functions in it to support writing Analysis Office APIs more easy.

Feel free to download file attached there as a basis for this second step that makes it another step more simple to use the whole VBA power of Anaylsis Office.

The result of the first blog was that you could see the parameters you need for a command like that example here for the SAPSetFilter API


As you can see when you come to the point to enter the parameter iMemberFormat you still have to know / look up that the available options for that parameter are “Key”, “Text”, “Internal_Key” or “Input_String”.

To enable these predefined values as an selectable option we will use that Excel functionality called ENUM objects. For that we will define the list of available options for that parameter like that

Public Enum MemberFormat
End Enum

and in the definition of our SAP_SetFilter Routine we change the code to

Public Function SAP_SetFilter(iDataSource As String, iDimension As String, iMember As String, iMemberFormat As MemberFormat) As Integer
    Dim lMemberFormat As String
    If iMemberFormat = Key Then lMemberFormat = "Key"
    If iMemberFormat = Text Then lMemberFormat = "Text"
    If iMemberFormat = Internal_Key Then lMemberFormat = "INTERNAL_KEY"
    If iMemberFormat = Input_String Then lMemberFormat = "INPUT_STRING"
    SAP_SetFilter = Application.Run("SAPSetFilter", iDataSource, iDimension, iMember, lMemberFormat)
End Function

You see that in the SAP_SetFilter we define a new variable lMemberFormat that we fill with the corresponding value we need. You might ask yourself why we have to do the “conversion”. The reason is simple and you will see it when you debug your code in detail. The value of the ENUM object is not transferred as the selected string but as the number of the entry e.g. 1 for Text and 2 for Key.

As a result you will get the list of available options for the MemberFormat parameter while coding your SAP_SetFilter


With that in mind we could also think of defining the ENUM object with expressions we like or in the language the user needs them and in our functions we convert that to the parameters the API needs. Its a little bit like a “semantic layer” that enables us to separate the expressions the user wants to use from the ones the technical API needs. Regarding language… of course you can also “translate” the function name to an expression that fits to your language like a translated version of the SAP_SetFilter to German…


Hope you enjoy my litte series about getting closer to the very good supported scripting style in design_studio.

Feel free to send feedback or enhanced versions of the file.

All the best


Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Dirk,

      thanks for adding the second part. I really like that support that I get now while coding vba in Analysis.