## [Solved] How use MAX function in basic?

Creating a macro - Writing a Script - Using the API

### [Solved] How use MAX function in basic?

Code: Select all   Expand viewCollapse view
`Function GetMax(rowStart As Long, rowEnd As Long, col As Long) As Long   dim numMax as Integer 'The largest number   dim numArr as Variant      numArr = Range(Cells(col,rowStart),Cells(col,rowEnd))      numMax = oFunctionAccess.CallFunction("MAX", numArr())   GetMax(rowStart, rowEnd, col) = numMaxEnd Function`

This code snippet works until the line where i call on MAX and it says numMax is 0. Why does this not work?
Last edited by Hagar Delest on Thu Sep 08, 2016 10:08 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.2 on OSX El Capitan
lolzlol77

Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

### Re: How use MAX function in basic?

Is this some new style troll attack or why does the same expert question come up several times per week? And why do you post such an expert question in the "Beginners" forum?
First answer is that you are not limited to StarBasic as macro language. You may also use a mature programming language with built-in arithmetics.
Second answer is that all spreadsheet functions take numbers or strings. You pass objects.
The VBA style how you access cell ranges is highly problematic because you do arithmetics based on the currently selected sheet.
https://forum.openoffice.org/en/forum/v ... ss#p394788
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26626
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: How use MAX function in basic?

So are you saying that i passed an object in the line:
numMax = oFunctionAccess.CallFunction("MAX", numArr())
?

I dont understand what "svc = createUnoService("com.sun.star.sheet.FunctionAccess")" means

I just started using openoffice basic a couple weeks ago so thats why i posted in the beginners section
OpenOffice 4.1.2 on OSX El Capitan
lolzlol77

Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

### Re: How use MAX function in basic?

Here is an example of getting the max of the range Sheet1.A1:A11
Code: Select all   Expand viewCollapse view
`svc = createUnoService("com.sun.star.sheet.FunctionAccess")oSheet = ThisComponent.Sheets.getByName("Sheet1")oRng = oSheet.getCellrangeByPosition(0,0,0,10) '=A1:A11 (left, top,right,bottom)Data = oRng.DataTheMax = svc.callFunction("MAX",array(Data))print TheMax`

The createUnoService() function returns an object that implements the named service. The documentation for com.sun.star.sheet.FunctionAccess is here and the documentation for its interface XFunctionAccess, which provides callFunction(), is here.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7127
Joined: Sat Nov 08, 2008 8:08 pm

### Re: How use MAX function in basic?

You can see that the code I wrote has little overlap with your VBA style code. OpenOffice has some ability to translate VBA-like code but I have never played with that, knowing nothing about VBA. I expect it is about as good as on line language translators, i.e. barely useful. If you want to write a lot of OpenOffice macros, you might want to take a look at the the material at http://www.pitonyak.org/oo.php
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7127
Joined: Sat Nov 08, 2008 8:08 pm

### Re: How use MAX function in basic?

MS may encourage to rely on user code. They have commercial reasons, of course. Apache and the folks working with AOO and LibO and contributing here mostly do not. Surely I don't.
There are grave general reasons - and in most cases aditional specific ones to avoid "macros" where possible.
The task under discussion here is surely better solved, IMO, by ordinary formulae based on ordinary standard functions of Calc.
Let me know the reasons if this is one of those exceptional cases where writing user code is preferrable.
Attachments
aoo85000BetterSolution_1.ods
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2402
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: How use MAX function in basic?

FJCC wrote:Here is an example of getting the max of the range Sheet1.A1:A11
Code: Select all   Expand viewCollapse view
`svc = createUnoService("com.sun.star.sheet.FunctionAccess")oSheet = ThisComponent.Sheets.getByName("Sheet1")oRng = oSheet.getCellrangeByPosition(0,0,0,10) '=A1:A11 (left, top,right,bottom)Data = oRng.DataTheMax = svc.callFunction("MAX",array(Data))print TheMax`

The createUnoService() function returns an object that implements the named service. The documentation for com.sun.star.sheet.FunctionAccess is here and the documentation for its interface XFunctionAccess, which provides callFunction(), is here.

Thank you i think i understand how svc works now!
OpenOffice 4.1.2 on OSX El Capitan
lolzlol77

Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

### Re: How use MAX function in basic?

Lupp wrote:MS may encourage to rely on user code. They have commercial reasons, of course. Apache and the folks working with AOO and LibO and contributing here mostly do not. Surely I don't.
There are grave general reasons - and in most cases aditional specific ones to avoid "macros" where possible.
The task under discussion here is surely better solved, IMO, by ordinary formulae based on ordinary standard functions of Calc.
Let me know the reasons if this is one of those exceptional cases where writing user code is preferrable.

Thank you so much for this solution and example! The reason i need to write user code is because its for a school assingment where we need to write our own max function. But i just wanted to see how we would call on the inbuilt max function. In my actual code i will probably use a loop to check the max of the values.
OpenOffice 4.1.2 on OSX El Capitan
lolzlol77

Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

### Re: [Solved] How use MAX function in basic?

This is the one that I have beeh looking for.. thanks
Debian 8 Jessie
Linux Mint 17.3 XFCE
Country : Indonesia
Learning is a need

RichieRH

Posts: 36
Joined: Sun Oct 19, 2014 6:16 pm