Creating a FUNCTION that "looksup" data from an EXTERNAL workbook...I'm failing, HELP!

bump. Anybody?

Question: Is this function procedure intended to be called as a UDF from a spreadsheet cell, or is it intended to be called from another VBA procedure? I ask because UDF's called from the spreadsheet cannot perform certain actions (like invoking the workbook.Open method).

Assuming this is a UDF called from a spreadsheet, a couple of thoughts:

1) I know you say you want the reference to the lookup table to be hardcoded in the function. FWIW, I have a couple of UDF's that perform lookups and I pass the lookup table to the function through the argument list. When I do this, I don't have any trouble accessing a lookup table in a closed workbook. As with native Excel functions, it doesn't seem to have any trouble accessing information in a closed workbook in this way.
2) In a couple of other cases, where the data in the lookup table is VERY static, I will store the lookup table in the same workbook as the function (usually saved as an addin). Having the lookup table in the same workbook gets rid of problems accessing data from a closed workbook and also allows me to set the reference using the Thisworkbook object. This has worked well for me for lookup data that rarely/never changes.
3) To access the debugger when writing UDF's, I will set a breakpoint at an appropriate point in the code, then call the function normally from the spreadsheet. Code execution stops at the breakpoint and allows me to use the debugger tools to figure out if the code is working correctly.

MrShorty,
Thanks for the replies.
1. I'm okay with the workbook either being open, or opening it...that doesn't deter me
2. I'm working in a NEW workbook everyday where I need to perform the lookups. The directory lookup worksheet in what is common, a new worksheet of the day is ever changing, which is why I want a UDF
3. I also put in breakpoints and continue with F8 through the code, but then after the:

Please Login or Register  to view this content.

line, debugger just STOPS

Thanks for your time, I'm still struggling with this.

Well, I fixed the issues with the ambiguous variables and other suggestions to no avail. Maybe I can ask a simple a question:
If this sub WORKS:

Please Login or Register  to view this content.

How do I turn it into a function?
I've tried this:

Please Login or Register  to view this content.

But it doesn't work. I just can't figure how to turn my sub into a function. In the example above, I hardcoded a UID, but I would send that to the function if I could get it to work!

Like I said above, the first thing I see is that a UDF (assuming it is being called from a spreadsheet cell) cannot invoke the .open method.

As I suggested above, many of the lookup UDF's I've written, I have written so that I pass the lookup table to the UDF through the argument list. So, something like the following:

Please Login or Register  to view this content.

It was quickly and sloppily done, but it gives you an idea of how I would structure such a UDF.

Thanks for the replies.
I don't think I have the roles confused, but what I was showing you above is that I have a sub that works BY ITSELF. It properly looks up and finds the dept from an external worksheet. If you paste that SUB into a module, and run it, it will work. You are correct in that the way I show it above, it does not accept any arguments. When I try to use it with the FUNCTION (and then, yes, I have to modify it to accept a UID parameter), it doesn't work. Heck, even if I don't pass it anything and hardcode the UID like it shows above, it quits working when I call it with the FUNCTION, which is strange to me. I want a function because I want to type "=GET_Dept(A12)" in a cell and have it go look up that UID in my external spreadsheet without having to copy paste stuff around.

Here's a pair of workbooks that illustrate one of the approaches I suggested for this problem. It does appear that the worksheet with the lookup table must be open for the UDF to work. excelforumbook1 has the lookup table, and book3 contains and calls the UDF.