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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
919
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Is the other workbook open?
 

Pauline10

New Member
Joined
Feb 22, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Pauline10

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Yes, but will the workbook normally be open?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
919
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 22, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,561
Office Version
  1. 365
  2. 2010
So, is it NOT possible to set the range from an (open) workbook such as the "x" variable used above?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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