Thread: UDF causes #value error whenever copying cells

1) The following code works well except for 1 wierd circumstance. This UDF code resides in a Cell in workbook 'A'. If I have another workbook open (call it workbook 'B'), and I do ANYTHING in workbook 'B' (like copy a cell to another cell within workbook 'B', or perform a calculation within workbook 'B', or even copy a cell from 'A' to 'B'), then the cell that this UDF resides in (workboook 'A') will change from a good number to a #VALUE error. If I work ONLY within 'A', then this UDF functions properly (giving a good result).
2) If I hit the F9 button (i.e. re-calc), with workbook 'A' active, then the #VALUE goes away, and the good number comes back. To clarify, all workbooks have AUTO CALC enabled. I guess the 'Application.Volatile' line causes this code to run when I hit the F9 button (thereby clearing the #VALUE error). If I hit F9 with 'B' active, then the #VALUE error (back in 'A') goes NOT clear.
3) NOTE #1: If I put a BREAKPOINT inside this code, and then repeat a "copy from cell to cell" within workbook 'B', I find that the following code DOES NOT execute, as it does NOT "stop" the code at the BREAKPOINT line. But, when I hit F9, then it DOES stop at the BREAKPOINT.
4) NOTE #2: If I "comment out" the Application.Volatile line, it still does the SAME thing. Except worse, then when I hit F9, it doesn't correct the #VALUE error. I really need the Application.Volatile step with this code, as these functions will not automatically update if their referenced cells are changed.
5) NOTE #3: The variables Row_Num & Column_Num are declared at the Module Level. If I use unique variables and declare these at the Procedure Level, it makes NO difference. I also tried putting a "DoEvents" line after Application.Volatile, but NO difference.
6) It seems I need this code to execute whenever there is a "change" on ANY workbook. Or else, do something to keep these cell values from changing anytime there is "action" on another workbook. Or, is there better code to do the samething? ... Do you have any CLUE how to fix this SNAG???

Function GetQLT(Search_Item As Variant, Header_Code As Variant) As Variant
 Application.Volatile True 'Forces function to re-calc if anything changes on worksheet
 Row_Num = WorksheetFunction.Match(Search_Item, Range("DOC.QltTbl.PrdFam.c"), 0)
 Column_Num = WorksheetFunction.Match(Header_Code, Range("DOC.QltTbl.IT_Name.r"), 0)
 GetQLT = WorksheetFunction.Index(Range("DOC.QltTbl.x"), Row_Num, Column_Num)
End Function

I'm not sure it's worth getting into the debate as to why you're using a UDF to do this but...

Given the UDF has been made Volatile cells utilising it will recalculate upon any Volatile action taking place.

It follows that if that action occurs in another file then the unqualified range references will become invalid given they are applied to the Active Worbook which is not the one containing the relevant ranges.... so perhaps utilise Application.Caller ?

