AO Workbook Template for dynamic formula rows
Dear AO users,
After having finished my document: “Best Practices for VBA in SAP BI Analysis for MS Excel”, I created a new workbook with custom formulas and so decided to prepare a kind of template which can be re-used for similar requirements.
Here, I will demonstrate the steps which can or should be executed to create a proper VBA coding to realize a dynamic row update with individual excel fomulas.
Basic idea is that I included a Datasource (BEX Query) and now want to include dynamic formulas in the columns in front of the actual crosstab.
The formulas are supposed to update all rows which are filled in the cross-tab by BW data after each query refresh.
In addition, a manual headline is to be included for all characteristics with Key & Text display, like here missing for the Region text:
I will skip the simple part of how to include a datasource and start with the VBA part:
Step1 – Include the “Callback after Redisplay”
Include the following coding into “ThisWorkbook”:
Public Sub Workbook_SAP_Initialize()
‘ register callbacks
Call Application.Run(“SAPExecuteCommand”, “RegisterCallback”, “AfterRedisplay”, “Callback_AfterRedisplay”)
Step 2 – Start your Modul1 Coding
First I will freeze the screen so that the users will see only the final result:
Public Sub Callback_AfterRedisplay()
Application.ScreenUpdating = False
Then we need some variables:
Dim lResult As Long
Dim i As Integer ‘ Headerline counter
Dim DS1_colend As Variant
Dim DS1_rowend As Variant
‘ Formula variables
Dim DS1_Formula1, DS1_Formula2, DS1_Formula3 As Variant
‘ Set the sheet name, in case more than one DS are be used
Dim DS1 As String
DS1 = “Example”
As you can see, I specified most variable names depending to “DS_1”.
In case you are going to have more queries included in the workbook this helps to understand the coding quickly.
Step 3 – Start the action
The Callback is registered initially on workbook open and with every change in a connected Datasource my VBA coding will be executed.
For testing purposes and due to the fact that the Crosstab has no real “size” if it is not refreshed, my whole coding is running only, if the Datasource is active:
lResult = Application.Run(“SAPGetProperty”, “IsDataSourceActive”, “DS_1”)
If lResult = True Then
‘ determine last column
DS1_colend = Range(“SAPCrosstab1”).Columns.Count
‘ determine last row
DS1_rowend = Range(“SAPCrosstab1”).Rows.Count
Step 4 – Fill the header line
This step is optional, you can also skip it if not relevant for you:
‘ Fill headerline
For i = 1 To DS1_colend
If i < DS1_colend – 1 Then
If Worksheets(DS1).Range(“A5”).Offset(0, i + 1) = “” Then
Worksheets(DS1).Range(“A5”).Offset(0, i + 1) = Worksheets(DS1).Range(“A5”).Offset(0, i) & “2”
Step 5 – Determine, Clear, Fill the custom formula cells
‘ Start with clearing of old formula area, in case selection is smaller
‘ Formula columns A and B
DS1_Formula1 = “=MID(RC,7,4)”
Worksheets(DS1).Range(“A6”).Formula = DS1_Formula1
DS1_Formula2 = “=MID(RC,4,2)”
Worksheets(DS1).Range(“B6”).Formula = DS1_Formula2
‘ Formula Column C: Identify China by Flag = Yes
DS1_Formula3 = “=IF(RC=””05″”,””Yes””,””No””)”
Worksheets(DS1).Range(“C6”).Formula = DS1_Formula3
‘ Set the formulas new
Worksheets(DS1).Range(“A6:” & “C” & 6 + DS1_rowend – 2).Select
Application.CutCopyMode = False
End If ‘ from IsDataSourceActive result
Application.ScreenUpdating = True
I copied the same coding for 4 Datasources and executed the same 4 times after every Redisplay.
So I switched between worksheets, and for whatever reason – I still don’t know – VBA is not able to execute this single statement without error:
I had to include a leading “Select”, like above:
Only then, this worked:
I hope you find this “How-To” helpful…
Best regards, Martin