VBA Macros

[Former Member]

Will macros work even if drilldowns are made with new columns being added and constant changing of the layout of the workbook.

[Former Member · Accepted solution]

Hi Kenneth,

The answer to that question depends very much on how the macros are written.

It is relatively simple to locate the query result table. So, it is relatively simple to know how many rows and columns are in the results.

To locate specific columns, then, you need only look at the (let's say) the first row in the result table to find the headings for characteristics / key figures the macro will need to know what is in each column. Write the macro where the columns are variables instead of hard coded.

The problem starts to come in when the user decides to drill across. Let's say that they want to see KF for each month and they drill across by month.

Now the characteristics / key figures are no longer in the first row; they are in the second row.

There is an API function that can pinpoint the data portion of the result table ... so, if the user decided to drill across by an additional characteristic, you can know that fairly reliably. But, how will you know if they put the new drilled-across characteristic in the first row, with the KF structure in the second row, or they did it the other way around?

All of this COULD be handled in a very-well-designed macro, but it seems like a lot of work and sooner or later someone will find a way to make it break.

What I do is write the macro to handle simple changes, such as addition of more more characteristics drilled DOWN. But, if the macro is unable to find the headings it needs in the row it expected them to be in (relative to the first row of the results table), I give the user a message and quit the macro.

I also publish instructions with each workbook (I usually embed a PowerPoint document right into the workbook) that tells the user what kind of changes they can make and what kind of changes they should not make.

So far (touch wood) this seems to work OK for me. It depends a lot on getting users what they wanted in the first place, so that their customization is minimal.

Let me know if you would like any details.

- Pete

[Former Member · Accepted solution]

I am impresssed by the explanation. I guess i can call you the macro King. Thanx a lot for the clarifications. Heres my situation.

I have 4 columns A, B, C and D. C is just a difference of A and B. The C value is used to do a calculation in D. I am getting the all the calculations correctly except for D "Result" as it has to use the result of C (which is not A-B straight). This is where i want the macro. But this workbook uses a lotta drilldown chars, so i am not sure what to do. I even used exceptional aggregation but didnt solve the issue.

Message was edited by: Kenneth Phillips

[Former Member · Accepted solution]

Hi Kenneth,

Thanks. I guess it's nice to be King of SOMETHING.

I do not quite understand why the calculation cannot be done inside BW. But, I accept you have tried and it is not working out for you. So, here are some bits of advice.

If you add a calculated column (say, column D) alongside a query result table. And if someone next refreshes the query and adds another drill-down characteristic, they will get a scary warning message from BW ... BW will not have enough room to place the results and will ask the user whether to over-write column D or move the result table to compensate. Except, if you have seen this message before, you know that the message is not all that clear to the user and they often do not know HOW they should respond to this message from BW.

To avoid that, I suggest that you move your result table over one or two columns and then put your macro results to the LEFT of the BW result table instead of putting the macro results to the RIGHT of the BW result table.

There are other solutions. If you understand how BW knows where query elements are located (the answer is ... BW uses named ranges in Excel), you can get BW to clean up your macro results for you each time that it updates the results table. But, unless you understand named ranges in Excel very, very well, this solution is a bit out of reach. Plus, there is no guarantee that a future upgrade to BW will not change the naming convention and make this solution fail. So, the most robust solution is to move your result table a few columns to the right.

Next, how to programmatically locate the results table? Use the SAPBEXonRefresh subroutine. You should already find this subroutine in your workbook. If you do not find it, then you are using Excel 2002 (or more recent) and have not changed your macro security settings to allow access to Visual Basic project. This setting cannot be changed programmatically. Each of your users will need to do this manually (only once). You will find this under Excel Tools >> Options >> Security >> Macro Security. But you will only find this on Excel 2002 or later.

Add code shown below to the SAPBEXonRefresh subroutine:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim ws As Worksheet

Set ws = resultArea.Parent

resultArea.Name = ws.CodeName & "_results"

End Sub

After refreshing the query, you should then be able to find the BW results table easily. To do this manually, press F5 and find the name in the list. To see the VB code equivalent of this, turn on the macro recorder, press F5 and select the name from the list.

In your macro, use this code to create variables for the first and last row and column in the BW result area:

Dim myRange as Range

'define extent of results area

Set myRange = Selection

firstRow = myRange.Cells(1).Row

firstCol = myRange.Cells(1).Column

numCells = myRange.Cells.Count

lastRow = myRange.Cells(numCells).Row

lastCol = myRange.Cells(numCells).Column

Here is code for locating desired columns. In this example, the headers are all in the first row of the BW results table:

'find columns that contain months, customer number

'and total records

ws.Select

dateCol = 0: custCol = 0: totalCol = 0

For Each Col In myRange.Columns

myCol = Col.Column

If InStr(1, Cells(firstRow, myCol), "month") > 0 Then dateCol = myCol

If InStr(1, Cells(firstRow, myCol), "Customer") > 0 Then custCol = myCol

If InStr(1, Cells(firstRow, myCol), "records") > 0 Then totalCol = myCol

Next Col

If dateCol = 0 Or custCol = 0 Or totalCol = 0 Then

MsgBox "Unable to calculate results for " & ActiveSheet.Name, _

vbExclamation, "Did not locate all required columns."

Exit Sub

End If

Hope this helps.

- Pete

[Former Member · Accepted solution]

Lemme try both your suggestions before even asking for more. I am not a master VBA macro writer like you so please bear with me. I will get back to you. Thanx so much.

I have tried some of your suggestions. Lemme summarise what we are trying to do.

-Find the results table area

-Finding out the row & columns of results area -- this is what the finding results table area for,isnt it?

-Then finding the required columns -- what if there are Calculated Key Figures with Formula Variables, where they dont have a proper name defined-only assigned at runtime.

Secondly, my question is where do i insert my overiding calcualtion for a particular result row.

Finally how do i deal with drilldowns which i have no contol over.

I really appreciate your patience here Pete, you the man.

Its become a real to much of a situation here trying to fix this for ever and ever.

Message was edited by: Kenneth Phillips

Message was edited by: Kenneth Phillips

[Former Member · Accepted solution]

Jus to say Pete, i am still waiting on you for your reply.

Thanx for all your effort.

[Former Member · Accepted solution]

Hi Kenneth,

Sorry for the delay in responding. I just had a couple of unexpected days off thanks to hurricane Rita. Luckily I did not personally suffer any significant damage.

FINDING RESULTS TABLE RANGE

Three ways to do this:

1. Described above. Use the routine named SAPBEXonRefresh to create a named range that you can find easily. Code is as follows:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim ws As Worksheet

Application.Goto reference:=resultArea

Set ws = resultArea.Parent

resultArea.Name = ws.CodeName & "_results"

End Sub

If you know the code name of the worksheet containing the query results, then you can locate it later with a command such as:

Application.Goto reference:=Range("Sheet1_results")

2. If you know the local query ID (see the Information tab in the query Properties dialog box), you can use this API function:

'This example locates query result Area using local query ID

Sub testResultRangeByID1()

Dim resultArea As Range, queryID As String

queryID = "SAPBEXq0001"

Set resultArea = Run("SAPBEX.xla!SAPBEXgetResultRangeByID", queryID)

Application.Goto reference:=resultArea

End Sub

3. similar to #2; you need to know the local query ID

'This example locates query result Area using range name created by BEx)

Sub LocateResultUsingBExRangeName()

Dim resultArea As Range, queryID As String

queryID = "SAPBEXq0001"

Set resultArea = Range("SAPBEXqueries!" & queryID)

Application.Goto reference:=resultArea

End Sub

FINDING ROWs AND COLUMNs OF RESULTS AREA

Each of the examples above used a command something like this:

Application.Goto reference:=resultArea

That will select the result table. Now you can identify the first and last rows and columns. Since the result table is always a rectangle, the first row will be the row containing the first cell; the first column will be the column containing the first cell. The last row will be the row containing the last cell in the range. The last column will be the column containing the last cell in the range.

Something like this:

Dim myRange as Range

Set myRange = Selection

firstRow = myRange.Cells(1).Row

firstCol = myRange.Cells(1).Column

numCells = myRange.Cells.Count

lastRow = myRange.Cells(numCells).Row

lastCol = myRange.Cells(numCells).Column

FINDING REQUIRED COLUMNS

No fool proof way. But, let's say that you are looking for one characteristic and one Key Figure. Let's say that the name of the characteristic is "Customer" and the calculated KF contains a month name followed by some text, for example "September Sales Rate". Let's say that you expect these titles to be in the first row of the result table. Code would be like this:

custCol = 0

rateCol = 0

For j = firstCol to lastCol

If cells(firstRow, j) = "Customer" then custCol = j

If cells(firstRow, j) Like "* Sales Rate" Then rateCol = j

Next j

If custCol = 0 or rateCol = 0 Then

MsgBox "Update routine will now terminate.", vbCritical, _

"Unable to locate all required columns."

Exit Sub

End if

The code I gave in the earlier note used the InStr function instead of the "Like" operator. They are equivalent concepts. Using one vs. the other is a matter of preference.

WHERE TO PUT EXCEL CALCULATION

Two choices: to the left or to the right.

As explained earlier, putting the calculated column to the right will cause a problem if the user later adds another drill-down column. Unless you understand completely example 3, above for locating the query results table. If you do understand example 3 completely, then you can put columns to the right and BEx will clean up for you when the query is next refreshed. I do this a lot; but, I would not recommend it until you are completely confident. Even then, the risk is that some day the Analyzer software will change and this trick will no longer work.

Putting the Excel-calculated column to the left is much safer. Particularly when you have only one column. Manually move the result table so that it does not start in column A anymore.

Code for Excel calculated column (example):

veryLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

'clear old data

Range(Cells(firstRow, 1), Cells(veryLastRow, 1)).ClearContents

'new data

For i = firstRow + 1 to lastRow

Cells(i, 1) = Cells(i, rateCol) + 1

Cells(i, 1).Format = "#,##0"

Next i

DRILL DOWN BY USER

None of this should be affected by the user adding additional drill-DOWN characteristics. Drill-accross can be a problem, but it can be handled if you really need to.

A bigger issue is if you are using characteristics as part of your calculation logic. If the user changes the display from Text to Key + Text (or vice versa), then this could screw up your logic. Let me know if think that will be an issue and I will send you some code to handle it.

- Pete

[Former Member · Accepted solution]

Feel sorry about that. Didnt mean to rush you when you in trouble. Apologize for what has happened.

Now i am understanding very well. But can help me put all this together into one piece. Let me know if this is right??

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim ws As Worksheet

Set ws = resultArea.Parent

resultArea.Name = ws.CodeName & "_results"

End Sub

Comment: Ignore this from code "Till here i got it in place"

sub Mac1()

Dim myRange as Range

Set myRange = Selection

firstRow = myRange.Cells(1).Row

firstCol = myRange.Cells(1).Column

numCells = myRange.Cells.Count

lastRow = myRange.Cells(numCells).Row

lastCol = myRange.Cells(numCells).Column

custCol = 0

rateCol = 0

For j = firstCol to lastCol

If cells(firstRow, j) = "Customer" then custCol = j

If cells(firstRow, j) Like "* Sales Rate" Then rateCol = j

Next j

If custCol = 0 or rateCol = 0 Then

MsgBox "Update routine will now terminate.", vbCritical, _

"Unable to locate all required columns."

Exit Sub

End if

veryLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

'clear old data

Range(Cells(firstRow, 1), Cells(veryLastRow, 1)).ClearContents

'new data

For i = firstRow + 1 to lastRow

Cells(i, 1) = Cells(i, rateCol) + 1

Cells(i, 1).Format = "#,##0"

Next i

End Sub " Does this look right to you??? I am trying to run this but dosent seem to do the calculations"

Need help here Peter. Thanx very much.

[Former Member · Accepted solution]

Hi Kenneth,

No problem. Houston was lucky this time.

It looks like you have almost everything. But, missing one important part. Before using

Set myRange = Selection

You need to actually select the result table. Code to do that is something like:

Application.Goto reference:=Range("Sheet1_results")

replace "Sheet1" with the CODE name of the worksheet that contains the query. In the VB Editor, in the Project Explorer window, under the Microsoft Excel Objects for your project (in VBA, the workbook is called a project), you will see a list of all the worksheets in the workbook. There are two names next to each one. The first name (not in parentheses) is the CODE name. The second one (in parentheses) is the name (I call it the "TAB name", but no one else calls it that).

After refreshing the query, you should be able to see the named range it created. To do this, in Excel press F5 (or if you are GUI-oriented, Edit >> Go To). If you do not find the named range in the list, then the SAPBEXonRefresh routine did not work. The most likely cause of that is that your macro security is not set correctly. To check this, in Excel, go to Tools >> Options; click on the Security tab; click the Macro Security button, click on the Trusted Publishers tab, be sure that the Trust Access to Visual Basic Project box is checked.

Unfortunately, every one of your users will need to do this same thing. If they do not, then the SAPBEXonRefresh will never work.

If that is a problem for you, write back and I will tell you a way around that problem.

This CODE name stuff is not really necessary unless you have more than one query in the workbook. The reason I use the CODE name, by the way, instead of the TAB name is because if we wrote (in the SAPBEXonRefresh routine):

resultArea.Name = ws.Name & "_results"

we would get an error is the TAB name contained blank spaces or non-ASCII characters. Plus, users sometimes change TAB names; but, I have never known a user to change the CODE names ... since they usually don't know how.

I am very surprised that the text I put in for "Customer" and "*Sales Rate" work for you. You need to replace those with the text that fits your project.

When you run the macro, try running it in break-mode. That is, from VB Editor run it by pressing F8. That will highlight one line of code at a time (just BEFORE) executing that line. You should be able to float your cursor over each variable and see that it contains the correct value AFTER the line of code is executed.

So, you should be able to see for example, that the first row,last row, first column, and last column are properly identified.

You sould be able to verify that the correct columns were found.

Once all of that is tracking correctly, you make the calculation read what you want it to read.

Hope this helps.

- Pete

[Former Member · Accepted solution]

I added the line as you sugggested. I added before the Dim .. statement.

Also the name is "Sheet1" is the actual name so neednt have to change.

i got the "named range" as a success.

Dont worry i changed "Customer and rate" to mine, just said yours so thought will not cause confusion.

One more change i made is...i didnt have to clear as other cells values are good. I am just overwriting one column values so commentes the clear statements and did a small calcualtion here....

-

---

'veryLastRow = ActiveSheet.Cells.SpecialCells : comment' (xlLastCell).Row

'clear old data

'Range(Cells(firstRow, 1), Cells(veryLastRow, 1)).ClearContents

'new data

For i = firstRow + 1 to lastRow

Cells(i, my1Col) = Cells(i, my1Col) - Cells(i, my2Col)

'Cells(i, 1).Format = "#,##0" : comment

Next i

-

---

Now i am getting an error:

" Runtime error: 1004 ":

Method 'Range of object '_Global' failed

in a pop-up window.

Can you tell what happ??

Message was edited by: Kenneth Phillips

[Former Member · Accepted solution]

Hopefully, the error message has a "debug" button on it. Press the debug button and the line of code that created the error will be highlighted. Tell me which line of code is creating the problem.

By the way ... I am glad that you commented out the format line. It is wrong. It should read:

Cells(i, 1).NumberFormat = "#,##0"

- Pete

Message was edited by: Peter Knoer

[Former Member · Accepted solution]

This is the line:

Application.Goto reference:=Range("Sheet1_results")

Let me know whats next. I am waiting eagerly, seems like we are very close to getting a solution.

Thanx.

I am just giving the whole code again:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

Dim ws As Worksheet

Application.Goto reference:=resultArea

Set ws = resultArea.Parent

resultArea.Name = ws.CodeName & "_results"

End Sub

Sub Mac1()

Dim myRange As Range

Application.Goto reference:=Range("Sheet1_results")

Set myRange = Selection

firstRow = myRange.Cells(1).Row

firstCol = myRange.Cells(1).Column

numCells = myRange.Cells.Count

lastRow = myRange.Cells(numCells).Row

lastCol = myRange.Cells(numCells).Column

my1Col = 0

my2Col = 0

my3Col = 0

For j = firstCol To lastCol

If Cells(firstRow, j) Like "*my1 *" Then my1Col = j

If Cells(firstRow, j) Like "my2 *" Then my2Col = j

If Cells(firstRow, j) Like "my3 *" Then my3Col = j

Next j

If my1Col = 0 Or my2Col = 0 Or my3Col = 0 Then

MsgBox "Update routine will now terminate.", vbCritical, _

"Unable to locate req columns."

Exit Sub

End If

'veryLastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

'clear old data

'Range(Cells(firstRow, 1), Cells(veryLastRow, 1)).ClearContents

'new data

For i = firstRow + 1 To lastRow

Cells(i, my1Col) = Cells(i, my2Col) - Cells(i, my3Col)

Cells(i, 1).Format = "#,##0"

Next i

End Sub

Message was edited by: Kenneth Phillips

[Former Member · Accepted solution]

Kenneth,

That line of code SHOULD work. Just for grins, I turned on the macro recorder, did the F5 and selected the named range. This is what I got from the macro recorder (the 3rd line below):

Application.Goto Reference:=Range("Query1_results")

'Application.Goto Reference:=Range("Sheet1_results")

Application.Goto Reference:="Query1_results"

You will notice that I change my worksheet code names so that I can more easily keep track of which query I am referencing. But, ignore that.

I get exactly the same response with the first line of code as the last line of code. I am at a loss to explain why it doesn't work for you. But, try it without the word Range (and without the parentheses) and see if you have better luck with it that way.

I pasted your line of code next to mine to see if there are any small differences that could be killing you. I do not see any.

- Pete

[Former Member · Accepted solution]

If that doesn't work, then I suggest doing exactly what I just described.

In Excel, turn on the macro recorder, press F5, select the named range, click OK.

Then, go see what the macro recorder came up with.

It is possible that the sheet's TAB name is different from the CODE name and that you are being confused by the difference.

- Pete

[Former Member · Accepted solution]

Tried using Macro recoder. It came up with the same range.

Then i created a button wherein i assigned the the macro to the button. Then i see that message we gave in our message box. "Update routine will now terminate."

I guess its not able to read the myCol values. Let me know how to proceed next. Thanx.

[Former Member · Accepted solution]

We probably should take this off line. You can send your phone number to my e-mail address:

pete77079@yahoo.com

Message was edited by: Peter Knoer

[Former Member · Accepted solution]

You once again proved that your the King.

Thanx for all the support.

[Former Member · Accepted solution]

Hi peter,

I am trying to manage Drill down sets from the user in order to find a column at the result area, Do you have any idea how to handle that?

Thanks a lot

Ricardo

[Former Member · Accepted solution]

How can I do to solve the Drill-accross problem?