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

Would you like to share the UDF with us?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<(E-Mail Removed)> wrote in message
news:b1c4c468-10ea-45ef-a90a-(E-Mail Removed)...
> 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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bernard Liengme" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| Would you like to share the UDF with us?
| best wishes
| --
| Bernard V Liengme
| Microsoft Excel MVP
| www.stfx.ca/people/bliengme
| remove caps from email
|
| <(E-Mail Removed)> wrote in message
| news:b1c4c468-10ea-45ef-a90a-(E-Mail Removed)...
| > 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
|
|

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/Return...ysFromVBA.aspx for more info.



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

<(E-Mail Removed)> wrote in message
news:b1c4c468-10ea-45ef-a90a-(E-Mail Removed)...
> 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

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.
--
Gary''s Student - gsnu2007a


"(E-Mail Removed)" wrote:

> 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
>

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:
>
> 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.
> --
> Gary''s Student - gsnu2007a
>
>
>
> "satishart...@gmail.com" wrote:
> > 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- Hide quoted text -
>
> - 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.

On Nov 29, 4:37 pm, satishart...@gmail.com wrote:
> 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:
>
> > 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.
> > --
> > Gary''s Student - gsnu2007a
>
> > "satishart...@gmail.com" wrote:
> > > 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- Hide quoted text -
>
> > - 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.- Hide quoted text -
>
> - Show quoted text -

Argh I meant Excel XP, not WinXP