By Dr. Marco Sisfontes-Monge, Managing Partner, Arellius Enterprises, Inc.

There is a lot of material available to explain enhancements made to improve MS Excel Visual Basic for Applications (VBA) methods, procedures, and commands in order to create a better bridge between MS Excel and SAP Business Planning and Consolidation itself. Especially if you have coded only ABAP programs, the migration to MS Excel VBA and its integration with SAP Business Planning and Consolidation requires a learning curve. The purpose of this article is to provide some quick examples to help you understand SAP BusinessObjects Analysis for Microsoft Office for SAP Business Planning and Consolidation. Readers will also learn how to leverage the VBA tools available in order to improve the end-user experience and provide further flexibility that the traditional BEx queries and SAP BusinessObjects Analysis for Microsoft Office functions do not provide. We will briefly review the overall application.

Significant changes have occurred with the integration between SAP S/4HANA embedded analytics and SAP Business Planning and Consolidation. Similar to the previous EPM version for Microsoft (MS) Excel Reporting, the newer versions of SAP Business Planning and Consolidation are oriented towards SAP BusinessObjects Analysis for Microsoft Office, within the same MS Excel environment. Before you get started, assess that your system, design, and architecture have been optimized for speed and that there are not unnecessary processes or programs that slow down your system. If there are, VBA might slow down your system even more. It all depends on your current architecture and the latest SAP recommendations found in the different OSS notes.

Four Steps to Work with SAP BusinessObjects Analysis for Microsoft Office , MS Excel, and VBA

Step 1: Create and insert your BEx query following the standard procedures for SAP Business Planning and Consolidation (Figure 1). The how and why for creating the SAP BusinessObjects Analysis for Microsoft Office query shown in Figure 1 won’t be discussed in this article for purposes of brevity. Follow up with your SAP BusinessObjects Analysis for Microsoft Office / SAP Business Planning and Consolidation team for further clarifications.

Step 2: Identify what you want to do with your end-users and/or management team. Some examples are described as follows:

• We would like to display ALL company codes, functional areas, and cost centers available in the system without using the standard prompt buttons, and both multiple selection and single selection of variables must be allowed within MS Excel.
• Also for all dropdown multi-selections, both the technical name and the description must also be made available to the user, and it must be linked real-time to the master data for the respective dimensions.
• The end-user would like to change the hierarchies displayed using a drop down for both dimensions GL account and cost center hierarchy
• Since we are working in a multinational corporation, exchange rates are very important, and the conversion based on FX rate year, and FX rate type must also be allowed to the user, this will help to convert the data from the query into one single currency using the standard SAP S/4HANA exchange rate tables.

Step 3: You need to first create BEx variables following the rules accordingly, it is single entry or multi-entry following the standard variable definitions from SAP BusinessObjects Analysis for Microsoft Office. Again, this is straight forward procedure, and it is recommended that you do further reading in the online help manuals from SAP if required.

Step 4: In the following sections we will be building the MS Excel interface with VBA, and there is some code required so each logic will be broken down.

Accessing the MS Excel Environment Fast Track

For purposes of this document we will explain briefly how VBA works for the purist SAP developers. VBA for applications like ABAP have libraries and functions, and can interface with BEx variables as mentioned previously. First, we need to have the developer tab available in your MS excel environment, similar to the SAP BusinessObjects Analysis for Microsoft Office tabs, such as analysis, analysis design, EPM, and data manager for SAP BusinessObjects Analysis for Microsoft Office. Just right click on the top ribbon to access the “Customize the Ribbon” menu, as shown in Figure 2.

After this step happens, the screen below appears. Click on Customize Ribbon and on the right-hand side click on the checkbox Developer as shown, and click OK, and then the Developer Tab would be added, as shown in Figure 3.

Now, when opening the MS Excel VBA environment,  BEx SAP BusinessObjects Analysis for Microsoft Office Queries will show up on the top left corner in the “Microsoft Excel Objects” section when available, and any custom modules that are nothing more than procedures called by some of the sheets, or programs, are called Modules. As you see on the right-hand side of the screen there is code that will be explained shortly and that’s the location where you write variables, statements, programs, loops, etc., as shown in Figure 4.

Setting up the Company Code Dimension List Box: The Basics

Figure 5 displays the end result that is shown within the MS Excel environment. In the first column we have the company code and the second column is the company code description, and as shown the object will allow multiple selection and the end-user will expect that these selections are reflected In the BEx Query shown In Figure 1. To do this, we will create VBA code that will reside In the Worksheet 2 object In the VBA environment displayed In Figure 4.

Key design issues:

