Max function in basic: range vs. list

[rmcd]

I have written a basic function called "macromax" that calls "max". In cells a1:a4 I have 5,3,1,10. "macromax(a1:a4)"=10, while "macromax(a1;a2;a3;a4)"=5 (the first entry in the list). Here is my code:

Function calc_Func(sFunc$,args())
dim oFA as Object
oFA = createUNOService("com.sun.star.sheet.FunctionAccess")
calc_Func = oFA.callFunction(sFunc,args())
end function

Function macromax(a())
macromax=calc_Func("MAX",Array(a()))
end function

There seems to be a difference between inputting a range and a list. Anyone have thoughts?

Thanks in advance, Bob

[Villeroy]

Never use Basic functions unless the task is impossible to do without (e.g. stuff like 123 =>"One Hundred Twenty Three"). Always stay with builtin functions for compatibility, transparency, reliability and performance.
Rough guess: Your macromax takes one argument only so macromax(a1;a2;a3;a4) gets the max of a1 only, disregarding subsequent arguments. Since stupid StarBasic does not know about variable param arrays you've got to define something like macromax (arg1, optional arg2, optional arg3, ..., optional arg30). Maximum count of arguments for built-in MAX is 30.

[TerryE]

As Villeroy says any function in Calc can take up to 30 args. Each arg is either a scalar (an inline constant or a single cell reference) in which case it is passed as a scalar variable or a range in which case it is passed as a variant array -- in both cases by value. It looks to me like you are trying to abuse the Calc function declaration system. Basic has its own function mechanisms. There are very few times when it is worth doing what you seem to be doing, and one is when you genuinely want to extent the Calc functions with a new function to call from worksheets in formulae.

[rmcd]

Thank you both. I have existing VBA code that I am trying to port to OpenOffice Calc. This code does create new functions to call from Calc (I'm not just rewriting max!). In VBA I am used to writing "worksheetapplication.max". I'm still trying to figure out how to call Calc functions from OO Basic.

Assuming two inputs, my Macromax function should simply be replaced by

function macromax(a,b)
macromax=calc_Func("MAX",Array(a,b))
end function

And as Villeroy points out, I could add up to 28 additional optional inputs if necessary.

Thanks for your help!

[TerryE]

Why not just write your own Basic function Max which takes a variant array argument?

[rmcd]

Appropriate suggestion. I don't really need a variant array for my purposes, I was just trying to understand why what I did wasn't working.

Thank you again for the responses.

[TerryE]

Allowing variant arrays just means that you can use For loop from LBound to UBound, rather than a cascade of If statements processing the optional arguments.

[B Marcelly]

Hi,
com.sun.star.sheet.FunctionAccess is normally used within a (Basic) program to have access to functions not supported by the programming language.
For example MAX( ) does not exist in Basic. Here is an equivalent

Code: Select all

Sub Main
Dim result As Double
result = Max(Array(3, 55, 55.3, 117, -13, 22.22))
print result
End Sub

Function Max(args())
dim oFA as Object
oFA = createUNOService("com.sun.star.sheet.FunctionAccess")
Max = oFA.callFunction("MAX",args())
end function

Notice that you can use FunctionAccess independently of Calc. This code could be in a Draw document.
______
Bernard

[Villeroy]

Apart from application wide settings (e.g. TwoDigitYear) this service has some document specific properties, which may influence the result: NullDate, RegularExpressions, MatchWholeCell, IgnoreCase, CalcAsShown.
This returns a new FunctionAccess with all properties adjusted to the same properties of a passed document.

Code: Select all

Function getFunctionAccessByDocument(oDoc):
' Return a FunctionAccess service derived from a document's PropertySetInfo
Dim oFA, oPSInfo, props(), i%, s$, p, v
	oFA = createUnoService("com.sun.star.sheet.FunctionAccess")
	oPSInfo = oDoc.getPropertySetInfo()
	props() = oFA.PropertySetInfo.getProperties()
	for i = 0 to uBound(props())
		p = props(i)
		s = p.Name
		if oPSInfo.hasPropertyByName(s) then
			v = oDoc.getPropertyValue(s)
			oFA.setPropertyValue(s, v)
		endif
	next
	getFunctionAccessByDocument = oFA
End Function