Ron Rosenfeld wrote...
"Barb Reinhardt" wrote:
I've been using the INDIRECT.EXT function for a while and am finding that I
have a consistent problem when worksheets are missing. I'm looking up data
in multiple workbooks on the same worksheet name, but find that if the
worksheet name does not exist, Excel seems to lock up.

There is a message that "Microsoft Excel is waiting for another application
to complete an OLE action" that will not go away. When I brute force exit
from Excel, a window is displayed asking for me to select another sheet to
use instead of the missing worksheet. I don't want to use another sheet if
it's not there. I'd rather move on or get an error in the cell. In
addition, there is an excel workbook with the name YMNQIYASUDYS created each
time there is a missing worksheet.
....

This looks like the same problem I had a few years ago using Automation
and another Excel instance to get data from closed workbooks by
entering generated formulas in the second instance.

When you enter a cell formula interactively or via macro, if that
formula contains token that are syntactically external reference links
but the workbook and worksheet combination doesn't exist, Excel
*ALWAYS* displays a dialog prompting you to choose another workbook or
worksheet. AFAIK, there's no way to disable these dialogs.

I finally got around this in my own pull udf by using
ExecuteExcel4Macro method calls, which don't cause these dialogs to
display when either workbook or worksheet don't exist. Looks like
Laurent Longre's INDIRECT.EXT retrogressed from earlier versions.

You should first check to see if you have the latest version (check at
http://xcell05.free.fr). If you do and are still seeing the problem, navigate
to the Forums board at that web site, and post your problem.

In the version I have, if a worksheet is missing, the function (as documented)
gives a #VALUE! error.

I get the same result as the OP using MOREFUNC.XLL version 3.7.1. This
was *NOT* the result I used to get using older versions. As I said
above, retrogression.

Note: bringing up Task Manager while Excel hangs doing this shows two
EXCEL.EXE processes, so it seems Laurent Longre's INDIRECT.EXT also
uses a separate Excel instance to fetch data from closed workbooks.