AO Workbook Template for dynamic formula rows

[Former Member]

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

End Sub

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

'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