Convert a SUB to a FUNCTION?

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
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)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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:

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62

ADVERTISEMENT

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)
 

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62
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!)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Once it get to the uidrow=... line the debugger stops working (F8 does nothing more)
Did the code actually open the "SAS Directory 05-2012.xlsx" workbook?
 

insaneoctane

Board Regular
Joined
Dec 2, 2005
Messages
62
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

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
Top