• Create another reference Worksheet tab as reference to create and extract your data, and this master data will be used to be read by your objects in a separate tab.
• In that sheet type or extract the data using SAPlistofMembers() function or manually type the master data required on the list or create a BEx query that populates the master data value that you need.
• Also create a Multiselection Variable in the SAP BusinessObjects Analysis for Microsoft Office BEx Query following the standard SAP BusinessObjects Analysis for Microsoft Office procedures. The key part is it must be a multiselection VARIABLE so the query will respond accordingly, otherwise, the VBA will show the multiple selection but only a single Master Data value would be selected. This is directly related to the type of variable created.
• Now, VBA will leverage the query definitions and integrate them with MS Excel environment as follows:
  • In Design Mode, we have set up a regular description in Column A to inform the user what is the variable, in this case Company Code with Multiple Selection.
  • Then, we need to select a reference cell where the values selected in the List Box would be stored, in this case it is B10 (the values 1012;1014) are displayed.
  • Next, we need to define the Range that feeds the List Box for displaying the values. For this we need to work with the code stored behind the scenes and reflected with the EMBED (“Forms.ListBox.1”,””). In this case, the Object name for the List Box is ListBox.1 as shown in Figure 6.

Now in Figure 6, we have the List Box 1 object, and we access the properties. The two key elements are the LinkedCell: B10, which is nothing more than the target cell that will store the selections in the List Box, in this case, the Company Code Numbers. Also, the SOURCE of the data displayed in the ListFillRange, which is the property that will control the list of values displayed. In this example, we are using a Worksheet called REFERENCE, and we are extracting the data from the Range J4:K91 for the list of company codes. Remember, you can use SAP BusinessObjects Analysis for Microsoft Office functions or manual entry or to make it real-time, you can create another query in the specified range insert it. Finally, the key part of the List Box is the display of the two columns, for that you need to set the property ColumnCount = 2, which brings the headers or Row 1 for the Range J4:K91 as shown in Figure 7.

The Code: Part 1

MS Excel VBA code can become very complex, like any other system believe it or not. It could have performance implications if not performed correctly, and certainly like any other programming language there are multiple options that affect performance and speed, and the procedure might change as complexity increases. The code will be stored in the Workbook (Sheet2) both Part 1 and Part 2. Part 1 code is directed to show and display values, and Part 2 is the one that transfers the selections in Part 1 to the SAP BusinessObjects Analysis for Microsoft Office Query.  Also, Code Part 1 Is required for EACH List Box or Combo Box or object you will be working with and It Is specific for each of them. However, Code Part 2 Is the only method or VBA code that can manage the behavior for ALL Objects (List Box 1, List Box 2, Combo Box 1, etc.) since It Is a single method applied per Worksheet.

For this reason, I created a simplified and direct code that you can use as template in order to implement it in any project as soon as TODAY and you must include it in a Worksheet code. The code has been designed to update the query linked to your List Box 1 or to the number that you desire to assign to your Object, and it will update the query after loosing “Focus” or after clicking outside the List Box.

Private Sub ListBox1_LostFocus()

Dim lngCurrentItem As Long

Dim strCurrentItem As String

Dim strAllSelectedItems As String

Dim rngOutput As Range

Me.Range(“B10”).Value = Me.ListBox1.Value

‘Me.ListBox1.Height = 81.5 ‘(controls the height of the Listbox object)

‘Me.ListBox1.Width = 325.5 ‘(Controls the width of the Listbox object)

Set rngOutput = [B10]

‘strAllSelectedItems = “”

For i = 0 To ListBox1.ListCount – 1

strCurrentItem = ListBox1.List(i)

If ListBox1.Selected(i) Then

If strAllSelectedItems = “” Then

strAllSelectedItems = strCurrentItem

Else

strAllSelectedItems = strAllSelectedItems & “; ” & strCurrentItem

End If

End If

Next i

If strAllSelectedItems = “” Then

rngOutput = “No Items Selected”

ElseIf InStr(1, strAllSelectedItems, ” – “, vbTextCompare) > 0 Then

rngOutput = strAllSelectedItems & ” Are Selected”

‘ Me.Range(“B10”).Value = Me.ListBox1.Value

Else

rngOutput = strAllSelectedItems

End If

End SubThe items highlighted in BOLD are the only ones that you need to worry about, do not worry about the code, so much, just updating the following key naming conventions:

• Name of the Listbox, in this case ListBox1 must be updated in the code everywhere for the correct behavior. Your ListBox could have other names or ListBox2, etc.
• Reference Cell: as we mentioned the reference cell where the values would be stored is “B10” so updated it accordingly based on your selected cell.

