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
Hello Martin - thank you again for doing this.
At SAP TechEd this week, we had an open ASUG session for Analysis Office - one of the attendees from Texas asked for best VBA practices and I mentioned your work here on SCN.
Thank you again - your post and contribution is very timely.
Hi Martin, thanks for that!
Do you also have a how-to if I want to insert a new Column into a Workbook, that has already a BEx Query inserted? The new Column should be ready to enter Values manually and save it to the Workbook.
I'm sorry but I don't really understand your question.
In IP, every input ready cell needs a 100% restriction of characteristics and key figure otherwise the cell remains closed.
How do you think, the new column would be restricted in the Bex Query?
Please post this as question (including example or screenshot) here:
Created a new discussion.
Thanks a lot.
Thank you, Martin. I often get asked for this feature by our power users. I will share and let you know if I have any further questions.
I cannot get my code to execute. I selected Refresh All Datasources and it still does not execute. Any ideas on why this is not working?
not knowing your exact code I don't have an answer...
If you don't see any VBA error (which you could debug), you can used that line directly after any AO function. E.g.:
Dim VlastError As Variant
lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")
VlastError = Application.Run("SAPGetProperty", "LastError")
In the debugger you can see an error no. & message if it did not work.