UDF referencing closed workbook not working

Hi

I built a udf that, in usage, references another workbook. Basically, it has the user provide a range, and then searches that range for various text. It uses a For each object in sheet.cells loop. I've found the formula only works when the workbook it is referencing is open. Is that correct? What is the reason?

How else could the UDF go through the contents of the workbook if it weren't open?

You can retrieve single values using ADO, but using the common elements of the object model applies to open workbooks.

Why is it that excel's native functions work? Do they use a different process to get the values? For example vlookup?

Some Excel functions work -- the ones that work on closed workbooks. INDIRECT, for example, doesn't.

Out of curiosity, do you know of a rhyme or reason for which work and which don't? I don't see any difference between the way vlookup looks at a spreadsheet in a workbook and the way Indirect does the same.

I believe Harlan Grove explained it this way:

Functions that either return range references or require particular arguments to be range references don’t work with closed workbooks:
OFFSET 1st argument must be a range reference, and it returns a range reference. Excel evaluates external references into closed workbooks as either scalar values or arrays, not as range references in Excel's strict working definition of 'range'.
INDIRECT Always returns a range reference.
RANK 2nd argument must be a range reference, no matter what online help says.
COUNTIF 1st argument must be a range reference.
SUMIF 1st and optional 3rd arguments must be range references.
CELL Optional 2nd argument must be a range reference.

Ok, when I test out Grove's list, I can see the arguments he refers to are called: reference, ref_text, ref, and range. However, vlookup's argument that I would expect to be the same is called table_array. After selecting a range for CountIf and Vlookup, the range looks exactly the same in the formula bar. So I guess I'm not seeing the difference.

That being said, could I somehow use however Vlookup's "table_array" argument works with my UDF defined below?

Please Login or Register  to view this content.

This is a generic udf, I know there are built-in excel alternatives. I'm really just curious about the differences I'm seeing.

Thanks shg

The difference is that VLOOKUP will accept an array as the lookup table, rather than a range. COUNTIF has to have a range.

The difference is, for VLOOKUP and MATCH and others, the second argument is commonly a range, but CAN BE a literal array:

=MATCH(3, {1,2,3,4,5}, 0) works fine.

In VBA, it can also be an array:

Please Login or Register  to view this content.

This doesn't work, because the first argument MUST BE a range:

=COUNTIF({1,2,3,3,4}, 3)

Ditto for

Please Login or Register  to view this content.

If you're using WorksheetFunction, IntelliSense will tell you which arguments must be ranges.

Sorry if I'm being obtuse, but either way doesn't excel have to go in and look at the values of a spreadsheet in a closed workbook? In my udf above could I ask for an array instead of a range and thus be able to use it on a closed workbook?

VBA and worksheet functions are not the same thing. In VBA, a range is a collection of cells in an open workbook. If you attempt to pass a range in a closed workbook to a UDF, you'll never arrive at the UDF.

So would it be correct to say excel worksheet functions have some special functionality that can't be accomplished by using vba?

That's not the issue - your UDF requires a range, not an array of values, which is why it doesn't work (just like COUNTIF). If your UDF worked with an array of values, you would be fine.

I get what you are saying about the range, my udf requires a range, not an array. My question now is why when I type a valid vlookup formula into a sheet it can find the correct value even if the workbook it is referencing is closed. How does it get the array it is working on out of the closed workbook?

Because VLOOKUP can work with an array of values, which is what it retrieves from the closed workbook. COUNTIF (for example) requires an actual range, which it cannot retrieve from a closed workbook, so does not work with closed workbooks.

The answer to that is buried in the bowels of Excel, but what difference does it make, Dave?

You can pass an array of values from a closed workbook to a UDF, you cannot pass a range.

This works:

=MyFunc('C:\some Path\[some closed Workbook.xls]some Sheet'!A1:A10)

... if MyFunc is like this

Please Login or Register  to view this content.

... and not like this:

Please Login or Register  to view this content.

EDIT: Consider that a Range has many more properties than Value; it has cell formatting, text formatting, rich-text formatting within cells, comments, dependents, formulas, height & width, ...

Ok, I think it's finally sinking in. That last post was helpful shg, thank you.
Thanks romperstomper. I appreciate the instruction.