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

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
jason1984
Posts: 6
Joined: Tue Dec 11, 2018 5:20 pm

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

Post by jason1984 »

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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

Can you upload the code of your function here?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Post by RoryOF »

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.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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&hil ... er#p329658
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
jason1984
Posts: 6
Joined: Tue Dec 11, 2018 5:20 pm

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

Post by jason1984 »

Zizi64 wrote:Can you upload the code of your function here?
New to coding but ok..

Code: Select all

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

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jason1984
Posts: 6
Joined: Tue Dec 11, 2018 5:20 pm

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

Post by jason1984 »

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

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply