Using UDF on multiple sheets causes #NAME? error

[VfTom]

Hi, I need some help with the following.
I have two workbooks where I need to do a lookup in between and obtain a value.
To be more precise there is one active mastersheet which contains all the data, and a map with separate sheets, and each sheet does a lookup on the mastersheet and obtain a specific value.

The lookup is done on multiple conditions.
The lookup value from the separate sheet is checked on the first column for the name, rest of the columns for the price, and color (only yellow marked items should be considered).

I also haved a third workbook where I have placed the whole code and the UDF.

This UDF works great within one workbook.
But in this setting with 3 workbooks I receive a #NAME? error. And this is what I need some help with.

I did a small research on this and I found there are solutions for this like:
- save the UDF in a personal.xls file, xla file or qualify the call with: =[Your Workbook.xls]MyFunct(Arg1, Arg2)

making a user-defined function accessible to all workbooks

I've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?

www.mrexcel.com

I've tried to place the UDF in a personal.xls file but that didn't work either
Is there an another way?
Thank you in advance for looking into it.

Here below an examle one of the separate sheets :

fruits.xlsx
	A	B	C
1	Company
2	4Fruits
3
4	Fruit	Price
5	Red Apple	#NAME?
6	Green Apple	#NAME?
7
partner

Cell Formulas
Range	Formula
B5:B6	B5	=GET_PRICE([fruits_source.xlsm]prices!$A$1:$D$7,$A$1,A5)

Here below an example of the master sheet:

fruits_source.xlsm
	A	B	C	D	E
1	Partner	Golden	Red Apple	Green Apple
2	FruitsRUs	€ 1	€ 1	€ 1
3
4	4Fruits	n/a	€ 1	€ 3
5
6	OneADay	€ 1	n/a	n/a
7
8
prices

And here below the UDF:

Public Function GET_PRICE(ByVal RngPrices As Range, ByVal vPartner As String, vType As String) As Variant Dim vRow As Long Dim vColumn As Long With Application.WorksheetFunction vRow = .Match(vPartner, RngPrices.Columns(1), 0) vColumn = .Match(vType, RngPrices.Rows(1), 0) End With 'check color If RngPrices.Cells(vRow, vColumn).Interior.Color = vbYellow Then GET_PRICE = RngPrices.Cells(vRow, vColumn).Value Else 'not yellow, return N/A GET_PRICE = "Nope" End If End Function

 

[RoryA]

If the UDF is in your personal macro workbook, you need to qualify the call with the name of the workbook - eg =Personal.xlsb!GET_PRICE([fruits_source.xlsm]prices!$A$1:$D$7,$A$1,A5)

Bear in mind that that UDF will not work if the source workbook is closed.

 

[offthelip]

I recommend saving your UDF in an addin since once you have set it up and activated it, it is very easy to keep adding new functions to the addin , so I find I am writing new functions all the time , I just save them in the addin just in case I want to use somewhere else. You can then call them in any workbok just like an excel function

 

[VfTom]

If the UDF is in your personal macro workbook, you need to qualify the call with the name of the workbook - eg =Personal.xlsb!GET_PRICE([fruits_source.xlsm]prices!$A$1:$D$7,$A$1,A5)

Bear in mind that that UDF will not work if the source workbook is closed.

thank you RoryA , something changed but now I get a #VALUE! error, any idea what that can be?

 

[VfTom]

I recommend saving your UDF in an addin since once you have set it up and activated it, it is very easy to keep adding new functions to the addin , so I find I am writing new functions all the time , I just save them in the addin just in case I want to use somewhere else. You can then call them in any workbok just like an excel function

thank you offthelip, the code should also be used by other users who don't have this plugin installed.

 

[offthelip]

thank you offthelip, the code should also be used by other users who don't have this plugin installed.

If other users need to use the function you can't put it in your personal.xlb either, the only way is to include in every workbook you send them . Alternatively you can get your users to install the addin themselves, I do have some clients that have done exactly that with my addin with UDF becausethey have found them useful

 

[VfTom]

If other users need to use the function you can't put it in your personal.xlb either, the only way is to include in every workbook you send them . Alternatively you can get your users to install the addin themselves, I do have some clients that have done exactly that with my addin with UDF becausethey have found them useful

you're right, I want to place the UDF in a third sheet where also the rest of the code is and can be used by others, but I'm sure these users will not download any addin's ...

 

[VfTom]

thank you RoryA , something changed but now I get a #VALUE! error, any idea what that can be?

I checked again and it's working now, thanks