## Calculating by macro

Creating a macro - Writing a Script - Using the API

### Calculating by macro

Hello everybody,
My macro practice has been a long time ago. Can someone please tell me the source code for the following example (The rest I should create myself, but the beginning is very difficult for me, which classes / objects / variables such as declare / define, address lines etc ...)?

I would like to add the numbers from column B to column D by pressing the button.
macro.jpg (8.31 KiB) Viewed 543 times

After pressing the button twice, the upper result would come out. After three times:
3
6
9
etc...

Many, many thanks in advance !!!

Andrej
Apache OpenOffice 4.1.5
Win10 Pro, 10.0.17134
Andrej

Posts: 2
Joined: Tue Dec 25, 2018 7:36 pm

### Re: Calculating by macro

If there are only three cells as in your example, you can use this
Code: Select all   Expand viewCollapse view
`oSheet1 = ThisComponent.Sheets.getByName("Sheet1")oCellB2 = oSheet1.getCellrangeByName("B2")oCellB3 = oSheet1.getCellrangeByName("B3")oCellB4 = oSheet1.getCellrangeByName("B4")oCellD2 = oSheet1.getCellrangeByName("D2")oCellD3 = oSheet1.getCellrangeByName("D3")oCellD4 = oSheet1.getCellrangeByName("D4")oCellD2.Value = oCellD2.Value + oCellB2.ValueoCellD3.Value = oCellD3.Value + oCellB3.ValueoCellD4.Value = oCellD4.Value + oCellB4.Value`

If there are many cells, a more elegant solutions would use a loop.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Calculating by macro

Als erstes vielen Dank für die schnelle Antwort!

Habe noch eine weiter Frage im Rahmen meines „Projekts“:
Ich lasse Zufallswerte in mehreren Zellen berechnen (via „Shift+Strg+F9“) solange bis deren Summe („B2“) eine max. Abweichung von 5% vom Basiswert ergibt.

Ich habe ein Makro aufgezeichnet und versucht eine Schleife um diese Aufzeichnung herumzubasteln, komme allerdings nicht weiter...
zufallszahl.jpg (10.77 KiB) Viewed 497 times

===
sub Zufall

dim document as object
dim dispatcher as object
Dim oDoc as Object
Dim oSheet as Object

document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

oDoc=ThisComponent
oSheet=oDoc.Sheets().getByIndex(0)

oCellA2 = oSheet.getCellrangeByName("A2")
oCellB2 = oSheet.getCellrangeByName("B2")

dispatcher.executeDispatch(document, ".uno:CalculateHard", "", 0, Array())

While ((oCellA2.value / oCellB2.value)< (0,95 * oCellA2.value) OR (oCellA2.value / oCellB2.value)> (1,05 * oCellA2.value))
dispatcher.executeDispatch(document, ".uno:CalculateHard", "", 0, Array())
Wend

end sub
===

Kann mir da bitte jemand helfen?

Vielen Dank!!!
Apache OpenOffice 4.1.5
Win10 Pro, 10.0.17134
Andrej

Posts: 2
Joined: Tue Dec 25, 2018 7:36 pm

### Re: Calculating by macro

Does the attached file do what you want?
Attachments
SumRandom.ods
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Calculating by macro

Andrej wrote:Als erstes vielen Dank für die schnelle Antwort!

Thanks to keep in English. Your discussion may be useful to other English speaking users.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).

Hagar Delest
Moderator

Posts: 28919
Joined: Sun Oct 07, 2007 9:07 pm
Location: France