#VALUE from user defined function

[Bob_Irving]

Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Excel 2004 /Mac OS 10.6.2 (fully updated)

I have a worksheet which contains a function what I have defined myself. When I open the sheet, it works OK
When I open a second worksheet, all the occurrences of this function go to #VALUE, until I click in the formula and press Return. They then calculate correct values.
I could deal with this if there weren't quite a lot of them.
What have I done to cause this? Is it to do with the "duplicated names" problem being mulled over in another thread, as it is quite possible that I have copied sheets between worksheets.

TIA
        Bob Irving

 

[Bob Greenblatt]

Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Excel 2004 /Mac OS 10.6.2 (fully updated)

I have a worksheet which contains a function what I have defined myself. When
I open the sheet, it works OK
When I open a second worksheet, all the occurrences of this function go to
#VALUE, until I click in the formula and press Return. They then calculate
correct values.
I could deal with this if there weren't quite a lot of them.
What have I done to cause this? Is it to do with the "duplicated names"
problem being mulled over in another thread, as it is quite possible that I
have copied sheets between worksheets.

TIA
Bob Irving

I have no idea what may be the cause from your description. The odds are
great that the function is not properly coded and the error is due to the
other sheet being active after it opens. If you post the code of the
function, I may be able to help.

 

[Bob_Irving]

Yes. found it - no Dims for variables used in the function. Also I'd given them the same name as a range... #VALUEs gone now...