[Solved] How use MAX function in basic?

Creating a macro - Writing a Script - Using the API

[Solved] How use MAX function in basic?

Postby lolzlol77 » Wed Sep 07, 2016 2:02 am

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) = 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
lolzlol77
 
Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

Re: How use MAX function in basic?

Postby Villeroy » Wed Sep 07, 2016 2:26 am

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

Re: How use MAX function in basic?

Postby lolzlol77 » Wed Sep 07, 2016 2:31 am

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?

Postby FJCC » Wed Sep 07, 2016 5:26 am

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.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.
Windows 10 and Linux Mint, since 2017
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: 7056
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How use MAX function in basic?

Postby FJCC » Wed Sep 07, 2016 5:50 am

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

Re: How use MAX function in basic?

Postby Lupp » Wed Sep 07, 2016 2:22 pm

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 89 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How use MAX function in basic?

Postby lolzlol77 » Thu Sep 08, 2016 2:22 am

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.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?

Postby lolzlol77 » Thu Sep 08, 2016 2:26 am

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
lolzlol77
 
Posts: 4
Joined: Wed Sep 07, 2016 1:59 am

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

Postby RichieRH » Wed Jul 04, 2018 8:58 am

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


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests