[Solved] Cell range to array

Creating a macro - Writing a Script - Using the API

[Solved] Cell range to array

Postby nw2063 » Thu Aug 14, 2008 4:06 pm

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


My thought for the function so far

FUNCTION myFunction(array())

--my calcutaltion

myFunction = value


So I like to use the array like


The length of the array need to be dynamic.

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

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
Posts: 2
Joined: Thu Aug 14, 2008 3:50 pm

Re: cell range to array

Postby Villeroy » Thu Aug 14, 2008 5:13 pm

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
User avatar
Posts: 26753
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: cell range to array

Postby JohnV » Thu Aug 14, 2008 5:30 pm

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)+100
End Function
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: cell range to array

Postby nw2063 » Fri Aug 15, 2008 12:12 pm

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 :oops:

Thank you all for the fast reply.
OOo 2.4.X on Ms Windows XP
Posts: 2
Joined: Thu Aug 14, 2008 3:50 pm

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests