[Solved] Can't use MIN or MAX in my macro function

Keyboard macros or custom scripts

[Solved] Can't use MIN or MAX in my macro function

Postby sedwick1024 » Sat May 08, 2010 7:30 am

I have a OO BASIC function in which I'm trying to use MIN the way you would in a spreadsheet cell (swapping ; for , of course), and every time I enter a call to the function in a cell, it gives me a runtime error, saying "Sub-procedure or function procedure not defined", pointing me to the line with the MIN call. Do I have to add a line to import a library or something? Thanks and sorry if this has been answered elsewhere...
Last edited by Hagar Delest on Wed May 12, 2010 10:02 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.1.1 on Windows XP Pro
sedwick1024
 
Posts: 2
Joined: Sat May 08, 2010 7:12 am

Re: Can't use MIN or MAX in my macro function

Postby ms777 » Sat May 08, 2010 2:39 pm

Calc functions usable in calc formulas have nothing to do with OO Basic functions. AFAIK, there is no Min/max in OO Basic. You have to define it yourself
ms777
Volunteer
 
Posts: 131
Joined: Mon Oct 08, 2007 1:33 am

Re: Can't use MIN or MAX in my macro function

Postby FJCC » Sat May 08, 2010 3:11 pm

I think the MIN function can be used in a macro. Here are two examples.
Code: Select all   Expand viewCollapse view
Sub Exmpl1
Dim Data(4)
Data(0) = 15
Data(1) = 53
Data(2) = 3
Data(3) = -43
Data(4) = 5
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
MinValue = svc.callFunction("Min",Data())
Print MinValue
End Sub

Sub Exmpl2
Sheet = ThisComponent.Sheets.getByIndex(0)
oCellRange = Sheet.getCellRangeByName("A1:A10")
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
arg = Array(oCellRange)
MinValue = svc.callFunction("Min",arg)
Print MinValue
End Sub
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 6820
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Can't use MIN or MAX in my macro function

Postby FJCC » Sat May 08, 2010 5:41 pm

Expanding on my last post a bit, you can access any spreadsheet function with the com.sun.star.sheet.FunctionAccess service. You just have to pass it the function name and the arguments of the function in an array. You can use the Vlookup function like this.
Code: Select all   Expand viewCollapse view
Sub Exmpl3
Sheet = ThisComponent.Sheets.getByIndex(0)
oCellRange = Sheet.getCellRangeByName("A1:B10")
SearchValue = Sheet.getCellRangeByName("C1").getString()
Column = 2
Mode = 0
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
arg = Array(SearchValue, oCellRange, Column, Mode)

Value = svc.callFunction("VLOOKUP",arg)
Print Value
End Sub

You can even use an array function, such as the FREQUENCY function. Naturally, you get an array back in that case.
Code: Select all   Expand viewCollapse view
FreqArray = array(oCellRange, oCellRange2)
oCellRange3.setData(svc.callFunction( "FREQUENCY", FreqArray ))
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 6820
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Can't use MIN or MAX in my macro function

Postby sedwick1024 » Sun May 09, 2010 4:05 am

That seems to work, thanks very much! Now I just need to figure out how to not get an error message for EVERY instance of a syntax error in my function's evaluation...
OpenOffice 3.1.1 on Windows XP Pro
sedwick1024
 
Posts: 2
Joined: Sat May 08, 2010 7:12 am

Re: Can't use MIN or MAX in my macro function

Postby RichieRH » Tue Mar 08, 2016 3:34 am

FJCC wrote:I think the MIN function can be used in a macro. Here are two examples.
Code: Select all   Expand viewCollapse view
Sub Exmpl2
Sheet = ThisComponent.Sheets.getByIndex(0)
oCellRange = Sheet.getCellRangeByName("A1:A10")
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
arg = Array(oCellRange)
MinValue = svc.callFunction("Min",arg)
Print MinValue
End Sub



I can't use this code in my data calc. I have some data in sheet (0) in the range cell (A1:A1000) but when I run this macro, the results of 'print minvalue' didn't show anything..Do you have an example that works??I mean in ods file...
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

Re: [Solved] Can't use MIN or MAX in my macro function

Postby FJCC » Tue Mar 08, 2016 4:13 am

Here is the original file.
Attachments
MinFunc.ods
(13.76 KiB) Downloaded 160 times
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 6820
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Can't use MIN or MAX in my macro function

Postby RichieRH » Tue Mar 08, 2016 5:05 am

FJCC wrote:Here is the original file.


I have tried your code and it s working like a charm..but why is not in my file??
Attachments
Aplikasi Warung.ods
Not working here...whats the problem?
(41.23 KiB) Downloaded 134 times
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

Re: [Solved] Can't use MIN or MAX in my macro function

Postby FJCC » Tue Mar 08, 2016 6:44 am

