[CALC] Writing a user defined function that uses a line number as an argument

[Damien]

I am trying to help automate a tedious task and I think a user defined function would come in really handy. What I want to do is to have a function that takes a line number (or 1D array of that line) and a number and does some simple arithmetic on it. This is what I have tried so far, but it fails to compile:

REM  *****  BASIC  *****

Function CON(a, b)

CON = a[2] * b * a[8] * (1 + (0.5*a[3])) * (1 - (0.15*a[4]))

End Function

Sub Main



End Sub

‘a’ is the array, and I would have expected a[2] to be cell Cn where n is the row number. ‘b’ is just a float.

[anon73440385]

For me it is unclear how your array is being created or how you want it being created. In other words: Why don’t you just:
Function CON(a2,a3,a4,a8,b) and use
CON = a2 * b * a8 * (1 + (0.5*a3)) * (1 - (0.15*a4)) and where a2=ROW(), when using the function in cell Cn i.e. calling by: =CON(ROW();a3;a4;a8;b)

[Damien]

I need to use this function hundreds of times, so I was just trying to minimise the amount of inputs since I know the columns of the inputs and they will all be in the same row, so ideally, I could just input the row number, the other number and it would return the calculated value. However, at this point, it may just be more efficient to bite the bullet and do it that way.

[anon73440385]

Now I understand - but then you don’t need an array or pass an array, but a macro evaluating some values from the row given as a parameter (which from my perspective is a dangerous thing since you’d make a user defined function dependent to the design of your sheet).

[Damien]

Ah great, how can I get a those values then, I have tried using oSheet.getCellFromPosition(a,2)but that returns an error ‘object variable not set’.

[Lupp]

You should not try to do every, even the first step based on guessing. Your example contains many errors or flaws which seem to come from different sources.
The next piece of code you try will not be much better… except you learn a few facts in advance.

One of the most quoted sources may still be https://www.pitonyak.org/OOME_3_0.pdf . Concerning the principles you needn’t worry about the versions of OpenOffice mentioned. Next to everything is still valid for all the versions of OpenOffice and LibreOffice. There are exceptions, of course.

“The Book” also has a lot of content only relevanjt for advanced users, in specic those who need to use the AOO/LibO API for document automation and te like. You may ignore it for some time.

[Lupp]

Native LibreOffice Basic hasn’t a way to get the reference from an array (CellRange) passed for a parameter of a user function. The function only gets an array of data which is always 2D formally, but may have one of the index ranges degenerated to the single index 1.
In addition: If you think to pass a single cell to the function, you get in fact not a “singleton” array, but a scalar of type String or Double.
If you actually need access to the Range itself (not just the data), native means are a bit complicated. Much simpler (but also coming with disadvantages) is in this case to create a Basic module with Option VBAsupport 1 .

If you only need the data, you can access them:
From one row e.g. : r(1, 1) ; r(1, 2); … r(1, 10) if there were 10 cell in the row addressed.
From one column: c(1, 1); c(2, 1); c(3, 1); … c(7, 1)
The bottom right data element if a range like C5:E20 was addressed as myData(16, 3)

[Damien]

I have solved the problem, I needed to use a(, ) to get the required data. Below is my code:

REM  *****  BASIC  *****

Function CON(a as Variant, b)

Dim machines
Dim speed
Dim prod
Dim base_speed

machines = a(1,3)
speed = a(1,4)
prod = a(1,5)
base_speed = a(1,9)

CON = machines * b * base_speed * (1 + (0.5*speed)) * (1 - (0.15*prod))

End Function