How to convert solution from MS Excel (VBA) to Libreoffice-Calc

Question

How to do it in Libreoffice-Calc?

OnTime for less than 1 second without becoming Unresponsive

This is the solution:

Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, _ 
    ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, _
    ByVal nIDEvent As LongPtr) As Long

Public TimerID As Long

Sub StartTimer()
    ' Run TimerEvent every 100/1000s of a second
    TimerID = SetTimer(0, 0, 100, AddressOf TimerEvent)
End Sub

Sub StopTimer()
    KillTimer 0, TimerID
End Sub

Sub TimerEvent()
    On Error Resume Next
    Cells(1, 1).Value = Cells(1, 1).Value + 1
End Sub

It would be great if everything worked under different OS

Thanks for the link, @JohnSUN. I couldn't adapt this solution to my task. I need to make a loop like in the controller to poll the state of the buttons inside it. I saw some of your messages on the forums. Maybe you can make a small example. Thank you. — Youra_P
– Youra_P, Commented Sep 28, 2024 at 10:45
@Youra_P: Would you consider a Python solution, or Basic only? — Jim K
– Jim K, Commented Sep 28, 2024 at 10:46
The best solution would be Basiс, иut another option is also possible. @Jim K — Youra_P
– Youra_P, Commented Sep 28, 2024 at 10:52
Is it possible to have a python macro that calls a basiс macro with an interval of less than one second? @Jim K — Youra_P
– Youra_P, Commented Sep 28, 2024 at 10:55

Jim K · Accepted Answer · 2024-09-28 21:31:53Z

3

Here is a python macro that calls a Basic routine every 10th of a second. For illustration, it stops itself after 20 seconds.

import threading

def do_call():
    desktop = XSCRIPTCONTEXT.getDesktop()
    ctx = XSCRIPTCONTEXT.getComponentContext()
    smgr = ctx.ServiceManager
    oMasterScriptProviderFactory = smgr.createInstanceWithContext(
        "com.sun.star.script.provider.MasterScriptProviderFactory", ctx)
    oScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
    oScript = oScriptProvider.getScript(
        "vnd.sun.star.script:Standard.Calc1.IncrementValue?"
        "language=Basic&location=application")
    oScript.invoke((), (), ())

class BasicScriptCaller:
    def __init__(self):
        self.timer = None

    def call_basic_script(self):
        do_call()
        self.timer = threading.Timer(0.1, self.call_basic_script)
        self.timer.start()

    def stop_basic_script(self):
        if self.timer is not None:
            self.timer.cancel()

    def start(self):
        self.call_basic_script()
        threading.Timer(20, self.stop_basic_script).start()

def start():
    caller = BasicScriptCaller()
    caller.start()

g_exportedScripts = (start,)

This routine increments cell A1, making something that looks like a timer.

Sub IncrementValue
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oCell = oSheet.getCellByPosition(0, 0)
    iOldVal = oCell.getValue()
    oCell.setValue(iOldVal + 1)
End Sub

To turn off, the thread could stop itself if a cell, say B1, has a non-zero value. Then you could make a button or checkbox to automatically set B1 to 1 instead of 0. Also, stop if the document is no longer open.

Update

Here are the code changes you requested in the comment. It runs until cell B1 has a value. Also, it allows start() to optionally accept the button event parameter.

class BasicScriptCaller:
    def __init__(self):
        self.timer = None

    def stop_condition(self):
        """Return true if the condition to stop is met."""
        oSheet = XSCRIPTCONTEXT.getDocument().getSheets().getByIndex(0)
        oCell = oSheet.getCellByPosition(1, 0)  # B1
        return bool(oCell.getString()) and oCell.getString().strip()

    def next_call(self):
        do_call()
        if not self.stop_condition():
            self.timer = threading.Timer(0.1, self.next_call)
            self.timer.start()

def start(button_evt=None):
    caller = BasicScriptCaller()
    caller.next_call()

g_exportedScripts = (start,)

edited Sep 28, 2024 at 21:31

answered Sep 28, 2024 at 16:28

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Youra_P Over a year ago

Thank you, @Jim K. For me, it was necessary to fix: Standard.Module1.IncrementValue . If I connect a macro to a button, it gives an error: (<class 'TypeError'>: start() takes 0 positional arguments but 1 was given File "C:\Program Files\LibreOffice\program\pythonscript.py", line 913, in invoke ret = self.func( *args )

Youra_P Over a year ago

Last request. Remove the second timer and make a stop macro. Thank you, @Jim K.

Jim K Over a year ago

@Youra_P: See updated answer.

Collectives™ on Stack Overflow

How to convert solution from MS Excel (VBA) to Libreoffice-Calc

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related