custom excel function returns array, showing #VALUE in cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

satishartham@gmail.com - 29 Nov 2007 19:38 GMT

I'm using a function that returns an array of data. I put in the
function name and hit ctrl-shift_enter and every cell in my selection
gets a #VALUE.

So I thought the function might be returning bad data. I put a
breakpoint in the debugger and I can see that the array being returned
has good data. The returned type is variant/string(0 to 499, 0 to
15). I've done some spot checking and the values look fine.

How can it be that the array looks fine in the debugger but i'm
getting #VALUE back in my cells?

thanks,

Satish

Reply to this Message

Bernard Liengme - 29 Nov 2007 20:14 GMT

Would you like to share the UDF with us?
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> I'm using a function that returns an array of data. I put in the
> function name and hit ctrl-shift_enter and every cell in my selection
[quoted text clipped - 11 lines]
>
> Satish

Reply to this Message

Niek Otten - 29 Nov 2007 20:41 GMT

And the formula in which you call it from the worksheet, including the range selected

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Would you like to share the UDF with us?
| best wishes
[quoted text clipped - 13 lines]
| >
| > Satish

Reply to this Message

Chip Pearson - 29 Nov 2007 20:49 GMT

You might want to post some code. You'll get a #VALUE result if your
function attempts to change any part of the Excel environment, including the
values of other cells. A function can only return a value or an array of
values to the cells from which it was called.

You might try changing the return type of the function to a Variant. E.g.,

Function MyFunction(....) As Variant

instead of an array.

See http://www.cpearson.com/Excel/ReturningArraysFromVBA.aspx for more info.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> I'm using a function that returns an array of data. I put in the
> function name and hit ctrl-shift_enter and every cell in my selection
[quoted text clipped - 11 lines]
>
> Satish

Reply to this Message

Gary''s Student - 29 Nov 2007 20:51 GMT

Make sure you are using it as an array on the Worksheet and it has the
correct orientation:

Function demo(r As Range) As Variant
v = Array(1, 2, 3)
demo = v
End Function

Now on the worksheet, highlight A1 thru C1 and enter
=demo(Z100) and finish with CNTRL-SHFT-ENTER rather than just ENTER. You
will see:

1 2 3

If you had selected A1 thru A3 you will see:

1
1
1

because you need a transpose.

Signature

Gary''s Student - gsnu2007a

> I'm using a function that returns an array of data. I put in the
> function name and hit ctrl-shift_enter and every cell in my selection
[quoted text clipped - 11 lines]
>
> Satish

Reply to this Message

satishartham@gmail.com - 29 Nov 2007 21:37 GMT

On Nov 29, 3:51 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Make sure you are using it as an array on the Worksheet and it has the
> correct orientation:
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Thanks for everyone's help but looks like I've found my answer:
http://support.microsoft.com/kb/250828. I tried the same function
call in WinXP and it's fine. My function was returning more data than
excel 2000 could handle.

Reply to this Message

satishartham@gmail.com - 29 Nov 2007 21:38 GMT

On Nov 29, 4:37 pm, satishart...@gmail.com wrote:

> On Nov 29, 3:51 pm, Gary''s Student
>
[quoted text clipped - 47 lines]
>
> - Show quoted text -

Argh I meant Excel XP, not WinXP

Reply to this Message