Convert a SUB to a FUNCTION?

[insaneoctane]

This SUB works:

Public Sub getData()
    uid = "1012500"
    dept = Empty
    Dim wBook As Workbook
    Dim Dir_MRange As String
    Dim DirectoryRange As Variant
    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

I'd like to make it a FUNCTION. I've tried but can't make it work. Any suggestions?
This is the function I tried to call the SUB that doesn't work...first I change the sub to:
Public Sub getData(ByVal uid As String, ByVal dept As String)

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

If I can make it work I'll change the hard-coded uid from "1012500" to a parameter that the function is called with.

 

[Leith Ross]

Hello insaneoctane,

Okay, let's try it this way... http://www.decisionmodels.com/calcsecretsj.htm

 

[insaneoctane]

Well, I know more but still have the problem.
The line:

Set wBook = Workbooks.Open("C:\Users\myuser\Documents\REFERENCE\SAP\SAS Directory 05-2012.xlsx") ' Path to external

behaves differently when it's executed as a standalone SUB vs a SUB called by my function. When I debug step-by-step, if the SUB is called manually (I have to remove the expected input parameters and hardcode them to do this), then it works and in the LOCALS window, I see that wBook becomes a workbook....but if a function calls my SUB, the wBook shows "Nothing" in the LOCALS window, so obviously, the SUB doesn't work right....now why?

 

[Norie]

Are you sure it's the sub not working?

There are certains things a UDF can't fo and I think one of them might be open a workbook.