Do ... Loop - Finite Repeat Command

Orlando · October 12, 2019, 6:46am

I don’t know why the macro I recorded is giving error! I added the “Do … Loop Until” command to the end of the macro.

The macro’s function is to stop the sum in cell A7 when the result equals 0 (zero).

Below is an attached template sheet.

In A1: A6

= INT (RAND () * 2)

In A7

= SUM (A1: A6)

Sub Main
dim document   as object
dim dispatcher as object
dim args1(0) as new com.sun.star.beans.PropertyValue
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
args1(0).Name = "ToPoint"
args1(0).Value = "$Sheet1.$A$7"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

Do
	ThisComponent.calculateAll()
Loop Until args1(0).Value = 0
End Sub

Finite Loop (updated)

Grateful for the attention!

Orlando Souza

anon73440385 · October 12, 2019, 9:31am

Looks like you accidentally deleted dim args1(0) as new com.sun.star.beans.PropertyValue or macro didn’t record that. But you did neither specify your operating system nor your LibreOffice version, so nobody could check any details.

mikekaganski · October 12, 2019, 12:42pm

Anyway, it’s unclear why would you expect args1(0).Value (that you’ve set to string “$Sheet1.$A$7”) to become 0 after a recalculation…

Orlando · October 12, 2019, 4:20pm

You’re right, @anon73440385! I already updated the transported macro from my main spreadsheet. The file is used in various versions of operating system or LibreOffice, because it is transported on a USB stick.

Got it, @mikekaganski! I don’t know how to configure args1 (0) .Value so that the loop is finite.

hug!

mikekaganski · October 12, 2019, 5:57pm

Try this.

Dim oSheet As Object
oSheet = ThisComponent.CurrentController.ActiveSheet
Dim oCell As Object
oCell = oSheet.getCellByPosition(0, 6)
Do
    ThisComponent.calculateAll()
Loop Until oCell.Value = 0

Note that you may also get the cell using

oCell = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("A7")

Orlando · October 13, 2019, 2:11am

\ o / Perfect, @mikekaganski !!!

Because my spreadsheet involves many columns with formulas, I had to include the WAIT 0 (suggestion from another Contributor) command after the ThisComponent.calculateAll () line, to avoid spreadsheet locks.

.

Thank you very much!!