Do ... Loop - Finite Repeat Command

asked 2019-10-12 08:46:06 +0200

Orlando gravatar image

updated 2020-07-27 10:57:48 +0200

Alex Kemp gravatar image

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

8)

edit retag flag offensive close merge delete

Comments

1

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.

Opaque gravatar imageOpaque ( 2019-10-12 11:31:24 +0200 )edit
1

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...

Mike Kaganski gravatar imageMike Kaganski ( 2019-10-12 14:42:45 +0200 )edit

You're right, @Opaque! 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, @Mike Kaganski! I don't know how to configure args1 (0) .Value so that the loop is finite. :(

hug!

Orlando gravatar imageOrlando ( 2019-10-12 18:20:11 +0200 )edit
1

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")
Mike Kaganski gravatar imageMike Kaganski ( 2019-10-12 19:57:06 +0200 )edit

\ o / Perfect, @Mike Kaganski !!!

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!!

:)

Orlando gravatar imageOrlando ( 2019-10-13 04:11:52 +0200 )edit
add a comment