#1
|
|||
|
|||
Need help detecting the reason for a strange behavior of a UDF
I have used a UDF function in sheet1!C7 and it shows me the expected result. But when I used this function in a cell outside sheet1 (sheet2!A1) with the same input, it unexpectedly showed me zero. (When I first wrote this UDF function in sheet2!A1, it gave me the expected result, but when I clicked on it, surprisingly its value changed and it showed zero. It seems this UDF function has this problem when dealing with inputs which are in another worksheet. The reason I am using this UDF function except Excel function like Vlookup is that my routine work in excel is such that I should constantly determine the value of some values that I do not know their exact position)
In addition to this problem, another problem is that when referring to another workbook, this UDF function gives #VALUE! Error when that workbook is closed. It would be very kind of you, if you give me some guides on how to deal with such problems. |
#2
|
||||
|
||||
1. Don't name a module the same as a function - it'll confuse Excel.
2. You need to use sheet-qualified references. It will work on other sheets interrogating any (other) sheet within a workbook. The udf won't work on closed workbooks. 3 Pecoflyer's solution here: https://www.msofficeforums.com/169449-post4.html will work on closed workbooks. Be aware that this will give accurate results as long as:If you're using Office365 the formula can be written into a LET formula which means the range you're searching need only be referred to once in the formula, or easier, it could be written into a LAMBDA function which you'd use in just the same way as you're using the UDF I wrote for you earlier. |
#3
|
|||
|
|||
@p45cal. Thank you for your answer. I understand the problem of using the same name for modules and UDFs, but, as the attached file, I am used to use “01” at the end of modules name to differentiate them from UDf names. Does this approach also confuse excel? By “you need to use sheet-qualified references “do you mean I should modify my code or the way the inputs are addressed in the UDF? Can you provide a link that describe your point? (I searched web by “sheet-qualified references for u VBA” but I got results that were mostly related to SUBs not UDFs)
|
#4
|
||||
|
||||
In your file the module and function are both just called matchrow.
|
#5
|
||||
|
||||
No.
for example: Code:
=matchrow(Sheet1!A2,Sheet71!C3:E9) Code:
=matchrow([AnotherBook.xlsx]Sheet1!$A$2,[AnotherBook.xlsx]Sheet1!$C$3:$E$7) |
#6
|
|||
|
|||
@Ep45cal. Excuse me very much. By @Debaser comment, I understood that I accidentally have attached the wrong file. The correct file is current attachment.(the edit option was not available to modify the original question).
. If you click on sheet2!A1 you can see the problem. Its references are correct and when first written It gave me the correct answer but when I clicked on it its result unexpectedly changed to 0zero. |
#7
|
||||
|
||||
You've Dimmed all variables at module level. I suspect it's to do with that although I can't reproduce the problem here.
only Dim the variables you need to at the function level: Code:
Function matchrow(x, interval) Dim a As Range Set a = interval.Find(x, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, searchformat:=False) If Not a Is Nothing Then matchrow = a.Row Else matchrow = "Not found" End Function Function matchcollum(y, interval) Dim a As Range Set a = interval.Find(y, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, searchformat:=False) If Not a Is Nothing Then matchcollum = a.Column Else matchcollum = "Not found" End Function Function vlookupUDF(x, y, interval) Dim nrow As Long, ncollumn As Long nrow = matchrow(x, interval) ncollumn = matchcollum(y, interval) vlookupUDF = Cells(nrow, ncollumn) End Function |
#8
|
||||
|
||||
Your issue is here:
Code:
vlookupUDF = Cells(nrow, ncollumn) |
#9
|
||||
|
||||
Quote:
Ah yes, well spotted. Code:
vlookupUDF = interval.parent.Cells(nrow, ncollumn) edit: just noticed you used interval.worksheet.cells ; I've never seen that, going to try it now. |
#10
|
|||
|
|||
Thank you very much. The issue was solved.
|
Tags |
udf function |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange behavior with Word macro | Ulodesk | Word | 5 | 06-04-2022 12:40 AM |
Strange Behavior with a Graph in Word | RP McIntosh | Drawing and Graphics | 6 | 07-01-2019 08:29 PM |
strange search behavior | cyraxote | Word VBA | 0 | 05-05-2017 03:00 PM |
Strange macro behavior in 2016 | Ulodesk | Word VBA | 5 | 04-02-2017 03:34 PM |
Pasting - strange behavior | SatControl | Word | 5 | 02-14-2010 12:57 PM |