UDF using Vlookup returning a VALUE error

Pauline10

New Member
Joined
Feb 22, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I'm a debutant in VBA (and I'm French, so please pardon the English errors coming ahead !) and am trying to create a UDF including a vlookup.
The problem is that it returns a VALUE error.
I frequently have to vlookup values in a recurrent table, and so to save time I would like to create a UDF that already includes the reference to this table, the only variable would be the lookup value :

Function Vachercher(a As Variant)

x = Workbooks("C:\Users\julie DUPRE\Documents\21-02-17 - FACTURES 14-21.xlsx").Worksheets("Restitution").Range("B2:BT36000")

Vachercher = Application.WorksheetFunction.VLookup(a, x, 58, False)

End Function

I don't understand the VALUE error...I've checked several times that my references and my range are correct and it seems there are. I've also tried the same UDF but without an external reference to a workbook to see if that was the problem (so i tried the udf within the worksheet containing the table) but with no luck.

I would greatly appreciate some help !

Pauline
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
What do you expect the Vachercher variable as? Long, String, etc. Or maybe just try to put

VBA Code:
Function Vachercher(a As Variant) as Variant
x = Workbooks("C:\Users\julie DUPRE\Documents\21-02-17 - FACTURES 14-21.xlsx").Worksheets("Restitution").Range("B2:BT36000")
Vachercher = Application.WorksheetFunction.VLookup(a, x, 58, False)
End Function
 
Upvote 0
Hi & welcome to MrExcel.
Is the other workbook open?
 
Upvote 0
What do you expect the Vachercher variable as? Long, String, etc. Or maybe just try to put

VBA Code:
Function Vachercher(a As Variant) as Variant
x = Workbooks("C:\Users\julie DUPRE\Documents\21-02-17 - FACTURES 14-21.xlsx").Worksheets("Restitution").Range("B2:BT36000")
Vachercher = Application.WorksheetFunction.VLookup(a, x, 58, False)
End Function
Hello Zot,

Thank you for your help. I would expect a string. So after I read your reply, I've tried :

1. Function Vachercher(a As Variant) as Variant : no luck
2. Function Vachercher(a As String) : no luck
3. Function Vachercher(a As String) as string : no luck either

  • Then I've made other attempts including referring only to the worksheet, and successfully launching the UDF within the workbook (and in another worksheet) :

Function Vachercher3(a As Variant)

y = Worksheets("Restitution").Range("B2:BT36000")

Vachercher3 = Application.WorksheetFunction.VLookup(a, y, 58, False)

End Function

  • I then tried the exact same function but simply adding the reference to the workbook (I changed the name of the workbook to "TEST") :

Function Vachercher5(a As Variant)

y = Workbooks("C:\Users\Pauline DUPLOYE\Documents\TEST.xlsx").Worksheets("Restitution").Range("B2:BT36000")

Vachercher5 = Application.WorksheetFunction.VLookup(a, y, 58, False)

End Function

And it didn't work : value error again.

So it seems like the reference to the workbook is the problem ? I've been careful to copy paste the path to it. I've tried with the workbook opened and closed.... Strange, isn't it ?
 
Upvote 0
Yes, but will the workbook normally be open?
 
Upvote 0
Hello Fluff,

Thank you ! After reading your comment, I tried it with the workbook opened and closed and no luck...
What @Fluff meant was that did you execute the UDF with the Workbook TEST opened?
 
Upvote 0
What @Fluff meant was that did you execute the UDF with the Workbook TEST opened?
I executed it twice. First with the workbook TEST opened. And second, since the UDF didn't work with TEST opened, I executed it with TEST closed (to see if the fact that the workbook was closed or opened was the reason why the UDF didn't work). But ideally, I would like the UDF to be executed while the workbook is closed.
 
Upvote 0
But ideally, I would like the UDF to be executed while the workbook is closed.
In that case I think you are out of luck. I know of no way for a UDF to access a closed workbook.
If the workbook was open you would use
VBA Code:
Function Vachercher(a As Variant)

x = Workbooks("21-02-17 - FACTURES 14-21.xlsx").Worksheets("Restitution").Range("B2:BT36000")
Vachercher = Application.VLookup(a, x, 11, False)

End Function
 
Upvote 0
Solution
So, is it NOT possible to set the range from an (open) workbook such as the "x" variable used above?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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