Integration with Visual Basic for Applications

You can add VBA (Visual Basic for Applications) functions to the BEx Analyzer. You then control functions of the BEx Analyzer from VBA routines. The VBA functions delivered by SAP can be found in the Visual Basic Editor in module xBEXapi.

Note that not all of the VBA functions of the BEx Analyzer 3.x are available. Most of these functions were integrated into design mode and formula mode. You should therefore use these integrated functions before using the VBA functions.

When migrating workbooks, this means that only VBA functions that are also available with SAP NetWeaver 7.0 can be migrated. A manual adjustment is required for the other functions.

More information:

Design Mode

Working in Formula Mode

The Visual Basic Editor provides the following VBA functions in module xBEXapi:

• Function SAPBEXgetWorkbookID(wbName As String) As String

  Determine the ID under which a workbook is saved.

  The workbook must be opened in Excel; its current name in the collection Application. Workbooks must be entered in the parameter wbName. Note that the technical name of the workbook generally differs from the title in the window.

  The function returns an empty string if the workbook is not found in the collection Application Workbooks, or if it is established that it has not yet been saved.

• Function SAPBEXreadWorkbook(wbID As String) As String

  Read a workbook from Favorites or Roles.

  The ID defined for the workbook in the Favorites or Roles must be entered in the parameter wbID.

  You can use the function SAPBEXgetWorkbookID to determine a workbook ID. If the reading process was successful, the function returns the Excel name of the open workbook as the result. Otherwise it returns an empty string.

  If no connection to a server exists, the function starts a logon dialog.

• Function SAPBEXsaveWorkbook(wbName As String) As Integer

  Save a workbook in Favorites or Roles.

  In the parameter wbname, you must enter the Excel name of the workbook you want to save. When you save, the Save dialog appears, in which you can select a folder in the Favorites or Roles.

  You cannot save the workbook without the dialog. This is particularly important when you precalculate the workbook with the BEx Precalculation Server.

  If no connection to a server exists, the function starts a logon dialog.

• Function SAPBEXgetErrorText() As String

  This return code can be interpreted as an error code for all API functions that provide a result of type integer:

  • -1Context error; the function cannot be used on the cell in question
  • 0  The function was executed without errors
  • > 0     An error occurred in the function

  In the last case, the text for the error can then be requested with SAPBEXgetErrorText (until the next call of an API function).

• Function SAPBEXsetFilterValue(intValue As String, Optional hierValue As String, Optional atCell As Range) As Integer

  Filter for cell atCell (if this parameter is missing, then the active cell is taken as the basis)

  The internal filter value must be entered in the parameterintValue. Note that this value can be different from the external value displayed in the filter cell. You can either use SAPBEXgetFilterValue or carry out the filter process once manually with the log book function switched on in order to determine the internal value for a known external value. The internal filter value is noted in the log.

  Enter the parameter hierValue if the filter value is a node of a certain characteristic hierarchy.

  "intValue = <blank>" means "do not filter anymore"

  If no connection to a server exists, the function starts a logon dialog.

  The function results in a context error if atCellis not a filter cell.

• Function SAPBEXgetFilterValue(intValue As String, hierValue As String, Optional atCell As Range) As Integer

  Determine the internal filter value(and, where necessary, the characteristic hierarchy used) for cell atCell (if this parameter is missing, then the active cell is taken as the basis).

  The result is returned in the parameters intValue and hierValue, which you subsequently have to pass to "byRef". As no byRef parameter transfer is supported when you call this function with the Excel method "Run", both values can also be queried using the help functions SAPBEXgetFilterValue_intValue and SAPBEXgetFilterValue_hierValue after calling SAPBEXgetFilterValue, for example.:

if run("SAPBEX.XLA!SAPBEXgetFilterValue", myValue, myHier) = 0 then

myValue = run("SAPBEX.XLA!SAPBEXgetFilterValue_intValue")

endif

The function results in a context error if atCellis not a filter cell.

• Function SAPBEXsetDrillState(newState As Integer, Optional atCell As Range) As Integer

  Change drilldown state for cell atCell (if this parameter is missing, the active cell is used as the basis).

  "newState = 0" means       "do not drilldown anymore"

  "newState = 1" means       "drilldown vertically""

  "newState = 2" means       "drilldown horizontally"

  If no connection to a server exists, the function starts a logon dialog.

  The function results in a context error if atCellis not a filter cell.

• Function SAPBEXgetDrillState(currentState As Integer, Optional atCell As Range) As Integer

  Determine the drilldown state at cell atCell (if this parameter is missing, the active cell is used as the basis).

  The result is returned in the parameter currentState, which must subsequently be transferred to "byRef". As no byRef parameter transfer is supported when calling up this function with the Excel method "Run", the value can also be queried after calling SAPBEXgetDrillState using the help function SAPBEXgetDrillState_currentState (see SAPBEXgetFilterValue).

  The function results in a context error if atCell is not a filter cell.