[Solved] Call Calc function from Basic

[pguio]

Hello,
I am trying to define my own macro in OO Calc using existing Calc function.

For instance I have defined a cell value to be
=ROUND(AVERAGE(IF(C3:C29>=LARGE(C3:C29,B33),C3:C29))-AVERAGE(IF(C3:C29<=SMALL(C3:C29,B33),C3:C29)/C2),2)

I would to define my own function to avoid errors when copying it and be able to call

=MyFunction(C2,C3:C29,B33)

I cannot figure how to do that.
I tried to start playing around and define a macro in My Macros & Diaplogs.Standard - LibreOffice Basic

Function MyLarge(A,B)
MyLarge = LARGE(A,B)
End Function

But it does not seem to work.
Is there a way to call from OO Basic the function LARGE defined in Calc?

Many thanks,
Patrick

[Villeroy]

Either you learn programming and solve this program with a mature programming language, or you use a spreadsheet. Writing a program to fill out a simple spreadsheet is a massive waste of time and energy.

btw: your formula returns #VALUE!

[Zizi64]

Code: Select all

Function MyLarge(A,B)
MyLarge = LARGE(A,B)
End Function

This is not such simple thing.
You must use the FUNCTIONACCESS in the macro code to use/call the Cell Functions. And you must pass all of the parameter to the called Cell Function as an ARRAY.

Code: Select all

Function myLarge(aData as Variant, optional lPassedIndex as long) as Double

 dim oFunctionAccess as object
 dim aParameters(1) as Variant
 dim lIndex as long

 	If IsMissing(lPassedIndex) then 
 		lIndex = 1
 	else
 		lIndex = lPassedIndex
 	end if
 	
 	oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	
	aParameters(0) = aData
	aParameters(1) = lIndex
		
	myLarge = oFunctionAccess.CallFunction( "LARGE", aParameters)
End Function

FunctionAccess.ods

(10.72 KiB) Downloaded 94 times

[Lupp]

(I didn't try myself.)
@Zizi64
The FunctionAccess service can only call one Calc function at a time. The intermediary results must be stored to variables or "nested" into the array of parameters used for the call by nested calls to FunctionAccess.
That's by far too complicated. In addition the questioner's example (as I see it) will need explicitly forced array evaluation in two places. Well, there is an optional property of FunctionAccess named "IsArrayFunction", but if it actually works as expected I never tested myself (and I tested a lot). The option is NOT YET included with my snippet "Lightweight Global Module Simplifying FunctionAccess". (I have an insofar untested version including it. You can get it if you want to try.)

If the question was about using a short mnemonic name for the expression, but otherwise to adapt its references as usual, the appropriate means is obviously the tool "Named Ranges or Expressions".

[pguio]

Thank you for your answer, that's useful! I was naive thinking I could call Calc functions from OO Basic.
I realised the other day when trying to convert another expression to convert percentage into mark

={IF(C3:C29>=92,"A",IF(C3:C29>=77,"B",IF(C3:C29>=58,"C",IF(C3:C29>=46,"D",IF(C3:C29>=40,"E","F")))))}

and I finally managed to write a Basic function to do the same

Function Mark(Marks)
I = 1
FOR EACH M IN M rks
IF M >= 92 THEN
Mark(I) = "A"
ELSEIF M >= 77 THEN
Mark(I) = "B"
ELSEIF M >= 58 THEN
Mark(I) = "C"
ELSEIF M >= 46 THEN
Mark(I) = "D"
ELSEIF M >= 40 THEN
Mark(I) = "E"
ELSE
Mark(I) = "F"
END IF
i = i+1
NEXT M
End Function

The other expression I was trying to get a OO Basic equivalent is just a measure of how good a question is to discriminate students. C2 is full mark for the question, C3:C29 are student marks, and B33 is a number smaller than half of the students number to calculate the lowest of the top B33 marks and the largest of the bottom B33 marks.
I just wanted to improve the readibility of the spreadsheet by defining the function once and calling it as needed for any exam spreadsheet.
I realise it might be difficult in OO Basic and I am not sure to understand how the Named Ranges or Expressions would help but would you have a pointer?
Otherwise call you import functions from any python package using the OO python interface? If so I guess it might be easier to use python?

Many thanks again,
Patrick

[Lupp]

You can call Calc functions from Basic using the FunctionAccess service.
You can not evaluate arbitrary Calc formulas as written to Calc cells, ...
and there is no automatism capable of creating a UserDefinedFunction based on such a formula.
(We need to distinguish formulas from functions.)

Concerning the assignment of "marks" to the ratios of the number of points achieved to the number of points awarded:
Dont write user code, but use standard formulas of Calc based on a lookup table.
Such assignments are everyday business for spreadsheets. See attached example:

marksForRatios.ods

(29.51 KiB) Downloaded 99 times