User-defined function not recognized in formula

[Hershmab]

Excel 2003, XP SP3:
I have written VBA code to define a private Boolean function for my current
workbook and inserted it in a new module belonging to that workbook. VBE
clearly recognizes it and does not flag any errors.

But when I reference the function in a formula (spelled correctly with
upper-case letters where appropriate) the spelling gets altered and the cell
shows #VALUE! When I try to debug the code, there is no evidence that it has
been entered at all.

I have previously tried creating the function under a different name and
placed it in other modules in other workbooks (in my XLSTART folder), but I
had no more success. In some of the tests, the cell value showed as #NAME?
instead.

What am I doing or not doing?

 

[Chip Pearson]

Well, first of all, what is the name of your function? It is quite
likely that Excel's Autocorrect feature recognizes it as a word that
is on the list of words to correct. On the Tools menu, choose
AutoCorrect Options, and then see if your original function name is in
the list. If so, delete that entry from the list or rename the
function. Renaming is probably better if other users are going to use
your function -- otherwise, they'd have to fix their own AutoCorrect
list.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Mike H]

Hi,

Post your function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Niek Otten]

<Post your function>

And the formula with which you call it, together with the values and types
of the arguments.
You did enter the function in a standard module, didn't you? It should not
be in a Sheet module or Workbook module.
In general, if you get #Value and do not even hit a debug point in he code,
the number of arguments is wrong or they are of the wrong type.

 

[Niek Otten]

<"debug point">

should be "Breakpoint"

Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Dave Peterson]

And you created a regular/normal/general module and put the code in that module?

What's the name of the function?
What's the name of the module?

Can you use the function in a simpler formula?

What did that formula look like?

Did you enable macros to run when you opened the workbook? But that would cause
a Name error--not a Value error?

Maybe it's working fine and your input isn't correct????

======
If the function is in a different workbook, you would need to use something
like:
='otherworkbookname.xls'!myFunc(a1)

If the function is in a different workbook that's been saved as an addin, then
you don't need to qualify it.
=myfunc(a1)
would work ok.

=============
The uppercase/lowercase isn't the problem. Excel seems to use the case of
whatever you chose to type the first time you used it in the first formula.