UDFs return #NAME error sometimes, other times, they work

[Guest]

So I have a spreadsheet with a bunch of cells that use a UDF. Sometimes when
I open the spreadsheet, some of the cells that have this UDF show a #NAME
error. Pressing F9 seems to clear these errors.

Is there some setting which forces UDFs to calculate automatically?

This is the code I'm using, if curious:

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
If Cell.HasArray Then GetFormula = "{" & GetFormula & "}"
End Function

If I eliminate the If...Then clause I still sometimes get the #NAME error,
sometimes not. So the code I have is not creating the error, so far as I can
tell...

Ideas?

 

[Dave Peterson]

Just a guess...

Next time you see the #name error, select that cell and see what the formula
says.

Does it point to the correct workbook?

Second guess...
Do you use other UDF's? If yes, do any of them have any errors that cause them
to break? Maybe excel stops calculating when the other UDF breaks.

 

[Guest]

Your first question is an interesting one.

I first noticed this error when opening the spreadsheet with Excel 2007, in
so-called compatibility mode. I'm thinking perhaps XL 2007 creates a virtual
workbook of some sort when opening a workbook created in a previous version
of Excel and that might cause the UDF to return this error. I haven't
noticed this when using the spreadsheet in other versions (2000 or 2003...)

There aren't any other UDFs in the workbook, just this one repeated a number
of times in various cells.

 

[Dave Peterson]

I haven't seen xl2007, so I can't help.

But if you haven't noticed the problem with previous versions, then maybe it's a
Beta problem?

If you're sure, can you post to the xl2007 community with your findings?

Your first question is an interesting one.

I first noticed this error when opening the spreadsheet with Excel 2007, in
so-called compatibility mode. I'm thinking perhaps XL 2007 creates a virtual
workbook of some sort when opening a workbook created in a previous version
of Excel and that might cause the UDF to return this error. I haven't
noticed this when using the spreadsheet in other versions (2000 or 2003...)

There aren't any other UDFs in the workbook, just this one repeated a number
of times in various cells.

 

[Guest]

Hi Dave,

This sounds like an issue that we'd definitely want to look into. I've got
a couple requests for you -

1. Can you try this on B2TR (now available for download) if you haven't
already?
2. If this still repros in B2TR, can you send me the solution (via email)
that repro's this so I can take a look with current builds and get a bug
filed if there's still a problem?

Cheers,
Dan
Excel Team

Note: We've only got a couple weeks to get bugs filed and fixed, so if I've
requested additional info via email, it'd be great if you can get that to us
ASAP. Please include any necessary sample files, as well as detailed repro
steps so that we can try to reproduce the problem on our side. Also - if
you're emailing me directly (definitely the most efficient at this point)
you'll want to fixup my email address to remove everything after danbatt and
before the @.