[Solved] Cell range to array

Creating a macro - Writing a Script - Using the API

[Solved] Cell range to array

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
Last edited by Hagar Delest on Fri Aug 15, 2008 6:20 pm, edited 3 times in total.
Reason: Tagged as solved
OOo 2.4.X on Ms Windows XP
nw2063

Posts: 2
Joined: Thu Aug 14, 2008 3:50 pm

Re: cell range to array

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 26763
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: cell range to array

When you send a Function an array of cells [=MyFunction(A1:A12)]it is an array in the Function.
Code: Select all   Expand viewCollapse view
`Function MyFunction(aray) MyFunction = aray(4,1)+100End Function`
JohnV
Volunteer

Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: cell range to array

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.
OOo 2.4.X on Ms Windows XP
nw2063

Posts: 2
Joined: Thu Aug 14, 2008 3:50 pm