Max function in basic: range vs. list
Max function in basic: range vs. list
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
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
Re: Max function in basic: range vs. list
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Max function in basic: range vs. list
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.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Re: Max function in basic: range vs. list
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!
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!
Re: Max function in basic: range vs. list
Why not just write your own Basic function Max which takes a variant array argument?
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Re: Max function in basic: range vs. list
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.
Thank you again for the responses.
Re: Max function in basic: range vs. list
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.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: Max function in basic: range vs. list
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
Notice that you can use FunctionAccess independently of Calc. This code could be in a Draw document.
______
Bernard
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
______
Bernard
Re: Max function in basic: range vs. list
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.
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice