XLL add in function obtain array range reference

Was this post useful to you?

Would like to be able to determine the rows/cols of the current array range from an XLL UDF written in c++. That is, have UDF's that are array funcs, but they need to know the entire Ref/Range of the array from which they are called.

1) Excel4(xlfCaller, ...) cannot be used in general, since if the user places the cursor in just one cell in the array range the XLOPER returned by xlfCaller will contain ONLY the ref to that one cell.

2) using Excel4(xlfSelection, ...) cannot be used in general, since it only returns the entire (array) range if the entire range is selected prior to the call, otherwise returns only the cell(s) which have been explicitly selected, and not the entire array range.

3) Have tried to use iE4 = Excel4(xlcSelectSpecial, &ArRef, 1, &SSArg) with all vars being XLOPER's and SSArg type = xltypeInt, and SSArg.Val.w = 6, which is the correct arg value to select the current array range.

iE4 returns 0, meaning "success".

However, this does not work in a UDF, as ArRef comes back as Type 4 (bool), and the .rwFirst, .rwLast, etc components are mostly rubbish.

Put differently, one would have expected ArRef to come back as type 1024 (xltypeSRef), with the relevant components holding the array Range ref values.

I am guessing the issue may be related to this being an "xlc" ie. a "command" function.

Regardless, must have some way to obtain the entire current array range/ref when only one or few cells in the array range are selected.

Please advise

DrO's picture
Sun, 09/14/2014 - 23:32

1 Answers for "XLL add in function obtain array range reference"

Sussed it!

In fact it works correctly wish xlfCaller, but can give the appearance of a wrong answer.

To make a long story short, when in debug mode, Excel always "cycles" through the func "twice". That is, it evaluates the entire UDF twice, as is evident when stepping through with the debugger.

When working with array formula's, and selecting just one cell in an array, clicking "fx", the xlfCaller fails on the first cycle through the UDF. HOWEVER, whenever Excel eval's an array formula, at the end of the eval, the entire array is selected/highlighted automatically by Excel (whether the eval was correct or not). Then, on the second pass through the UDF, the "auto selection" of the entire array range at the end of the first cycle, allows xlfCaller to return an XLOPER with the correct Range Ref.

Post your reply

Filtered HTML

  • Allowed HTML tags: <b> <br> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.