Skip to Content
author's profile photo Former Member
Former Member

VBA Macros

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2005 at 11:59 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.