User Defined Function problem

[Nimo]

Hi All,
I'm trying to get to grips with creating UDF's.

I have put the following together and it works fine if it's on the same sheet in the same
workbook but it won't work between two workbooks.

Any help as to what I'm doing wrong would be appreciated.

It's my version of a backward lookup where I can key in a negative number for coln.

Function Revlkup(Lval As Variant, rng1 As Range, coln As Integer)

Dim lval2 As Range

For Each lval2 In Range(rng1.Address)


If lval2 = Lval Then

Revlkup = lval2.Offset(0, coln)
Exit Function

Else

Revlkup = "No match found"

End If


Next lval2

End Function

Rgds

Nimo

 

[dk]

Hi,

If you want to use the function in another workbook then you have a few options.

1. You can prefix your function name with the name of the workbook that contains your function e.g. =Yourworkbook.xls!Revlkup(A1, B1:C10,5)

2. You can put your function into an add-in. If you just save the workbook as an xla file you can then use your function in any workbook without having to use the workbook name. If you have a number of custom UDFs then this is a good way to store them and it also means that you can easily distribute the functions to others.

HTH
DK

 

[Nimo]

Hi DK,
Thanks for the response.

I forgot to mention I have it saved and installed as an add in so any workbook I use it in is fine. The problem occurs when I try to pull information from a different workbook.

Both workbooks are in the same instance of excel.

Any ideas?

Nimo

 

[RoryA]

Function Revlkup(Lval As Variant, rng1 As Range, coln As Integer)

Dim lval2 As Range
Application.Volatile True
For Each lval2 In rng1.cells


If lval2 = Lval Then

Revlkup = lval2.Offset(0, coln)
Exit Function

Else

Revlkup = "No match found"

End If

Next lval2

End Function

 

[Nimo]

Thanks rorya.

Works perfect.

Rgds

Nimo

 

[dk]

OK, sorry I misunderstood.

The reason your code is not working is because of this:

For Each lval2 In Range(rng1.Address)

The part in bold is not qualified with a worksheet or workbook reference so by default it will use the active sheet. You should be able to get the function to work by changing the line to this:

For Each lval2 In rng1.Cells


HTH
DK

 

[dk]

too slow

 

[Nimo]

Thanks for the explanation DK.

Nimo