Skip to Content
Author's profile photo Former Member

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:

Report.png

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”)

End Sub

Reistercallback.png

Step 2 – Start your Modul1 Coding

Function.png

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

‘Crosstab size

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”

            End If

End If

Next i

    

Step 5 – Determine, Clear, Fill the custom formula cells

‘ Start with clearing of old formula area, in case selection is smaller

Worksheets(DS1).Select

Worksheets(DS1).Range(“A6:C6”).Select

Worksheets(DS1).Range(Selection, Selection.End(xlDown)).Select

Selection.Clear

‘ Formula columns A and B

DS1_Formula1 = “=MID(RC[4],7,4)”

Worksheets(DS1).Range(“A6”).Formula = DS1_Formula1

DS1_Formula2 = “=MID(RC[3],4,2)”

Worksheets(DS1).Range(“B6”).Formula = DS1_Formula2

‘ Formula Column C: Identify China by Flag = Yes

DS1_Formula3 = “=IF(RC[3]=””05″”,””Yes””,””No””)”

Worksheets(DS1).Range(“C6”).Formula = DS1_Formula3

‘ Set the formulas new

Worksheets(DS1).Range(“A6:C6”).Select

Selection.Copy

Worksheets(DS1).Range(“A6:” & “C” & 6 + DS1_rowend – 2).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Worksheets(DS1).Range(“A1”).Select

End If ‘ from IsDataSourceActive result

Application.ScreenUpdating = True

End Sub

Final information:


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:

Worksheets(DS1).Range(“A6:C6”).Select

I had to include a leading “Select”, like above:

Worksheets(DS1).Select
Only then, this worked:

Worksheets(DS1).Range(“A6:C6”).Select

I hope you find this “How-To” helpful…

Best regards, Martin

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Tammy Powlas
      Tammy Powlas

      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.

      Tammy

      Author's profile photo Former Member
      Former Member

      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.

      Thanks.

      Kind regards,

      Adrian

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Adrian,

      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:

      http://scn.sap.com/community/businessobjects-analysis-ms-office/content?filterID=contentstatus%5Bpublished%5D~objecttype~showall

      Thanks, Martin

      Author's profile photo Former Member
      Former Member

      Hi Martin

      Created a new discussion.

      Thanks a lot.

      Adrian

      Author's profile photo Lynn Peter
      Lynn Peter

      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.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Sandy,

      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.

      BR, Martin