Custom Array Functions
 

Watch your code step by step...

Let's say a_number (the cell) = 5

- Dim aAd(1 To 12) As Integer
This should create an array with 12 elements, although I would choose for

Dim aAd As Variant
aAd = Array(12)

or

Dim aAd(12) As Variant

- Dim n As Integer
Loop counter, seems correct

Now here we go and fill the array...

For n = 1 To 12 Step 1
aAd(n) = n * a_number
Next n

- So far so good, the result is:
aAd(1) = 5
aAd(2) = 10
...
aAd(12) = 60


Finally you say
- DispArray = aAd

and there it goes wrong. You have an array with 12 elements, so now you
should specify the element to return
DispArray = aAd(a_number)

Because you can't return an Array with 12 values into one single cell,
DispArray = aAd will return 0.




"Syed Zeeshan Haider" schreef in
bericht ...
Hello Everybody,
I have Excel 2003 Pro on WinXP Pro.

I have been searching on internet but could not find any useful
information about creating custom array functions.
Can somebody tell me any links to the reference about creating custom
array functions?

Well, actually I saw a rather complex code on a site. This code returned
array but I couldn't spend next 2 to 3 hours in understanding what was it
supposed to calculate. No description was provided.
I tried to experiment with my own following code:

Public Function DispArray(a_number As Integer) As Variant
' yes, varaibles are not logical
Dim aAd(1 To 12) As Integer
Dim n As Integer
For n = 1 To 12 Step 1
aAd(n) = n * a_number
Next n
DispArray = aAd
End Function

It is supposed to display multiplication table's result of a given integer
(a_number). But it returns same result in all select cells.

Thank you,
--
Syed Zeeshan Haider


-----------------------------------------------------------------------------------------------------------------------------
Download a free game to play with Internet Explorer from
http://szh.20m.com/entertainment/olwg.html

Custom Array Functions
 

In general array formulas get entered into multiple cells at the same
time (using ctrl+shift+enter), so whereas you can't return a 12-member
array to a single cell, you *can* return it to 12 cells.

Tim.


"moi" wrote in message
...
...........

Finally you say
- DispArray = aAd

and there it goes wrong. You have an array with 12 elements, so now
you should specify the element to return
DispArray = aAd(a_number)

Because you can't return an Array with 12 values into one single
cell,
DispArray = aAd will return 0.

Custom Array Functions
 

"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
In general array formulas get entered into multiple cells at the same time
(using ctrl+shift+enter), so whereas you can't return a 12-member array to
a single cell, you *can* return it to 12 cells.

And that's what I was trying to do. I selected 12 cells for this reason.

Custom Array Functions
 

I wouldn't call myself an expert, but I did write (for myself) a handfu
of functions that return arrays (using Excel 2002). You've dimensione
the array to be one dimensional. In my experience, when
one-dimensional array is returned to Excel, Excel interprets it as
horizontal array. In this case, if you select a column of 12 cells (
vertical array) and array enter the array formula, Excel returns th
1st element of the horizontal array 12 times. If you select a row o
12 cells (a horizontal array), Excel will correctly return th
horizontal array.

I'm not sure the best workaround for this. The obvious solution to m
at the time I was working with those UDF's was to dimension each arra
as 2 dimensional arrays: Either DIM aAd(12,1) to force Excel (and me
the programmer) to recognize a vertical array, or DIM aAd(1,12) t
force me, the programmer to explicitly recognize that I'm working wit
a horizontal array. Some have also suggested using the TRANSPOS
worksheet function to transpose the horizontal array to a vertica
array.

Does that help

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=39083

Custom Array Functions
 

Yes, it helps!

I really appreciate the help.

I have to do some more experiments in Excel VB in the light of your
directions. If I stuck somewhere, hopefully I will bother forum-participants
again.

Thank you very much!

"MrShorty" wrote in
message ...

I wouldn't call myself an expert, but I did write (for myself) a handful
of functions that return arrays (using Excel 2002). You've dimensioned
the array to be one dimensional. In my experience, when a
one-dimensional array is returned to Excel, Excel interprets it as a
horizontal array. In this case, if you select a column of 12 cells (a
vertical array) and array enter the array formula, Excel returns the
1st element of the horizontal array 12 times. If you select a row of
12 cells (a horizontal array), Excel will correctly return the
horizontal array.

I'm not sure the best workaround for this. The obvious solution to me
at the time I was working with those UDF's was to dimension each array
as 2 dimensional arrays: Either DIM aAd(12,1) to force Excel (and me,
the programmer) to recognize a vertical array, or DIM aAd(1,12) to
force me, the programmer to explicitly recognize that I'm working with
a horizontal array. Some have also suggested using the TRANSPOSE
worksheet function to transpose the horizontal array to a vertical
array.

Does that help?


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile:
http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=390837