At this point, the only code we have introduced is to allow a List Box to display the values for a company code or a list of values, and store the selections into a cell. The second part of the code will use the value stored in cell B10, and now transfer it to a BPC/BEx variable using SAP BusinessObjects Analysis for Microsoft Office , and that it was defined from the beginning when the query was created. Remember, the VBA code cannot transfer or capture or interact with variables that do not exist in the BEx query, and thus your values displayed in the query can only be changed if there are SAP BusinessObjects Analysis for Microsoft Office/BEx variables defined.

The Code: Part 2

Now as mentioned, we have created an interface that displays company codes and display them into a list box. Now we need that the selections, the values of this multiple company code selections, be transfer to a BEx/SAP BusinessObjects Analysis for Microsoft Office variable as part of a SAP BusinessObjects Analysis for Microsoft Office Query.  In order to read a specific cell, we will implement a Worksheet_Change(ByVal Target As Range) Sub inside the Worksheet2. Notice in the following code we are updating the SAP S/4 HANA dimension /ERP/P_COMPCODE01 with the MS VBA code.

The bolded code are where you need to update your code to your cell that you are storing the values for the company code selection screen. Similarly to code Part 1, I have created a template for you to use, and just replace the key elements to your project. An important difference here is Part 1 code must be created for EACH dimension that you will be creating or retrieving, but Part 2 code is required only ONCE so all your dimensions or variables to be updated must be controlled within this code, the only thing you need to change is the cells and the data source (“DS_1”) in the current case. The other important factor to remember is that the first section of the code must also be consistent so you will store the all List Boxes but the values store must be stored in a similar range, in this case we are storing all values for the objects in the Range(“B10:B21”).

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range(“B10:B21“)) Is Nothing Then

‘ CalculateSalary (Target.Row)

‘ End If

Dim lMsg As Long

Dim lResult As Long

‘Updates invisible

Application.ScreenUpdating = False

lResult = Application.Run(“SAPSetRefreshBehaviour”, “Off”)

‘Companycode

lResult = Application.Run(“SAPSetVariable”, “/ERP/P_COMPCODE01”, Range(“B10“).Value, “INPUT_STRING”, “DS_1”)

‘ ADD THE OTHER DIMENSIONS HERE WITH THE SAME FORMAT, and we are updating the Variable ‘Names and they must be aligned to be multiple selection or single selection

‘lResult = Application.Run(“SAPSetVariable”, “/ERP/P_COMPCODE01”, Range(“B10”).Value, “INPUT_STRING”, “DS_1”)

If lResult = 1 Then

lMsg = Application.Run(“SAPAddMessage”, “The prompt was set.”, “INFORMATION”)

Else

lMsg = Application.Run(“SAPAddMessage”, “The prompt could not be set.”, “ERROR”)

End If

‘ Updates visible

Application.ScreenUpdating = True

lResult = Application.Run(“SAPSetRefreshBehaviour”, “On”)

End If

End Sub

Final Results

In summary, implementing  Code Part 1 and Code Part 2 in a List Box 1 will allow you to select multiple company codes and transfer the values to the  SAP BusinessObjects Analysis for Microsoft Office /BEx query that you are working in. If the variables and code are updated accordingly, it does not matter if you are working with a Report or Input Form. In the example that we have discussed, we just maintained only one Dimension Company Code, and as shown in Figure 8, we are able to perform multiple selection on the company code object, and that will be transferred into the query results by Code Part 2.

As shown in Figure 8, we perform and enhance the code even more to include more dimensions such as Cost Center and Functional Area. In order to do this, you need to copy Code Part 1 In the SAME Worksheet 2 where the previous code was created, and change the names of the objects and cells. For example, if we want to create Cost Center List Box selection, it will be called List Box 2, and it will be in different Cell Range, and update the items in Bold previously defined. Do the same procedure to add the Functional Area.

Remember, Code Part 2 is the one that must be enhanced to include the functional area and the cost center as noted above. You don’t need to create a new procedure. You need new procedures only for Code Part 1 for the new List Box 2 and List Box 3 for the Cost Center and Functional Area, respectively.

Certainly, the two programs provided to you as templates for your development are more useful to your end-users in comparison with the traditional code that displays ALL prompts and variables available in the query. Most users do not like to work with the standard prompt option or display SAP BusinessObjects Analysis for Microsoft Office since it might get too busy and complex, if you have multiple variables. With the code provided now the end-user can change variables one at a time in a more user-friendly environment without the need to use the code shown below attached to a button:

Sub Prompts()

Dim lResult As Long

lResult = Application.Run(“SAPExecuteCommand”, “ShowPrompts”, “ALL”)

End Sub

I hope with this procedure will be useful to improve your SAP BusinessObjects Analysis for Microsoft Office Workbooks, and improve your reporting experience to your end-user community.