Convert a SUB to a FUNCTION?

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
72
This SUB works:
Code:
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)

Code:
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Just guessing here. Try this function...

Code:
Function getDept(uid As Variant)
    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)
    getDept = wBook.Worksheets("Directory").Range("I" & uidrow + 1).Offset(0, 0).Value
    wBook.Close
    Set wBook = Nothing
End Function
And call it this way...

Code:
MsgBox getDept(1012500)
 
Upvote 0
Why not just use a formula?

=INDEX("C:\Users\myuser\Documents\REFERENCE\SAP\[SAS Directory 05-2012.xlsx]Directory!I:I", MATCH(uid, "C:\Users\myuser\Documents\REFERENCE\SAP\[SAS Directory 05-2012.xlsx]Directory!B:B", 0) + 1)
 
Last edited:
Upvote 0
Just guessing here. Try this function...

And call it this way...

I tried that. No go. It just gives #VALUE!. I copied your suggested function, and called it from a cell using "=getDept(1012500)"
Then I inserted a break point and used F8 to step through the function. Once it get to the uidrow=... line the debugger stops working (F8 does nothing more)
 
Upvote 0
Why not just use a formula?

=INDEX("C:\Users\myuser\Documents\REFERENCE\SAP\[SAS Directory 05-2012.xlsx]Directory!I:I", MATCH(uid, "C:\Users\myuser\Documents\REFERENCE\SAP\[SAS Directory 05-2012.xlsx]Directory!B:B", 0) + 1)

Because I want to use this over and over and a function would be FAR less typing and something I could easily remember. I want to put =get_dept(A2) in the cell and have this work. I'm trying to simplify things (our course this function has proven not to be simple!)
 
Upvote 0
I found the link to work without the j at the end....
I added some error trapping to my FUNCTION/SUB and it's throwing this:

91: Object variable or With block variable not set at this line:
Code:
uidrow = Application.WorksheetFunction.Match(uid, wBook.Worksheets("Directory").Range(Dir_MRange), 0)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top