(there may be better methods though... generally speaking I try to avoid Volatile UDFs so don't have a great deal of experience)

If you pass the ranges you need, the function will recalculate automatically without requiring it to be volatile:

Function GetQLT(vRowFind As Variant, vColFind As Variant, _
                rRowLook As Range, rColLook As Range, _
                rTbl As Range) As Variant
    
    Dim iRow        As Long
    Dim iCol        As Long

    With WorksheetFunction
        iRow = .Match(vRowFind, rRowLook, 0)
        iCol = .Match(vColFind, rColLook, 0)
        GetQLT = rTbl(iRow, iCol).Value
    End With
End Function

I haven't ben able to reproduce your problem with the code that you provided. It appears to work just fine. Maybe you have narrowed down your problem into the work area. I would check your definitions of the range variables. Are they static, or are you changing them?

The problem is as DO described, and it could be solved with Application.Caller, but it's bad practice not to pass UDFs all of their dependents and then try to paper it over by making it volatile.

EDIT:

Originally Posted by DO

... why you're using a UDF to do this but ...

That too.

Thanks shg - yes I should perhaps have elaborated on that myself - I'm not condoning Volatile UDFs ... but I'd say if you end up passing all range variables you may as well just use the native syntax.

This is an interesting one though... a while ago my old boss used to complain how long it took to type in an INDEX with embedded MATCH functions etc... so I spent some time coming up with an automatic shorthand translator - don't ask...
ie you type in short hand and it wrote back the native function... I know... I'll see if I can find it (so you can have a laugh at my expense)

Ha, I found it....

Private Sub Worksheet_Change(ByVal target As Range)
Dim str_temp As String, str_formula As String
Dim v_args As Variant
Dim cell As Range, rng_tbl As Range
Dim l_rng_tbl_rows As Long, l_rng_tbl_cols As Long
On Error GoTo Fatal
For Each cell In target.Cells
    If InStr(cell.Formula, "=TLOOKUP") = 0 Then Exit Sub
    str_temp = Replace(cell.Formula, "=TLOOKUP(", "")
    str_temp = Left(str_temp, Len(str_temp) - 1)
    v_args = Split(str_temp, ",")
    'define INDEX left most cell
    Set rng_tbl = Range(v_args(2))
    'define INDEX table based off left most cell and rows/columns
    l_rng_tbl_rows = Cells(rng_tbl.Parent.Rows.Count, rng_tbl.Column).End(xlUp).Row - (rng_tbl.Row - 1)
    l_rng_tbl_cols = Cells(rng_tbl.Row, rng_tbl.Parent.Columns.Count).End(xlToLeft).Column - (rng_tbl.Column - 1)
    Set rng_tbl = rng_tbl.Resize(l_rng_tbl_rows, l_rng_tbl_cols)
    'test to see if Target intersects range - if so means error -- ie table to left of TLOOKUP formula (circular references)
    If Not Intersect(target, rng_tbl) Is Nothing Then GoTo Fatal
    'translate formula to non-volatile equivalent thereof
    str_formula = "INDEX(" & rng_tbl.Address & "," & _
                        "MATCH(" & v_args(0) & ",INDEX(" & rng_tbl.Address & ",0,1),0)," & _
                        "MATCH(" & v_args(1) & ",INDEX(" & rng_tbl.Address & ",1,0),0)" & _
                    ")"
    If UBound(v_args) = 3 Then str_formula = "IFERROR(" & str_formula & "," & v_args(3) & ")"
    're-enter revised formula (disable events)
    Application.EnableEvents = False
    cell.Formula = "=" & str_formula
    Application.EnableEvents = True
    'release memory
    Set rng_tbl = Nothing
Next cell
Exit Sub

'handler to enforce events
Fatal:
MsgBox "Er... Error..."
Application.EnableEvents = True
End Sub

Pre-emptive defences...

a) I wrote this a while ago (in an afternoon as I recall)
b) In the above guise it's aimed at 2007 (use of IFERROR)
c) it assumes match_type of 0 at all times
d) it expects to be entered stand alone, ie not embedded or used multiple timeswithin one function

Basic premise being that to enter the INDEX/MATCH you would simply enter:

=TLOOKUP(horizontal criteria,vertical criteria,top left cell of table [,optional error value])

on entry the above would convert to native function syntax so as to keep non-UDF but allow for short hand entry.

seemed a good idea (for about a day)

Might be useful to someone... you never know.

I like this line best:

    MsgBox "Er... Error..."

Ha, these days I prefer to use "Computer Says No" as my dialog...

This is based on a classic sketch from the comedy show over here "Little Britain"

Originally Posted by sauerj

I am trying to get the Application.Caller idea to work

In the most basic terms

With Application.Caller
    Row_Num = WorksheetFunction.Match(Search_Item, .Range("DOC.QltTbl.PrdFam.c"), 0)
    'etc...
End With

You say your co-workers are impatient ... well UDF's are generally speaking slower than native functions

It might be worth having a read though of Charles Williams' thoughts on the matter: http://www.decisionmodels.com/calcsecretsj.htm

..., well UDF's are generally speaking slower than native functions

Not to mention having them recalculate unnecessarily.

FWIW, I would put good money on the fact that your worksheet needs a major redesign, if that's part of an already huge formula...