custom excel function returns array, showing #VALUE in cells

S

satishartham

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
 
N

Niek Otten

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Chip Pearson

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.



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

Gary''s Student

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

satishartham

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







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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top