Passing ranges to basic / python macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
f__l
Posts: 2
Joined: Fri Oct 07, 2022 4:47 pm

Passing ranges to basic / python macro

Post by f__l »

I am trying to pass ranges to a basic macro acting as bridge to a python function.
I found a way to cope with them when passing them as strings as in

Code: Select all

=CC("A1:B1", "parametri")
In this case I am using:

Code: Select all

function cc(my_range, my_parset as string) as double
    Dim myScript As Object
    Dim varIn(1),varIdx(0),varOut(0) As Variant
    varOut        = Array()
    myScript      = GetPythonScript("canoni/calcolo.py$CCanone", "document")
    varIn(0)      = my_range
    varIn(1)      = my_parset
    cc               = myScript.invoke(varIn,varIdx,varOut)
end function 
but I would like to pass them as 'real' ranges so that when I move the formula they change accordingly

Code: Select all

=CC(A1:B1, "parametri")
I thought I could transform the range into string using

Code: Select all

function cc(my_range as object, my_parset as string) as double
    Dim myScript As Object
    Dim varIn(1),varIdx(0),varOut(0) As Variant
    varOut        = Array()
    myScript      = GetPythonScript("canoni/calcolo.py$CCanone", "document")
    varIn(0)      = my_range.getRangeAddressesAsString()
    varIn(1)      = my_parset
    cc               = myScript.invoke(varIn,varIdx,varOut)
end function 
but I get

Code: Select all

BASIC runtime error.
Object variable not set.
alternatively getting the range as an array of values would do, but I do not know how to do it either.

Thanks for any hint
LibreOffice 7.4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Passing ranges to basic / python macro

Post by Villeroy »

"A1:B1" is a string with 2 letters, 2 digits and a colon.
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
f__l
Posts: 2
Joined: Fri Oct 07, 2022 4:47 pm

Re: Passing ranges to basic / python macro

Post by f__l »

well, yes, it is: that is the first case, and the python scripts uses the string (variable iv in the python script) as

Code: Select all

    v_range  = sheet.getCellRangeByName(iv)
    
    v_data   = v_range.getDataArray()
    v_sum    = 0.0
    for v in v_data:
        v_sum = v_sum + v[0]
and I get what I expect.
What I really want is a version where the range is not a string (unchanging when I move the formula in another cell) but an actual 'range' so that it changes when moving the formula to another cell but

Code: Select all

varIn(0)      = my_range.getRangeAddressesAsString()
does not give me "A1:B1" from the range A1:B1 in =CC(A1:B1, "parametri"), using the second definition of CC reported.
LibreOffice 7.4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Passing ranges to basic / python macro

Post by Villeroy »

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
User avatar
Lupp
Volunteer
Posts: 3546
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Passing ranges to basic / python macro

Post by Lupp »

(Back to "Basic as bridge")
The only way I know to pass a range directly via a parameter for which a range reference is given as argument of a user function is to use the

Code: Select all

Option VBAsupport 1
for a module (one of your Basic modules).
What you actually get then is an A-Range object supporting the (undocumented) service ooo.vba.excel.Range and providing a property .CellRange which you can use as the bridge to the ordinary LibreOffice API.
I'm using that option now and then, and it seems to be reliable enough for years now.
Please note that the mentioned .CellRange object does not support the single-cell services. Use

Code: Select all

ARANGEobject.CellRange.getCellByPosition(0, 0)
to get that.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31277
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Passing ranges to basic / python macro

Post by Villeroy »

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

Re: Passing ranges to basic / python macro

Post by Villeroy »

=CC(A1:X99) passes a 1-based 2-dimensional array to Basic. That array needs to be converted into nested 0-based arrays.
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