For a UDF

=Personal.xls!CaseVlook(args)


Gord Dibben MS Excel MVP

On Sun, 5 Jul 2009 02:58:00 -0700, Mike H
wrote:

Hi,

You need to tell Excel it's in Personal.Xls

Application.Run "Personal.xls!CaseVLook............etc

Mike

"Jack Sons" wrote:

Hi all,

When I put a UDF in a module of the workbook in which I want to use the UDF
the result is #NAME?
When I put a UDF in a module of personal.xls I can use it in any other xls,
I thought. But when I do, the result also is #NAME?
Strange enough, UDF's that I put in modules of workbooks or personal.xls
long ago do work flawlessly.

See the most recent example below.

What goes wrong and how to correct it?

TIA

Jack Sons
The Netherlands
---------------------------------------------------------------------------------------------------
'To use the macro, simply call the function with the value you want to find
'(say cell C1), the range whose first column should be searched
'(such as A:B), and optionally the offset of the column within that range,
as he
'=CaseVLook(C1;A:B;2) semicolons because of my European version of Excel
2000.


Function CaseVLook(compare_value, table_array As Range, _
Optional col_index As Integer = 1)
Dim c As Range
Dim rngColumn1 As Range

Application.Volatile

Set rngColumn1 = table_array.Columns(1)
CaseVLook = "Not Found"

'Loop first column
For Each c In rngColumn1.Cells
If c.Value = compare_value Then
CaseVLook = c.Offset(0, col_index - 1).Value
Exit For
End If
Next c
End Function