Calculating by macro

Creating a macro - Writing a Script - Using the API

Calculating by macro

Postby Andrej » Tue Dec 25, 2018 7:41 pm

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

Postby FJCC » Wed Dec 26, 2018 3:19 am

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.Value
oCellD3.Value = oCellD3.Value + oCellB3.Value
oCellD4.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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calculating by macro

Postby Andrej » Wed Dec 26, 2018 3:37 pm

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

Postby FJCC » Fri Dec 28, 2018 5:52 pm

Does the attached file do what you want?
Attachments
SumRandom.ods
(9.22 KiB) Downloaded 41 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calculating by macro

Postby Hagar Delest » Sat Dec 29, 2018 4:15 pm

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).
User avatar
Hagar Delest
Moderator
 
Posts: 28919
Joined: Sun Oct 07, 2007 9:07 pm
Location: France


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests