The Analysis Add-In functions can return several error values. For a robust Excel or VBA solution, you”ll need to know what each of these errors are, when they might occur, and how to handle them. Every solution is different, so I won’t try to explain how you should handle these error values in your solution, but instead, I’ll just list and explain the error values that you’ll probably encounter.

This article is about VBA/Excel errors only, and not the Add-In error codes that can be retrieved using SAPGetProperty and “LastError”.

I’m only detailing the errors that I’ve seen produced by the add-in (as at 1.4 SP3.1 and in Excel 2007), so if you know of any more, please add details in the comments…

Excel has a number of built in errors, as follows:

Number  Cell Display  Occurrence

=======  =============  ===========================================================================

2000    #NULL!        Two range areas do not intersect.

2007    #DIV/0!       A number is divided by zero.

2015    #VALUE!       Incorrect type of argument or operand is used.

2023    #REF!         A cell reference is invalid.

2029    #NAME?        Excel doesn’t recognize text in the formula

2036    #NUM!         Invalid numeric values in a formula/function.

2042    #N/A          A value isn’t available to a function/formula.

Of the errors above, the Analysis add-in returns the errors below, but the occurrence rules sometimes differ slightly….

2015 – #VALUE! – This error is returned in at least 2 situations:

1. When calling a function and providing invalid arguments to the function. For example, if you call a function that expects a Data Source Alias, and you provide an Alias that doesn’t exist in the workbook, the function will return Error 2015. Note however, that the Add-In only treats certain invalid arguments as errors. For example, calling SAPGetMember with a non-existent Dimension/Member pair as the second argument, will not return an error, but instead, just a zero-length string.

2. When calling a function that you might expect to return an array, but there aren’t any values in the array. For example, calling SAPGetCellInfo (to discover a cell’s SELECTION) on a cell that is a total across all dimensions, will not return any dimensions in the array, and instead return Error 2015. It’s not really an error that indicates that the cell doesn’t have a selection, or that an error occurred with your syntax or arguments, but it is an indicator that the cell’s SELECTION dimensions actually include all row and column dimensions. Note the difference between this error and Error 2042 below. ie. There is a subtle difference between returning “no data to return” as Error 2015, and returning “non-existant data” as Error 2042.

2023 – #REF! – This error indicates that the datasource hasn’t been successfully refreshed, or has encountered an Exception. In other words, the datasource is not connected, and the function can’t return a value. The user should have been presented with an Exception dialog, with an option to Restart the session, so if you encounter this error in your code, you’ll need to handle a Restart.

2029 – #NAME! – This error isn’t actually returned by the Add-In, but instead by Excel. It indicates that the add-in is either not loaded, or that the function you’re calling isn’t registered (you might encounter this if you’re trying to call a function that is only present in a newer version of the add-in than you’re currently using, or if a function in the current version is removed in a future version of the Add-In)

2042 – #N/A – This is the error value that you’ll probably encounter most frequently with the Analysis add-in. The add-in returns this when you’ve requested data that doesn’t exist. For example, requesting DIMENSION details with SAPGetCellInfo, on a data cell, would not return any data because data cells do not have DIMENSION details.

Found any other Error values while using the add-in? Add them in the comments…

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Martin Kreitlein

    Hey Andrew,

    great post … very interesting 🙂

    I did not want to start a new topic, so I hope you don’t mind if I ask my question here:

    –> Did anybody already test this function?

    =SAPListOfVariables(“DS_1″;”KEY”;”PROMPTS”)

    It looks like it does not display the variable names, but the same as:

    =SAPListOfVariables(“DS_1″;”TEXT”;”PROMPTS”)

    I had expected to get a list of the technical variable names, but both are showing only the description?!

    Am I wrong?

    Thanks, Martin

    (0) 
    1. Andrew Roebuck Post author

      Hello Martin, and thanks for your feedback.

      You’re right that the SAPListOfVariables only returns the Variable descriptions. The 2nd parameter determines whether the Text or the Key is returned for the Value in the variable.

      There is a problem here if you inadvertantly end up with 2 variables that have the same description, as you can’t (with this function alone) reliably determine which variable you’re retrieving the value for. This has been raised as an Idea on Idea Place… I provided a link here:

      http://scn.sap.com/community/businessobjects-analysis-ms-office/blog/2013/09/27/analysis-vba-functions–vote-for-idea-place-enhancements

      Vote for the enhancement, and we might get it in a future release. In the meantime, you can design your query to have unique variable descriptions and/or hard-code the technical names into your spreadsheet/VBA.

      If you must dynamically retrieve the variable descriptions from SAPListOfVariables, you could pass each variable description retrieved from SAPListOfVariables to SAPGetVariable using TECHNICALNAME as the 3rd parameter, in order to resolve the technical name from the description. I’m not sure how SAPGetVariable behaves if you provide a variable description that is not unique – It might return all matches, it might return the first match, the last match, or an arbitrary/non-deterministic match, it might not return any matches, or it might return an error. Again, I don’t have variables with common descriptions, so I can’t test this…. If you do take this approach, please reply with your findings so that others might benefit.

      (0) 
      1. Martin Kreitlein

        Hi Andrew,

        thanks a lot for that Information. Right now I understood the difference of SAPListOfVariables by KEY and TEXT.

        And your hint in your idea was finally my solution 🙂

        Fortunately our variable description is unique per report, so it will work.

        I’m not able to, but a colleague of mine voted for all of your 4 ideas!

        Best regards, Martin

        (0) 

Leave a Reply