Possible to have UDF vlookup from closed workbook?

[largeselection]

I'm new to UDFs, is it possible to have a UDF for a vlookup that references a closed workbook?

I have:

Function CCLookup(Rank)
 
CCLookup = excel.application.worksheetfunction.vlookup(Rank, 'O:\C\Templates\[UDF Lookup Sheet.xlsx]Sheet1'!R2C1:R676C5,2,false)
 
This is not working and I've read a few other posts that seem to suggest it's not possible to do a lookup with a closed workbook directly in vba.  Any workarounds?

 

[RoryA]

It's not, as such. You could use Evaluate with the formula as you would enter it. Why are you using a UDF anyway?

 

[largeselection]

I have a vlookup that I do a lot a lot a lot so I thought I might be able to simplify my life a bit by creating a UDF so that instead of always having to open the file with the references and typing in the formula I could just use a UDF like CClookup() and have it do that for me.

 

[largeselection]

How could I use evaluate and have it use the cell I target as the lookup value?

I've tried this:

Function whatnumber(target)
Dim fpath As String
Dim fname As String
Dim sname As String
fpath = "O:\C\CXXX\"
fname = "UDF LOOKUP SHEET.xlsx"
sname = "Sheet1"

whatregion = Evaluate("vlookup(target,' " & fpath & "[" & fname & "]" & sname & "'!$A$2:$E$676,2,false)")

End Function

I have also tried the similar equation, but instead of evaluate I had

Activecell.Formula = _
"=vlookup...(SAME AS ABOVE)"

Neither worked.

 

[RoryA]

whatregion = Evaluate("vlookup(" & target & ",' " & fpath & "[" & fname & "]" & sname & "'!$A$2:$E$676,2,false)")

 

[ron2k]

How could I use evaluate and have it use the cell I target as the lookup value?

I've tried this:

Function whatnumber(target)
Dim fpath As String
Dim fname As String
Dim sname As String
fpath = "O:\C\CXXX\"
fname = "UDF LOOKUP SHEET.xlsx"
sname = "Sheet1"

whatregion = Evaluate("vlookup(target,' " & fpath & "[" & fname & "]" & sname & "'!$A$2:$E$676,2,false)")

End Function

I have also tried the similar equation, but instead of evaluate I had

Activecell.Formula = _
"=vlookup...(SAME AS ABOVE)"

Neither worked.

If I understand correctly and assuming that you will use the formula just on few cells you may want to look at the "PULL" UDF: http://www.mrexcel.com/forum/showthread.php?p=2131711#post2131711
Specifically at post #4 and 5

Hope this helps

Ron

 

[largeselection]

I'm getting 0 as the result instead of "USA". Here is the code:

Function region(target)
Dim fpath As String
Dim fname As String
Dim sname As String
fpath = "O:\C\Cxxx\"
fname = "UDF LOOKUP SHEET.xlsx"
sname = "Sheet1"

region = Evaluate("=vlookup(" & target & ",' " & fpath & "[" & fname & "]" & sname & "'!$A$2:$E$676,2,false)")

End Function

Do I have to have all that code for pull before this? and then include the pull function after the vlookup?

In the alternative, I thought I might be able to use just a lot of cases so something like:

Function(Number)
 
Select Case Number.Value
 
Case "2"
ActiveCell.FormulaR1C1 = _
"USA"
 
end select
 
end function

That also didn't work, but I could perhaps use that to accomplish a similar feat since I would only have 900 cases or so.

 

[largeselection]

I got the second part to work using:

Function CClookup(number as string)
select case number
Case is = "3"
CClookup = "USA"
.
.
.
end select
 
end function

Thanks for all your help!