Yes, after some debugging, the references to ranges are getting confused when
they are referenced from another active workbook.
I'm having some success in getting this to work.

I replaced references in the UDF that looked like this:
range(MyColLookup)
with this:
MyWorkbook.Names(MyColLookup).RefersToRange

This actually works, but now I have a chicken-and-egg problem.
In my function, I have hardcoded the name of my workbook:
Dim MyWorkbook As Workbook
Set MyWorkbook = Application.Workbooks("Quik3.xls")

How can I have the UDF determine the name of the workbook it is in without
my actually having to type it into the code?
I see in the Project explorer the module is under VBAProject(Quik3.xls)
Can the code reference its own parent?

(By the way, thanks for the quick replies to my questions. I hope somebody
is paying you for this attention :-)


"Bob Phillips" wrote:

It's hard to say without seeing the code. Your problem was caused because it
was trying to refer to the range of the activesheet, so I forced it refer to
the range's parent sheet.