You really had me confused for a while.The problem is that the name of the procedure is the same as the name of the variable you are trying to print. Both are called MaxValue. Try
Code: Select all   Expand viewCollapse view
Sub MaxValue
Sheet = ThisComponent.Sheets.getByIndex(0)
oCellRange = Sheet.getCellRangeByName("A16:A40")
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
arg = Array(oCellRange)
MaximumValue = svc.callFunction("Max",arg)
Print MaximumValue
End Sub
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 6820
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Can't use MIN or MAX in my macro function

Postby RichieRH » Tue Mar 08, 2016 7:40 am

Yes, it's now working.Thanks a lot...FJCC
Indeed..It's an easy simple things to solved but sometimes we often think too far and forget the basic case.



Sorry, English lang is not my native.
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

Re: [Solved] Can't use MIN or MAX in my macro function

Postby malazkd » Thu Oct 20, 2016 4:54 pm

Hi! I have a problem in macro scripting. Is WORKDAY function available? I don't know exactly how to write it on vb.

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub Main

Dim svc As Object
Dim result
Dim oDate As Date
Dim oValueAdd
Dim oHolidayRange

Doc             = ThisComponent 'refers to the whole file as object
 
Request             = Doc.Sheets(0)
HolidayList             = Doc.Sheets(1)
   oDataRange = Request.getCellRangeByName("E1:E1000")
   oHolidayRange = HolidayList.getCellRangeByName("A1:A1000")
   oDate =  Request.getCellRangeByName("B3").Value()
   oValueAdd =  Request.getCellRangeByName("B2")
   'test = array(oDate,oValueAdd,oHolidayRange)
   
   svc = createUnoService("com.sun.star.sheet.FunctionAccess")
   result = svc.callFunction("WORKDAY",Array(oDate,oValueAdd,oHolidayRange))
   'result = workday(test)
   MsgBox(result)

End Sub
Last edited by RoryOF on Thu Oct 20, 2016 5:21 pm, edited 1 time in total.
Reason: Added [code] tags. [RoryOF, Moderator]
OpenOffice 4.1.2
malazkd
 
Posts: 3
Joined: Thu Oct 20, 2016 4:46 pm

Re: [Solved] Can't use MIN or MAX in my macro function

Postby Villeroy » Thu Oct 20, 2016 6:10 pm

What is the meaning of all this? Why can't you use a simple spreadsheet? The purpose of spreadsheet software is that non-programmers do not have to write any programs to calculate weekdays, minimums, maximums etc.
Obviously you are not a programmer.
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: 25853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Can't use MIN or MAX in my macro function

Postby malazkd » Thu Oct 20, 2016 6:18 pm

Hello Sir Villeroy, sorry but I am a beginner dev and I'm new in macro scripting.

I am developing a template that generates multiple requests to be uploaded in a system.

I need WORKDAY to compute due date for each request. Would you know how to implement WORKDAY in macro?.... Thanks.
OpenOffice 4.1.2
malazkd
 
Posts: 3
Joined: Thu Oct 20, 2016 4:46 pm

Re: [Solved] Can't use MIN or MAX in my macro function

Postby RoryOF » Thu Oct 20, 2016 6:31 pm

Have you used the Forum search (top right of forum window) to search for Workday? There are a number of threads on the subject.
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 27497
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Can't use MIN or MAX in my macro function

Postby FJCC » Thu Oct 20, 2016 7:42 pm

Here is an example of using WORKDAY() in a macro.
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.Sheets.getByName("Sheet3")
StartDate = oSheet.getCellrangeByName("A1").Value
DaysToAdd = oSheet.getCellrangeByName("B1").Value
oHolidays = oSheet.getCellrangeByName("C1:C2")
svc = createUnoService("com.sun.star.sheet.FunctionAccess")
result = svc.callFunction("WORKDAY",Array(StartDate,DaysToAdd,oHolidays))
print result
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
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: 6820
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Can't use MIN or MAX in my macro function

Postby Villeroy » Thu Oct 20, 2016 9:17 pm

malazkd wrote:Hello Sir Villeroy, sorry but I am a beginner dev and I'm new in macro scripting.
I am developing a template that generates multiple requests to be uploaded in a system.
I need WORKDAY to compute due date for each request. Would you know how to implement WORKDAY in macro?.... Thanks.

No, I only know how to implement such things without macros.
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: 25853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Can't use MIN or MAX in my macro function

Postby malazkd » Fri Oct 21, 2016 7:26 am

Thanks FJCC! I discovered that I am already implementing correct set of codes/syntax (same as yours)
I was encountering issues because my range in Holidays includes the column name/header.

it should be "E2:E30" and not "E1:E30"

Thank you so much everyone!
OpenOffice 4.1.2
malazkd
 
Posts: 3
Joined: Thu Oct 20, 2016 4:46 pm

Re: [Solved] Can't use MIN or MAX in my macro function

Postby Villeroy » Fri Oct 21, 2016 12:34 pm

This is the kind of problem which can be cleared up easily by entering the equivalent formula into a spreadsheet cell. You did not even tell us why you think that something is wrong with your code (in fact there is nothing wrong with it). No error message, no actual sheet data, no debugging result, nothing.
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: 25853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests