[Solved] Cell range to array

[nw2063]

Hi, I'm new to makro programming with oo, but I have some experience in programming.

I want to make some special statistical calculations and thought to use a makro for that.
I thought the easiest way would use a function which will return the desired value, so I need to get the values from the table like 'A1:A12' and put them in an array that the function can use. I have so far searched the help and the net but found no concrete answer to that. I understand the VOL(a;b;c) example, but this uses only single cells and I want/need to use a range.

I want to type in the spreadsheet

=myFunction(A1:A12)

My thought for the function so far

FUNCTION myFunction(array())

--my calcutaltion

myFunction = value

END FUNCTION

So I like to use the array like

array(0)=A1
.
.
.
array(11)=A12

The length of the array need to be dynamic.

I hope some one can give me a clue how to make this work.

Thanks

[Villeroy]

Store the function in special library "Standard" of either one of your document's container or container "MyMacros" (stored in your profile).
Set a stop mark in your function.
Put =Myfunction(A1:B2) in a spreadsheet (*the* spreadsheet if the code is embedded).
Watch the variable passed to your function. It's a 2-dimensional array as documented in the Basic help.
Function MyFunction(array()) can not work since array itself is the name of a function in Basic. Without arguments it produces a blank array.

[JohnV]

When you send a Function an array of cells [=MyFunction(A1:A12)]it is an array in the Function.

Code: Select all

Function MyFunction(aray)
 MyFunction = aray(4,1)+100
End Function

[nw2063]

sometimes it is so easy, I must have missed the fact that the array is 2 dimensional.
Like the saying goes 99% of the problems are sitting in front of the screen

Thank you all for the fast reply.