Can a script macro have arguments like a built-in function?

Keyboard macros or custom scripts

Can a script macro have arguments like a built-in function?

Postby jason1984 » Sat Dec 15, 2018 4:46 pm

Hello,

I get to create a macro script for Libreoffice-Calc using python. The script is pretty much a function with arguments (a cell coordinate and a variable N) that need to manually set whenever it is called.
I tried to call the macro like a built-in function but it didn't recognize it as a function.
If I run it directly with "Tools-...-Run macro", unsurprisingly it results in an error saying it's missing positions (col, row, N).

Is it possible to have arguments in the macro script? If not have we a workaround?
Libreoffice 6.1.3.2 on openSUSE Leap 15.1
jason1984
 
Posts: 6
Joined: Tue Dec 11, 2018 5:20 pm

Re: Can a script macro have arguments like a built-in functi

Postby Zizi64 » Sat Dec 15, 2018 5:08 pm

I get to create a macro script for Libreoffice-Calc using python.

Did you tried to write it as a StarBasic Function?

...or at least try to write a wrapper function for your function in StarBasic.

But your StarBasic function or a simple Wrapper function will not appeare in the function list of the Calc. You must know the name of the function, and the parameters if you want use it.



I think you must write a more complicated Add-on for integration into the function list...
Last edited by Zizi64 on Sat Dec 15, 2018 5:15 pm, edited 2 times in total.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7712
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can a script macro have arguments like a built-in functi

Postby Zizi64 » Sat Dec 15, 2018 5:10 pm

Can you upload the code of your function here?
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7712
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can a script macro have arguments like a built-in functi

Postby RoryOF » Sat Dec 15, 2018 5:12 pm

Yes it is possible: design an input dialog as part of the macro and use the entered inputs, or, (simpler) use input boxes.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28202
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can a script macro have arguments like a built-in functi

Postby Villeroy » Sat Dec 15, 2018 5:31 pm

Regarding user defined spreadsheet functions, you can have either one of these:
1. A StarBasic function in the "Standard" library which always accessible since it is loaded automatically.
2. A function provided by a Calc add-in. This can be written in an object oriented language having a bridge to UNO (Python, Java, C++) but NOT StarBasic. These functions are localizable and appear in Calc's user interface (function wizzard, balloon help etc). Add-in functions do not appear among the macros.

A simple but powerful Python add-in wrapping Python string functions in one spreadsheet function: viewtopic.php?f=20&t=83856&p=389632#p389632
A sudoku solver "stolen" from Peter Norvig wrapped into an UNO array function: download/file.php?id=36503
IDL reference for add-ins: http://www.openoffice.org/api/docs/comm ... AddIn.html
However, there are ways to call a Python macro from a StarBasic macro: viewtopic.php?f=45&t=73145&p=329658&hilit=scriptprovider#p329658
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: 26267
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a script macro have arguments like a built-in functi

Postby jason1984 » Sat Dec 15, 2018 5:37 pm

Zizi64 wrote:Can you upload the code of your function here?

New to coding but ok..
Code: Select all   Expand viewCollapse view
"""This script is to create a macro for libreoffice calculator, in order to calculate the final score of a single
student."""

import uno
import openpyxl
import os
from openpyxl.utils import column_index_from_string



def score_cal(col, row, N):  # get the coodinate of the first cell in the format like 'C4', and N is the number of weeks
    desktop = XSCRIPTCONTEXT.getDesktop()
    model = desktop.getCurrentComponent()    # access the current writer document
    sheet = model.CurrentController.ActiveSheet
    startingCol = column_index_from_string(col)  # Get the column index
    count_A = 0  # initial grades count to 0
    count_B = 0
    count_C = 0
    count_D = 0
    count_K = 0
    count_J = 0
    for col in sheet.iter_cols(min_row=row, min_col=startingCol, max_col=startingCol + N * 2,
                               max_row=row):
        for cell in col:
            if cell.value == 'A':
                count_A = count_A + 1
            elif cell.value == 'B':
                count_B = count_B + 1
            elif cell.value == 'C':
                count_C = count_C + 1
            elif cell.value == 'D':
                count_D = count_D + 1
            elif cell.value == 'K':
                count_K = count_K + 1
            elif cell.value == 'J':
                count_J = count_J + 1
    final_score = int((count_A * 95 + count_B * 85 + count_C * 75 + count_D * 65) / N  - count_K * 5 - count_J)
    return final_score
Last edited by jason1984 on Sat Dec 15, 2018 5:39 pm, edited 1 time in total.
Libreoffice 6.1.3.2 on openSUSE Leap 15.1
jason1984
 
Posts: 6
Joined: Tue Dec 11, 2018 5:20 pm

Re: Can a script macro have arguments like a built-in functi

Postby Villeroy » Sat Dec 15, 2018 5:38 pm

You don't need your user defined function for this. It is trivial to do with any spreadsheet program (even more trivial with a database)
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: 26267
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can a script macro have arguments like a built-in functi

Postby jason1984 » Sat Dec 15, 2018 6:15 pm

Villeroy wrote:You don't need your user defined function for this. It is trivial to do with any spreadsheet program (even more trivial with a database)


The main purpose is to learn how libre-office macro works. I might use it in other scenarios.

Checked your links but didn't understand how it help solve my problem.

So adding just a few lines in my code to enable arguments is not possible?
Libreoffice 6.1.3.2 on openSUSE Leap 15.1
jason1984
 
Posts: 6
Joined: Tue Dec 11, 2018 5:20 pm

Re: Can a script macro have arguments like a built-in functi

Postby Villeroy » Sat Dec 15, 2018 6:41 pm

No, it's not possible. You've got to learn first things first.
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: 26267
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 3 guests