Try this

Public Function addsome(InputValue As Double, AddWhat As String) As Double
Application.Volatile
Dim ToAdd As Double
ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _
Application.Caller.Parent.Range("TestRange"), 2, False)
addsome = InputValue + ToAdd
End Function


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"makulski" wrote in message
...
Well rather than post my UDF, which is rather elaborate, I tried to
recreate
the problem with a simpler UDF.

My first attempt didn't exhibit the problem described.

public function addone(InputValue As Double) As Double
Application.Volatile
addone = InputValue + 1
End Function

That worked fine. So I made it closer to my UDF by adding a
Application.Worksheet function. Create a named range called TestRange 2
columns by 3 rows. Put values in it like this:
Five 5
Six 6
Seven 7

Now, create this function:

Public Function addsome(InputValue As Double, AddWhat As String) As Double
Application.Volatile
Dim ToAdd As Double
ToAdd = Application.WorksheetFunction.VLookup(AddWhat,
Range("TestRange"), 2, False)
addsome = InputValue + ToAdd
End Function

If you put thius into a worksheet cell =addsome(A1,"Five")
Calc it. If A1 contains 1, the result will be 6.
Go to another workbook, hit calc.
Now return to the UDF workbook, and the result is #VALUE
Hit calc and it resumes being 6.

I hope that is more or less complete.
So it looks like my problem is the way I am using Worksheetfunction or
Range()
Any corrections you see I need to make.
Thanks.



"Bob Phillips" wrote:

Sounds like a problem in the UDF. Post it.