Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-06-2022, 12:00 AM
soroush.kalantari soroush.kalantari is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2016
Advanced Beginner
Need help detecting the reason for a strange behavior of a UDF
 
Join Date: Jun 2021
Posts: 40
soroush.kalantari is on a distinguished road
Default 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.
Attached Files
File Type: xlsm userdifiedmatch.xlsm (16.2 KB, 4 views)
Reply With Quote
  #2  
Old 08-06-2022, 05:17 AM
p45cal's Avatar
p45cal p45cal is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2019
Expert
 
Join Date: Apr 2014
Posts: 639
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

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:
Quote:
Originally Posted by soroush.kalantari View Post
The values which I want to determine their rows are unique and occur just one time
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.
Reply With Quote
  #3  
Old 08-08-2022, 08:16 PM
soroush.kalantari soroush.kalantari is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2016
Advanced Beginner
Need help detecting the reason for a strange behavior of a UDF
 
Join Date: Jun 2021
Posts: 40
soroush.kalantari is on a distinguished road
Default

@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)
Reply With Quote
  #4  
Old 08-09-2022, 01:26 AM
Debaser's Avatar
Debaser Debaser is offline Need help detecting the reason for a strange behavior of a UDF Windows 7 64bit Need help detecting the reason for a strange behavior of a UDF Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 196
Debaser will become famous soon enough
Default

In your file the module and function are both just called matchrow.
Reply With Quote
  #5  
Old 08-09-2022, 02:13 AM
p45cal's Avatar
p45cal p45cal is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2019
Expert
 
Join Date: Apr 2014
Posts: 639
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Quote:
Originally Posted by soroush.kalantari View Post
do you mean I should modify my code
No.

Quote:
Originally Posted by soroush.kalantari View Post
or the way the inputs are addressed in the UDF?
for example:
Code:
=matchrow(Sheet1!A2,Sheet71!C3:E9)
or if in another workbook which is open:
Code:
=matchrow([AnotherBook.xlsx]Sheet1!$A$2,[AnotherBook.xlsx]Sheet1!$C$3:$E$7)
Use the mouse to select the ranges and the references will be correct automatically.
Reply With Quote
  #6  
Old 08-09-2022, 08:05 PM
soroush.kalantari soroush.kalantari is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2016
Advanced Beginner
Need help detecting the reason for a strange behavior of a UDF
 
Join Date: Jun 2021
Posts: 40
soroush.kalantari is on a distinguished road
Default

@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.
Attached Files
File Type: xlsm vlookupUdF.xlsm (17.4 KB, 3 views)
Reply With Quote
  #7  
Old 08-11-2022, 04:01 AM
p45cal's Avatar
p45cal p45cal is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2019
Expert
 
Join Date: Apr 2014
Posts: 639
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

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
See if that helps.
Reply With Quote
  #8  
Old 08-11-2022, 09:08 AM
Debaser's Avatar
Debaser Debaser is offline Need help detecting the reason for a strange behavior of a UDF Windows 7 64bit Need help detecting the reason for a strange behavior of a UDF Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 196
Debaser will become famous soon enough
Default

Your issue is here:

Code:
vlookupUDF = Cells(nrow, ncollumn)
that will return the value from whichever sheet is active when the formula calculates. You should use interval.worksheet.cells(nrow, ncollumn)
Reply With Quote
  #9  
Old 08-11-2022, 09:14 AM
p45cal's Avatar
p45cal p45cal is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2019
Expert
 
Join Date: Apr 2014
Posts: 639
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Quote:
Originally Posted by Debaser View Post
Your issue is here:

Code:
vlookupUDF = Cells(nrow, ncollumn)
that will return the value from whichever sheet is active when the formula calculates. You should use interval.worksheet.cells(nrow, ncollumn)

Ah yes, well spotted.
Code:
vlookupUDF = interval.parent.Cells(nrow, ncollumn)
should do it (untested).
edit:
just noticed you used interval.worksheet.cells ; I've never seen that, going to try it now.
Reply With Quote
  #10  
Old 08-12-2022, 07:05 PM
soroush.kalantari soroush.kalantari is offline Need help detecting the reason for a strange behavior of a UDF Windows 10 Need help detecting the reason for a strange behavior of a UDF Office 2016
Advanced Beginner
Need help detecting the reason for a strange behavior of a UDF
 
Join Date: Jun 2021
Posts: 40
soroush.kalantari is on a distinguished road
Default

Quote:
Originally Posted by Debaser View Post
Your issue is here:

Code:
vlookupUDF = Cells(nrow, ncollumn)
that will return the value from whichever sheet is active when the formula calculates. You should use interval.worksheet.cells(nrow, ncollumn)
Thank you very much. The issue was solved.
Reply With Quote
Reply

Tags
udf function

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help detecting the reason for a strange behavior of a UDF Strange behavior with Word macro Ulodesk Word 5 06-04-2022 12:40 AM
Need help detecting the reason for a strange behavior of a UDF 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
Need help detecting the reason for a strange behavior of a UDF Strange macro behavior in 2016 Ulodesk Word VBA 5 04-02-2017 03:34 PM
Need help detecting the reason for a strange behavior of a UDF Pasting - strange behavior SatControl Word 5 02-14-2010 12:57 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft