Formula returns "#VALUE"

[Guest]

I have a function in VBA that we call all over the place in our code. It
looks like...

Function GetFld(Fieldname As String, secID As String)

I am trying to call it from a cell in Excel. So I typed...

=getfld("pricecode",D5)

That returns "#value". I assume I'd making some noob mistake, what is it?

Maury

 

[JE McGimpsey]

It's impossible to tell from the information you've provided.

The call from the worksheet isn't likely the problem (depending on
what's in D5, obviously).

So what's in D5?

And if you set a breakpoint in the first line of your function and
manually initiate a calculation, where does the error occur in your
function code?

 

[Guest]

There does not appear to be a return type on the function.

I don't see the code for the function so I don't know what it is returning.
Probably should say something like :
Function GetFld(Fieldname As String, secID As String) as integer
or
Function GetFld(Fieldname As String, secID As String) as string

are you seting GetFld to a value in the code?

If this doesn't help, post the code to the function.

 

[Dave Peterson]

Using this:
Function GetFld(Fieldname As String, secID As String)
is like specifying this:
Function GetFld(Fieldname As String, secID As String) As Variant

But it won't help the OP even if he did change this.

 

[Guest]

And if you set a breakpoint in the first line of your function and
manually initiate a calculation, where does the error occur in your
function code?

It doesn't! That's what's confusing. The function is not getting called at
all.

Maury

 

[Dana DeLouis]

One debugging technique is to use the Insert Function button on a worksheet
(The Fx button)
Select "User Defined" from the Category drop down.
Maybe the code resides on another workbook? I'm just guessing.

 

[Guest]

Ahhh! More information...

There is a similar function that takes a different identifier, a number
instead of a string. It's otherwise identical. I typed THAT formula into the
cell and it worked perfectly (after looking up the number by hand). The
breakpoint inside that function also worked.

So for some reason the string input is wrong, but the error occurs BEFORE
the code gets called. IE, it's as if Excel is blocking the call because of
the format of the input.

Does this ring any bells? Is there something I have to do to force Excel to
interpret the data as "String". The cell reads "33761N109", and I tried
formatting it as Text, but that didn't change anything

Maury

 

[Guest]

Even more info, this call also fails...

=getfld("pricecode","64045Y108")

Note that in this case I am "hand inputting" two strings, which is what the
function wants. As in the earlier examples, the fails _before_ calling the
code in the module.

Maury

 

[Guest]

Select "User Defined" from the Category drop down.

Ah ha! So it turns out there are two GetFlds with different sigs. I don't
know how this is possible, and a search of all the workbooks involved doesn't
turn it up either. So that remains a mystery.

But by making a copy and renaming getfld3, everything immediately worked.
Thanks!

Not so noob after all I guess...

Maury