Analysis functions - Excel/VBA Error values explained

[Former Member]

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...