Porting Excel VBA to LibreOffice: AddressOf

Question

I have a function in some Excel VBA which sets up a timer. When I try to run the code, LibreOffice Calc complains that the "parentheses do not match".

Here is the function in question:

Sub StartTimer()
    Dim f As Single
    f = Range("K2").Value
    Dim t As Single
    t = 1 / f
    t = t / 2
    TimerID = SetTimer(0&, 0&, t * 1000&, AddressOf TimerProc)
End Sub

The part of code highlighted by the IDE when it errors is this: SetTimer(0&, 0&, t * 1000&, AddressOf I think it's the AddressOf part which it has trouble with, it doesn't seem to be rendered like a keyword. Is there some equivalent to this in LibreOffice's implementation of VBA? Google searching tends to just produce Microsoft's docs which are not helpful.

Here is the full module:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public dTime As Date

Public Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long, _
    ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long

Sub StartTimer()
    Dim f As Single
    f = Range("K2").Value
    Dim t As Single
    t = 1 / f
    t = t / 2
    TimerID = SetTimer(0&, 0&, t * 1000&, AddressOf TimerProc)
End Sub
      
Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)
      Dim c As Integer
      c = Range("K1").Value
      c = c Xor 1
      Range("K1").Value = c
End Sub

Sub Macro1()
    Dim c As Integer
    c = Range("K1").Value
    c = c Xor 1
    Range("K1").Value = c
    
    Dim f As Single
    f = Range("K2").Value
    Dim t As Single
    t = 1 / f
    Sleep (t * 1000)
    
    c = Range("K1").Value
    c = c Xor 1
    Range("K1").Value = c
End Sub

@JohnSUN's BeanShell solution no doubt works, but personally, I would write this macro in Python-UNO using threading.timer() and time.sleep(). Something like ask.libreoffice.org/en/question/249535/…. Avoid Option VBASupport 1 and Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) as these things are likely to get you headed in the wrong direction. — Jim K
– Jim K, Commented Sep 4, 2020 at 14:22
One more idea: Would something like this meet your needs? ask.libreoffice.org/en/question/96559/… — Jim K
– Jim K, Commented Sep 4, 2020 at 14:30

JohnSUN · Accepted Answer · 2020-09-04 07:23:06Z

It is not a good idea to use solutions that are tied exclusively to Microsoft tools in LibreOffice. You lose the main advantage of the package - multi-platform, your solution won't work on Linux or MacOS.

The principle of a timer is very simple - a separate thread simply counts down the time and signals to your main code.

In Basic LibreOffice there is no way to create threads. But Basic is not the only programming language available to you.

Let's see this example. It's quite old, but still works.

C:\FakePath\DemoTimer.ods

Basic code is not very complicated

Sub Timermacro
Rem This procedure is called by pressing the START button - 
Rem indicates to the timer how often it should work and how many times and starts it.
Dim nTime As Long, nCount As Long
Rem These variables - oJob1 and oP - are described in the adjacent module.
    oP = GenerateTimerPropertySet()
    oJob1 = createUnoListener("JOB1_", "com.sun.star.task.XJobExecutor")
    oP.xJob = oJob1
    getParams(nTime, nCount)
    oP.lMaxIterations    = nCount
    oP.lPeriodInMilliSec = nTime * 1000
    oP.start()
End Sub

Sub StopTimer
Rem This is the handler for pressing the STOP button. Everything is very simple here.
    oP.stop()
End Sub

Function GenerateTimerPropertySet() As Any
Dim oSP As Variant 
Dim oScript As Variant 
    oSP    = ThisComponent.getScriptProvider("")
Rem Pay attention to this line - this is the main trick of this solution
Rem The current document (location=document) contain BeanShell code (language=BeanShell))!
Rem It's called timer.bsh and is in the timer library (timer.timer.bsh)
    oScript = oSP.getScript("vnd.sun.star.script:timer.timer.bsh?language=BeanShell&location=document")
    GenerateTimerPropertySet = oScript.invoke(Array(), Array(), Array())
End Function

Sub JOB1_trigger(s As String)
Rem Here we are simply demonstrating that the timer is doing its job.
Rem After each timer is triggered, add the current date and time and countdown number.
Dim oSheet As Variant
Dim oCursor As Variant
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim nEndRow As Long
Dim oCellRangeByPosition As Variant
Dim oDataArray As Variant

    oSheet = ThisComponent.getSheets().getByIndex(0)
    oCursor = oSheet.createCursor()
    
    oCursor.gotoEndOfUsedArea(False)
    aRangeAddress = oCursor.getRangeAddress()
    nEndRow = aRangeAddress.EndRow+1
    
    oCellRangeByPosition = oSheet.getCellRangeByPosition(0, nEndRow, 1, nEndRow)
    oDataArray = oCellRangeByPosition.getDataArray()
    oDataArray(0)(0) = Format(Now,"YYYY-MM-DD HH:mm:SS")
    oDataArray(0)(1) = s
    oCellRangeByPosition.setFormulaArray(oDataArray)
