.ClearContents(some flags) not working as needed

[JohnPittman]

Hi,
I first tried in version 7x
I went to “stable” version
Version: 6.4.7.2 (x64)
Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5
CPU threads: 4; OS: Windows 10.0 Build 19041; UI render: GL; VCL: win;
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded

I am new to LibreOffice , and used excel vba before
I’m trying to learn this office replacement as I do not like the MS subscription model $$
In Calc
If I do a subroutine

sub ClearField
   ThisComponent.Sheets(0).GetCellRangeByName("D9:J24").ClearContents(7)
End sub

And use menu tolls marco run — it works everytime however…
I have also a Function that is where I need the clearcontents to do it’s thing
The function get data from a mysql table doing some math and put results in cells in range.
Then function on it’s on works just fine for getting and displaying the data.
but if I add the line to clearcontents

ThisComponent.Sheets(0).GetCellRangeByName("D9:J24").ClearContents(7)

in the function , it does not clear anything (oh, the cells contain strings and doubles)

If I try to call the subroutine from the function it does not work: Call ClearField or ClearField

I have placed breakpoints in code and added MsgBox for debugging, but can not find the error of my ways
I spent a couple of days with ver 7.x latest and then deleted and tried stable. Same results.
Just trying to learn stuff and keep this old brain working
John

(Slightly edited for better readabilty by @Lupp .)

[Lupp]

I may have misunderstood. Let me ask anyway:
Do you call the original Sub (the one calling the ‘clear’ Sub) from a Base component? If so the ThisComponent object should simply be the wrong component. I would expect an error reported, but there may be conditions suppressing it.
What about

Sub clearRange_S0_D9_J24(pDoc As Object)
   pDoc.Sheets(0).GetCellRangeByName("D9:J24").ClearContents(7)
End Sub  

and a call to this Sub like

clearRange_S0_D9_J24(mySpreadsheetDocument)

in the outer one?

[schiavinatto]

To use as a SubMacro, do the following:

Sub teste
	ClearField 0, "D9:J24", 7
End Sub


rem SubMacro
sub ClearField ( xPlan As Integer, xArea As String , xClear As Integer )
   ThisComponent.Sheets( xPlan ).GetCellRangeByName( xArea ).ClearContents( xClear )
End Sub

[JohnPittman]

schiavinatto
I tried your suggestion, but it did not work when called from my function.
Thanks anyway.
John

[Ratslinger]

Hello,

The problem is that with a function the main portion needs to complete before the clearing can run. With basic, by the finish of the main portion the information for clearing is no longer available.

Now I have come across this problem a few times before when dealing with Python. Because of this have come up with a solution but it requires a routine in Python. The placement of the code is your discretion - user area or in the document. You can use APSO for that → Alternative Script Organizer for Python.

In your Function you can replace this:

ThisComponent.Sheets(0).GetCellRangeByName("D9:J24").ClearContents(7)

with:

Dim oScriptProvider, oScript
Dim aParams(), aOutParamIndex(), aOutParam()
oScriptProvider = ThisComponent.getScriptProvider()
 Rem this is Python in document; change 'document' to 'user' if code is in user area
oScript = oScriptProvider.getScript( "vnd.sun.star.script:Clear_Cell_Functions.py$calcClearValue?language=Python&location=document")
get_sys_platform = oScript.invoke( aParams(), aOutParamIndex(), aOutParam() )

And here is the Python file (save as “Clear_Cell_Functions.py” as that is in the basic code presented):

#!/usr/bin/env python3
import uno
import sys
import time
from threading import Thread
def calcClearValue(*args):
    oDoc = XSCRIPTCONTEXT.getDocument()
    thread = Thread(target = log, args = (oDoc,))
    thread.start()
def log(oDoc):
    time.sleep(1)
    oSheet = oDoc.getSheets().getByIndex(0)
    sheet = XSCRIPTCONTEXT.getDocument().CurrentController.ActiveSheet
    cell_range = oSheet.getCellRangeByName("D9:J24")
    cell_range.clearContents(7)

g_exportedScripts = calcClearValue,

The key to the situation lies in a separate thread in the Python module. Don’t know how to do threading in basic or if even possible (vaguely seems to me it is).

The Python code is based upon the answer by Jim K in this post → Python macro - Calc - Stuck with getCellByPosition and threads

Have worked with Jim K resolving other Python issues including another with threading. He is very knowledgeable in Python as well as many other areas.

[Lupp]

Even Andrew Pitonyak doesn’t mention a way to run parallel threads from Basic (as far as I can see).
I’m not familiar with multi-threading by user-written software anyway, but surely you will need to check conditions for certain additional steps in the routine that created the parallel thread (Sub1) if there are any depencies. To enable checks for Sub1 you might require to communicate with Sub2 via global variables…
(please tell me if this was silly.)

[Ratslinger]

@Lupp,

Will look further today for threading in Basic. Easily could be mistaken but thought I saw something on this within the last six months. This dealing with threads is not clear to me. More than once have I had need for this:

Dialog problem when using Python

Base macro that opens a new/clean record in another form using python

What caught my attention here was in running the Function (sub works fine) did MRI on the object and was able to clear the contents. This was the same situation as other posts. Indirectly it worked. Led me to try the same method here and it worked. Still not clear as to why this is necessary.

As for dependencies, don’t see what this may be or how it is necessary. However, it would not be the first time I was wrong

[JohnPittman]

Ratslinger, Thank You!
It works for me, but had to do it as user. I could not figure out how to add to calc doc. But , I will read the online resources and figure it out. This is a learning experience for me. I did need to add a wait statement right after the call to python or the function would complete before the python thread finished. And on the sheet the data would just “flash” in the cells
I will need to brush up on python, it’s been about 10 years since I did some websites in php and python scripts.
Thanks again!
John

[Ratslinger]

@JohnPittman,

For embedding Python in document use APSO (link in answer). Also see this post for some insight → Did you know that you could also use APSO to Organize your Python scripts within LibreOffice?

[Ratslinger]

@Lupp,

I would say you are correct in regard to dependencies. Can envision situations where the thread is in a lengthy process and normal processing is available. One could step upon the other. Will need to do some further testing in this area.

Thank you for pointing this out.

Also, it appears my recollection of threading in Basic may be M$ related.

[Ratslinger]

With regard to dependencies, you can have another python module to be called to check if there are any existing threads running. See → look at threading.enumerate().

This will present the main thread (just executed) and any other threads currently running. Depending upon what is being done you can loop through until the thread is done - no longer in list.