Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Using custom functions within custom validation | Excel Discussion (Misc queries) | |||
Custom Array Functions | Excel Programming | |||
Custom Functions in C/C++ against in VBA | Excel Programming |