End Sub 

Sub getParams(nTime As Long, nCount As Long)
Rem This procedure prepares parameters for the timer.
Rem In this case, they are simply read from the cells of the first sheet of the current book.
Rem But you can set them in any way - ask in the dialog, calculate by the current time, 
Rem generate according to a special algorithm, etc.
Dim oSheet As Variant
    oSheet = ThisComponent.getSheets().getByIndex(0)
    nTime = oSheet.getCellByPosition(4, 4).getValue()
    nCount = oSheet.getCellByPosition(4, 5).getValue()
End Sub

The main trick of the solution is here:

Based on one of the comments in the code, ms777 posted this a long time ago, when OOO 2.4 was still in use. It is already difficult to find that OpenOffice anywhere, the site on which this solution was published has not existed for a long time - but the solution works.

I am quoting the ms777's code unchanged, "as is":

import com.sun.star.uno.Type;
    import com.sun.star.uno.UnoRuntime;
    import com.sun.star.lib.uno.helper.PropertySet;
    import com.sun.star.lib.uno.helper.WeakBase;
    import com.sun.star.task.XJobExecutor;
    import com.sun.star.lang.XInitialization;
    import com.sun.star.beans.PropertyValue;
    import com.sun.star.beans.XPropertyChangeListener;
    import com.sun.star.beans.PropertyChangeEvent;
    import com.sun.star.lang.EventObject;
    import com.sun.star.uno.AnyConverter;
    import com.sun.star.xml.crypto.sax.XElementStackKeeper ; // defines a start and a stop routine

    // Workaround for  http://qa.openoffice.org/issues/show_bug.cgi?id=89978 needed from OO 2.4 onwards
    Thread.currentThread().setContextClassLoader(this.getClass().getClassLoader());

    // This prevents an error message when executing the script a second time

    try { Class.forName("ms777Timer_06");}
      catch (ClassNotFoundException e) {
      System.out.println( "class not found - compiling start" );


    public class ms777Timer_06 extends PropertySet  implements XElementStackKeeper
      {

    // These are the properties of the PropertySet
      public boolean bFixedRate = true;
      public boolean bIsRunning = false;
      public int lPeriodInMilliSec = 2000;
      public int lDelayInMilliSec = 0;
      public int lMaxIterations = 5;
      public int lCurrentIteration = 0;
      public XJobExecutor xJob = null;

    // These are some additional properties
      Task xTask =null;
      Timer xTimer = null;

      public ms777Timer_06()  {
        registerProperty("bFixedRate",  (short) 0);
        registerProperty("bIsRunning",  (short) com.sun.star.beans.PropertyAttribute.READONLY);
        registerProperty("lPeriodInMilliSec",  (short) 0);
        registerProperty("lDelayInMilliSec",  (short) 0);
        registerProperty("lMaxIterations",  (short) 0);
        registerProperty("lCurrentIteration",  (short) 0);
        registerProperty("xJob",  (short) com.sun.star.beans.PropertyAttribute.MAYBEVOID);
        xTimer = new Timer();
        }

    //XElementStackKeeper
      public void start() {
        stop();
        if (xJob==null) {return;}
        xTask = new Task();
        lCurrentIteration = 1;
        bIsRunning = true;
        if (bFixedRate) {
          xTimer.scheduleAtFixedRate( xTask, (long) lDelayInMilliSec, (long) lPeriodInMilliSec );
          } else {
          xTimer.schedule( xTask, (long) lDelayInMilliSec, (long) lPeriodInMilliSec );
          }
        }

      public void stop() {
        lCurrentIteration = 0;
        bIsRunning = false;
        if (xTask!=null) { xTask.cancel();}
        }

      public void retrieve(com.sun.star.xml.sax.XDocumentHandler  h, boolean  b) { }

    class Task extends TimerTask  {
        public void run()  {
            xJob.trigger(lCurrentIteration.toString());
            lCurrentIteration +=1;
            if (lCurrentIteration > lMaxIterations) {
              stop();
              }
          }
        }
      }

    System.out.println( "class not found - compiling end" );
    } // of   catch (ClassNotFoundException e)

    System.out.println( "generating timer property set ... " );
    return new ms777Timer_06();

I hope this will solve your problem.

Collectives™ on Stack Overflow

Porting Excel VBA to LibreOffice: AddressOf

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related