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.