UDF returning #VALUE when another Excel file is opened

[gbuell]

Hi. I'm new to the world of Excel programming, so I hope I'm not being too simple here. I've Googled around for the answer to this and couldn't find a simple fix.

I've created some UDFs for a specific Excel file I'm working on. Here's the code:

Please Login or Register  to view this content.

When I open my file, everything works fine, and the cells using those UDFs update as they should. When I open another Excel file, though, the cells using the ExtraQuantity function return #VALUE, and I have to double-click on the cells and press ENTER to get them to calculate correctly again. I can save at this point and reopen and everything's fine, but it errors again as soon as I open another file. What should I add to my functions to keep this from happening? Thanks.

[TMS]

How are you calling the functions? Might be useful to upload a sample workbook with some typical data.

Regards, TMS

[JosephP]

I suggest you pass the extrapercentage value to the function as an argument rather than relying on a named range in the code

[gbuell]

JosephP, that seems to have fixed it! Any insight as to why using a named range was causing a problem? (I assume it's to do with the newly opened file not having that named range, but it seems odd that Excel would get hung up by that.)

[JosephP]

your code doesn't specify where the named range is so it defaults to looking in the active workbook. it's better to pass the value as an argument-then your function doesn't need to be volatile any more