UDF using Vlookup returning a VALUE error

[Pauline10]

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

 

[Zot]

What do you expect the Vachercher variable as? Long, String, etc. Or maybe just try to put

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

 

[Fluff]

Hi & welcome to MrExcel.
Is the other workbook open?

 

[Pauline10]

What do you expect the Vachercher variable as? Long, String, etc. Or maybe just try to put

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 ?

 

[Pauline10]

Hi & welcome to MrExcel.
Is the other workbook open?

Hello Fluff,

Thank you ! After reading your comment, I tried it with the workbook opened and closed and no luck...

 

[Fluff]

Yes, but will the workbook normally be open?

 

[Zot]

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?

 

[Pauline10]

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.

 

[Fluff]

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

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

 

[kweaver]

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