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

[insaneoctane]

My FUNCTION takes person's UID and returns Dept from an external spreadsheet.
When I try to debug my function with F8, it just quits working (as a function- F8 debug worked as a sub). Any help would be great. By the way, here's some of the specific challenges I've run into:

* What I think I'm doing seems like it SHOULD be a common idea!
* I want to hardcode the whole path into the function.
* I tried moving the "sub" into the function, but that didn't work...then I tried calling the sub from the function (like below), but that doesn't work either
* I will write 2 or 3 functions like Get_Dept (Get_section, Get_Name) that will just return different column data form the directory workbook

Feel free to give me a suggestion from scratch! But, here's what I've got so far:

Public uid As String
Public dept As String

Public Sub getData(uid, dept As String)
Dim wBook As Workbook
Dim Dir_MRange As String
Dim DirectoryRange As Variant
ActiveWB = ActiveWorkbook.Name 'This workbook
Set wBook = Workbooks.Open("C:\Users\myuser\Documents\REFERENCE\SAP\My Directory 05-2012.xlsx") ' Path to external
Dir_MRange = "B2:B14740" 'This contains the UID to match from
uidrow = Application.WorksheetFunction.Match(uid, wBook.Worksheets("Directory").Range(Dir_MRange), 0)
dept = wBook.Worksheets("Directory").Range("I" & uidrow + 1).Offset(0, 0).Value 'The column of data to return is column "I"
wBook.Close
Set wBook = Nothing
End Sub
Public Function Get_Dept(uid_local As String)
dept = "empty"
uid = uid_local
Call getData(uid, dept)
Get_Dept = dept
End Function

Any help would be GREATLY appreciated!

[protonLeah]

Hello insaneoctane, welcome to the forum. Please take a moment to read the forum rules and then add code tags around your code. Code tags are required.

[insaneoctane]

bump. Anybody?

[MrShorty]

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.

[insaneoctane]

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:

uidrow = Application.WorksheetFunction.Match(uid, wBook.Worksheets("Directory").Range(Dir_MRange), 0)

line, debugger just STOPS

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

[protonLeah]

You have:

Public uid As String, and
Public Sub getData(uid, dept As String)

These are two different uid variables. So,

uidrow = Application.WorksheetFunction.Match(uid, wBook.Worksheets("Directory").Range(Dir_MRange), 0)

should have a problem (ambiguous/conflicting names). Since you are using the public var: uid in the function code, you should change this one.

Also, in the parameter declarations Public Sub getData(uid, dept As String) As String only types "dept" as String, not "uid" . Each variable must be typed separately:

Public Sub getData(ByVal uid As String, ByVal dept As String)

Also, your function:

Public Function Get_Dept(uid_local As String)

doesn't specify a return data type so it is Variant by default, it should be String:

Public Function Get_Dept(uid_local As String) As String

[insaneoctane]

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:

Public Sub getData()
    uid = "1012500"
    dept = Empty
    Dim wBook As Workbook
    Dim Dir_MRange As String
    Dim DirectoryRange As Variant
    ActiveWB = ActiveWorkbook.Name 'This workbook
    Set wBook = Workbooks.Open("C:\Users\myuser\Documents\REFERENCE\SAP\SAS Directory 05-2012.xlsx") ' Path to external
    Dir_MRange = "B2:B14740"
    Dir_Range = "A2:K14740"
    uidrow = Application.WorksheetFunction.Match(uid, wBook.Worksheets("Directory").Range(Dir_MRange), 0)
    dept = wBook.Worksheets("Directory").Range("I" & uidrow + 1).Offset(0, 0).Value
    wBook.Close
    Set wBook = Nothing
End Sub

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

Public Function Get_Dept(uid_local As String) As String
    dept = "empty"
    Call getData(uid_local, dept)
    Get_Dept = dept
End Function

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!

[MrShorty]

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:

function getdata(uid,lookupcolumn,returncolumn)
uidrow=application.worksheetfunction.match(uid,lookupcolumn,0)
getdata=returncolumn.cells(uidrow,1).value
end function

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

[protonLeah]

You seem to have the roles of sub and function confused. Those the attachment help?

[insaneoctane]

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.

[MrShorty]

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.