custom excel function returns array, showing #VALUE in cells
 

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

custom excel function returns array, showing #VALUE in cells
 

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

wrote in message
...
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

custom excel function returns array, showing #VALUE in cells
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bernard Liengme" wrote in message ...
| 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
|
| wrote in message
| ...
| 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
|
|

custom excel function returns array, showing #VALUE in cells
 

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)

wrote in message
...
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

custom excel function returns array, showing #VALUE in cells
 

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


" 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

custom excel function returns array, showing #VALUE in cells
 

On Nov 29, 3:51 pm, Gary''s Student
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



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

custom excel function returns array, showing #VALUE in cells
 

On Nov 29, 4:37 pm, wrote:
On Nov 29, 3:51 pm, Gary''s Student





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

" 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