I have a spreadsheet that uses some user defined functions.
I have included in the code application.volitile, so they recalc when the
workbook needs calculating.
They are public functions and are located in module1.

All seems to work fine, except ...
if I open another spreadsheet or do a calc in another spreadsheet, when I
return to the workbook containing the UDFs, all of the UDF cells show #VALUE.
If I hit the calc button, the UDFs all calculate.

Question: How can I get the UDFs to calculate when I have other workbooks
open at the same time?

Sounds like a problem in the UDF. Post it.

--
---
HTH

Bob


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



"makulski" wrote in message
...
I have a spreadsheet that uses some user defined functions.
I have included in the code application.volitile, so they recalc when the
workbook needs calculating.
They are public functions and are located in module1.

All seems to work fine, except ...
if I open another spreadsheet or do a calc in another spreadsheet, when I
return to the workbook containing the UDFs, all of the UDF cells show
#VALUE.
If I hit the calc button, the UDFs all calculate.

Question: How can I get the UDFs to calculate when I have other workbooks
open at the same time?

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.

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.

That's the secret incantation!
It works perfectly.
Thanks so much.

"Bob Phillips" wrote:

Try this

Application.Caller.Parent.Range("TestRange")

I'm back. That worked for my addsome function. But when I tried to apply it
to my real function, it didn't work and it occurs to me that I don't
understand the soln anyway.

I thought I'd just stick Application.Caller.Parent in front of every range()
in my function. But that gives me an error 424 Object required.

application.caller seems to return a large integer (no idea what it is)
application.caller.parent (or dot any other property gets a 424).
It seems to work in the first worksheetfunction.(,range(),) though I don't
know why, but the next occurance in the UDF causes it to drop out of runtime.

??
Next idea?


"Bob Phillips" wrote:

ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _
Application.Caller.Parent.Range("TestRange"), 2, False)
addsome = InputValue + ToAdd

It's hard to say without seeing the code. Your problem was caused because it
was trying to refer to the range of the activesheet, so I forced it refer to
the range's parent sheet.

Post the whole code.

--
---
HTH

Bob


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



"makulski" wrote in message
...
I'm back. That worked for my addsome function. But when I tried to apply
it
to my real function, it didn't work and it occurs to me that I don't
understand the soln anyway.

I thought I'd just stick Application.Caller.Parent in front of every
range()
in my function. But that gives me an error 424 Object required.

application.caller seems to return a large integer (no idea what it is)
application.caller.parent (or dot any other property gets a 424).
It seems to work in the first worksheetfunction.(,range(),) though I don't
know why, but the next occurance in the UDF causes it to drop out of
runtime.

??
Next idea?


"Bob Phillips" wrote:

ToAdd = Application.WorksheetFunction.VLookup(AddWhat, _
Application.Caller.Parent.Range("TestRange"), 2, False)
addsome = InputValue + ToAdd