Using Analysis functions
You can use the Analysis functions in VBA macros. The return value of a formula in a macro corresponds to the return value of the formula used in a Microsoft Excel sheet. Depending on the function that you use, a formula can either return a single value (single string value) or a list (array). If the list contains only one line, the returning array is one-dimensional. If the list contains two or more lines the array is two-dimensional.
If you use a formula in a macro, the function is the first parameter followed by the arguments you use to create a formula with this function.
If a formula is invalid, Microsoft Excel returns an error.
Example
Dim lResult As Variant
lResult = Application.Run("SAPGetVariable", "DS_2", "0BW_VAR", "Value")
This formula returns the current value of variable 0BW_VAR.
Example
Dim lResult As Variant
lResult = Application.Run("SAPListOfDimensions", "DS_1")
This formula returns a list with the dimensions of data source DS_1. If the data source contains only one dimension, the returned array is one-dimensional.
To make your programming easier, you can create an additional function to ensure that the array is always two-dimensional, for example the function GetAsTwoDimArray.
Function GetAsTwoDimArray(value As Variant) As Variant 'value is error return the error If IsError(value) Then GetAsTwoDimArray = value 'value is array ElseIf IsArray(value) Then 'first check if the array is two-dimensional 'by requesting the upper bound of the 2nd dimension. 'if this is not the case an error occurs (Err.Number <> 0). 'ignore errors, handled locally On Error Resume Next Dim lIndex As Integer Dim lErrorCode As Integer lIndex = UBound(value,2) lErrorCode = Err.Number 'set error handling back to default On Error GoTo 0 If lErrorCode = 0 Then 'no error: array is two-dimensional GetAsTwoDimArray = value Else 'copy one-dimensional array into a two-dimensional one Dim i As Integer Dim lArray() As Variant ReDim lArray(1 To 1, 1 To UBound(value)) For i = 1 To UBound(lArray, 2) lArray(1, i) = value(i) Next GetAsTwoDimArray = lArray End If Else 'return Empty GetAsTwoDimArray = Empty End If End Function
In the following example, a list of all dimensions is returned from function SAPListOfDimensions. All dimensions which are neither on the rows nor on the columns axis are added to a string that is displayed in the standard message dialog with the API method SAPAddMessage. Using the function GetAsTwoDimArray, you ensure that the returned array is always two-dimensional.
Public Sub ShowDimensionsNotOnRowsOrColumns()
Dim lList As String
Dim lResult As Variant
lResult = Application.Run("SAPListOfDimensions", "DS_1")
lResult = GetAsTwoDimArray(lResult)
For i = 1 To UBound(lResult, 1)
If lResult(i, 3) <> "ROWS" And lResult(i, 3) <> "COLUMNS" Then
lList = lList & " " & lResult(i, 2)
End If
Next i
Call Application.Run("SAPAddMessage", "Dimensions:" & lList, "INFORMATION")
End Sub