Skip to Content
Author's profile photo Former Member

Step by step guide: How to Set BEx workbook variables using VBA

      Before starting keep in mind that if you write some incorrect VBA code in BEx workbook and save it in the server, there is a chance that FATAL error will occur and excel will be closed after you click OK or CANCEL in error message window. This error can only be fixed by deleting the workbook and recreating it with new one. So I suggest trying this solution on locally saved workbook first. 

Step 1: 

Open workbook with SAP BEx Analyzer and save it locally.

Step 2:

Create variable list like this in different sheet :

In this example we use only 2 variables but you can define more simply by creating

VAR_NAME_2                        0              Name of variable
VAR_VALUE_EXT_2             0              Value of variable
VAR_NAME_3                        0              Name of variable
VAR_VALUE_EXT_3             0              Value of variable

and so on.

Step 3:

Insert button using BEx Analyzer Add-in:


Step 4:

In Design Mode simply click on inserted button:


Select command type “Workbook-Specific Command”:

Leave as it is and press “Finish”

and set static parameters and click OK:


DATA_PROVIDER – value will show which data provider should be used than variables is processed.
CMD – processed values from cells before submitting them.
SUBCMD – submits variables for data provider.
Command Range – into this text field insert range with your variable list.

Tip: Setting static parameters can get tricky sometimes so double check it.

Step 5: Insert variables and click your created button. Query should now run with your inserted values and Change Variable screen will not appear.

Step 6: To change values you can write macro like this:

When using external source (like C# project):

Sub changeVariables( compCode As String, period As String)
      Sheets(“Sheet1”).Visible = True 
      Range(“C3”).Value = compCode
      Range(“C5”).Value = period

      Sheets(“Sheet1”).Visible = False
End Sub

When using in workbook coded values:

Sub changeVariables()
      Sheets(“Sheet1”).Visible = True 
      Range(“C3”).Value = “9999”
      Range(“C5”).Value = “2013”

      Sheets(“Sheet1”).Visible = False
End Sub

Button_click_39 sub will look like this (this Sub will be generated automatically):

Public Sub BUTTON_39_Click()
      Dim BEx1 As Object
      Set BEx1 = Application.Run(“BExAnalyzer.xla!GetBEx”)
      Call BEx1.RaiseButtonClick(Parent.Name, “BUTTON_39”)
End Sub


This method is not very elegant but does the job. Locally saved workbooks can get variable values from external sources or use coded ones. This solution proves to be stable when running it periodically. This can be used when writing simple report generator which generates physical reports with several languages, currencies and etc.

Hope this helps.

Additional information:
SAP note 1085218

Assigned Tags

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

      This is exactly what I have been looking for but when I create the button and set the static parameters, and then click the button, the variable screen pops up, without my values.

      When I then go into the static parameters again a new cmd with index 3 and SHOW_VARIABLE SCREEN value has been added by itself. The index for DATA_PROVIDER is also changed.

      Can you advise me on how to get past this issue?