[Solved] How use MAX function in basic?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
lolzlol77
Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

[Solved] How use MAX function in basic?

Post by lolzlol77 »

Code: Select all

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) = numMax
End 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How use MAX function in basic?

Post by Villeroy »

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.
viewtopic.php?f=20&t=84841&hilit=+funct ... 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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
lolzlol77
Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

Re: How use MAX function in basic?

Post by lolzlol77 »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How use MAX function in basic?

Post by FJCC »

Here is an example of getting the max of the range Sheet1.A1:A11

Code: Select all

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.Data
TheMax = 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How use MAX function in basic?

Post by FJCC »

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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How use MAX function in basic?

Post by Lupp »

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.
See also attached example.
Attachments
aoo85000BetterSolution_1.ods
(26.21 KiB) Downloaded 217 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
lolzlol77
Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

Re: How use MAX function in basic?

Post by lolzlol77 »

FJCC wrote:Here is an example of getting the max of the range Sheet1.A1:A11

Code: Select all

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.Data
TheMax = 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?

Post by lolzlol77 »

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.
See also attached example.
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
User avatar
RichieRH
Posts: 36
Joined: Sun Oct 19, 2014 6:16 pm

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

Post by RichieRH »

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
Post Reply