[RESOLVED] Custom function results in #Value! errors
Please help. I wrote a custom Excel function that works great. However, when I create a copy of the sheet that uses the function, move that new copied sheet to a new workbook, and then return to my original sheet (that uses the function), all of the function results become #Value! error cells. Sometimes doing an 'activesheet.calculate' resolves the errors (after waiting a period of time), and sometimes I have to copy and paste the cells containing the function call; i.e., refresh the formulas.
Is there something I can do to prevent the #Value! errors, or do I have to workaround the problem by recalculating or refreshing the formulas that contain the function call?
Thanks.
Last edited by doasidont; Oct 22nd, 2013 at 09:09 AM.
Shot in the dark: You have "Active"-Somethings in your function (ActiveWorkbook, ActiveSheet)
EDIT: I just saw your
Sometimes doing an 'activesheet.calculate' resolves the errors
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Function Find_Contract(cNum As String)
Dim aNum As Object, pNum As Object
Dim searchRange As Range
Set aNum = Sheets("Contract Pseudos").[Actual_Contract_Number].Find(cNum, LookAt:=xlWhole)
If Not aNum Is Nothing Then
Find_Contract = aNum
GoTo endFunc
Else
With Sheets("Contract Pseudos")
Set searchRange = Intersect(.[Data], .[pseudoCols])
Set pNum = searchRange.Find(cNum)
If Not pNum Is Nothing Then
Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column)
' Check if this is an ambiguous contract number
If .Cells(pNum.Row, .[Ambiguous].Column) = "Yes" Then
' If so, add an indicator
Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column) & "-AMB"
End If
GoTo endFunc
End If
End With
End If
Find_Contract = "NA"
endFunc:
Set aNum = Nothing
Set pNum = Nothing
End Function
Last edited by si_the_geek; Oct 24th, 2013 at 12:59 PM.
Reason: corrected typo in code tags
Any ideas on this? Also, when I use this function in several hundred cells, even with calc set to manual, Excel reports that it cannot continue due to low resources. Please help.
I wish I could but it contains sensitive info. I've tried to workaround by manually copying and repasting the column that contains the custom function, recalcing, but the only thing that works, which is not practical, is double clicking on a cell in the column. When I do that and exit the cell, the correct function result appears in the cell.
I'll try to create a workbook that recreates the problem. One more bit of information. The formula that uses the custom function is inserted in the column with VBA. If I remove the code that inserts these formulas from the subroutine and place that code in another macro, allow the subroutine to complete, and then manually run the new macro, the macro runs without running out of resources, but the workbook expands to 10 times its original size.
Creating another workbook will take more work than I thought, so I have to think about how I'd do that. More info, though. It's only the FillDown cells that get the #Value! error. The formula that uses the customer function looks okay, but all of the cells except the first one do not calculate the correct value. Is there some characteristic, attribute that I have to give the function so that it calculates on demand?
Short of writing a custom function, is there another way that I can find a value within a table using standard Excel functions? The table is less than 100 rows and 15 columns. Thanks.
Here is simply example of table. The custom function first searches for the first name. If not found, it searches for nick names. If a nick name is found, it returns the first name.
is there another way that I can find a value within a table using standard Excel functions?
quite possibly, depending on the setup of the table, as to how it can work, using vlookups, match or countifs
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
vlook's won't work because the table is not sorted. I think you can match on a column or a row, but I don't think you can match to a table of multiple rows and columns (can you?). Maybe my function is poorly written. If someone can suggest a better way, I'd appreciate it.
as i can not see your table, hard to guess
i can only open .xls files
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
where cell h1 contains the name /nickname to lookup
i would believe this could be extended to 8 columns, this may have been changed in later versions
this is tested on your sample sheet
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Thanks, Westconn1, but there are at least 11 columns. And thanks, 3com, but your code won't work for me. My function works but it returns #Value! in all cells that receive the FillDown formula. If I loop through all the cells in the column, inserting the formula that call the function, it takes forever to do all of the cells (thousands). If I set calc to manual, the cells formulas in the cells don't work; i.e., I get funccall(a2) in all cells instead of funcall(a2), funccall(a3), etc. Something is preventing the functions from returning a good value instead of #Value!
i am not sure i understand, are you looking up the name from each row on some other sheet?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
I'll try to get your point, but I've some questions.
[Actual_Contract_Number], .[Data], .[pseudoCols],[Ambiguous]; they are mameranges, column names???
cNum store user names???
if so, I assume you've a process passing username value to the function and return Actual_Contract_Number,
but what is waiting for???
I think perhaps instance book you are using (including all formulas nameranges, macros etc.) is needed. Of course, replacing sensitive info.
I had downloaded your zip, but I can't see the formula calling any function. except columns names formulae.
well, I imagined the following scenario:
Obviously you will have to adapt this code to your code.
Code:
Sub call_Func()
Dim strname
strname = Range("G1").Value
Find_Contract (strname)
End Sub
Function Find_Contract(cNum As String)
Dim aNum As Object, pNum As Object
Dim searchRange As Range
Set aNum = Sheets("Contract Pseudos").[First_Name].Find(cNum, LookAt:=xlWhole)
If Not aNum Is Nothing Then
cAddr = aNum.Address
nContract = Range(cAddr).Offset(0, 5).Value
Find_Contract = nContract
MsgBox nContract, vbInformation, "doasidont"
GoTo endFunc
Else
With Sheets("Contract Pseudos")
Set searchRange = Range("Nick_Name")
Set pNum = searchRange.Find(cNum)
If Not pNum Is Nothing Then
Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column)
MsgBox "Find_Contract is " & Find_Contract, vbInformation, "doasidont"
' Check if this is an ambiguous contract number
If .Cells(pNum.Row, .[Ambiguous].Column) = "Yes" Then
' If so, add an indicator
Find_Contract = .Cells(pNum.Row, .[Actual_Contract_Number].Column) & "-AMB"
MsgBox "Find_Contract is " & Find_Contract, vbExclamation, "doasidont"
End If
GoTo endFunc
End If
End With
End If
Find_Contract = "NA"
MsgBox "Find_Contract is " & Find_Contract, vbCritical, "doasidont"
endFunc:
Set aNum = Nothing
Set pNum = Nothing
End Function
Type First name you are looking for, and run macro.
Thanks for all of your help. I redesigned my contract and pseudo contract (name and nickname) worksheet, and no lopnger have the #Value! problem. I still don't understand why I got it because I have even a more complex custom function that works fine. But for now, this is no longer a problem.
[RESOLVED] Custom function results in #Value! errors
Reply With Quote
Originally Posted by westconn1
,0)),IF(ISNA(MATCH(H1,E:E,0)),,MATCH(H1,E:E,0)),MATCH(H1,D
