My macro is not working. It does practically nothing

English
,
#1

I can’t get macros to work properly!
I record a macro that sums 4 cells and then copies the value of that sum into an adjacent cell (so I can move the cells with the values later without worrying about the formulas). But when I play the macro nothing happens, except the cursor moves down 2 cells… What’s the problem?
I have LibreOffice ver. 6.4.7.2, which comes with Ubuntu 20.04. I even installed ver. 7.1.5.2, but it has exactly the same problem.

edit by @karolus actually we need more info, please copy&paste your macro_code …:

sub dode_sum_copy
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args6())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args8(1) as new com.sun.star.beans.PropertyValue
args8(0).Name = "By"
args8(0).Value = 1
args8(1).Name = "Sel"
args8(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args8())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:PasteOnlyValue", "", 0, Array())

rem ----------------------------------------------------------------------
dim args10(1) as new com.sun.star.beans.PropertyValue
args10(0).Name = "By"
args10(0).Value = 1
args10(1).Name = "Sel"
args10(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(1) as new com.sun.star.beans.PropertyValue
args11(0).Name = "By"
args11(0).Value = 1
args11(1).Name = "Sel"
args11(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(1) as new com.sun.star.beans.PropertyValue
args12(0).Name = "By"
args12(0).Value = 1
args12(1).Name = "Sel"
args12(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args12())


end sub
#2

please edit your question by c&p your code.

#3

Forget the macro recorder and learn to program macros. The macro recorder has many limitations and you’ll hardly get useful macros out of the recorder.

Ref.: LibreOffice Help - Recording a Macro - especially section “Limitations of the macro recorder”

…use a formula (just the SUM (SIGMA) symbol and afterwards use Data -> Calculate -> Formula to Value (though I wonder, why you use a spreadsheet application, if you don’t want “worry” about formulas)

#4

Long story short:
I need to copy the values of the SUMs elsewhere and use them in further calculations.

To be honest:
If I was a spreadsheet expert then MAYBE I would use pivot tables instead of macros and copying etc. But I only have a vague idea about what you can do with pivot tables and very little experience on how…

#5

Seems like you were hoping that pressing the Sigma button on the Formula Bar would be recorded in the macro code? I have to disappoint you - not all mouse clicks are recorded. Therefore, your code is missing a summation line.

I tried to translate your macro actions into normal notation. (This is not the best solution! In fact, it is written differently, much shorter, but for a beginner in programming, the short solution will be incomprehensible). Hope the comments help you understand what the code does and how:

Sub SetSum4CellsFromCurrent
Dim oCurrentSelection As Variant
Dim oStartCell As Variant
Dim aCellAddress As New com.sun.star.table.CellAddress
Dim nColumn As Long, nRow As Long
Dim oSheet As Variant
Dim oCellRangeForSum As Variant
Dim sRangeName As String
Dim oCellResult As Variant
Rem To start your moving down we need know in which cell cursor at now:
	oCurrentSelection = ThisComponent.getCurrentSelection()
Rem We use the first cell of the current selection 
Rem (zero column and zero row of selection) as the starting one:
	oStartCell = oCurrentSelection.getCellByPosition(0, 0)
	aCellAddress = oStartCell.getCellAddress()
	nColumn = aCellAddress.Column
	nRow = aCellAddress.Row
Rem Which sheet of this spreadsheet is the cursor on?
	oSheet = oStartCell.getSpreadsheet()
Rem What range of cells do we want to sum?
Rem In the current column from the current row to the row "current + 3"
	oCellRangeForSum = oSheet.getCellRangeByPosition(nColumn, nRow, nColumn, nRow+3)
Rem How is the address of this range written?
	sRangeName = oCellRangeForSum.AbsoluteName
Rem Where should the result be placed?
	oCellResult = oSheet.getCellByPosition(nColumn+1, nRow+3)
Rem Write the SUM() function, specifying a range of cells as a parameter:
	oCellResult.setFormula("=SUM(" & sRangeName & ")")
Rem Replace the formula with its value
	oCellResult.setValue(oCellResult.getValue())
Rem At the end of your macro, the cursor moves two more rows down
	oStartCell = oSheet.getCellByPosition(nColumn, nRow+5)
	oController = ThisComponent.getCurrentController()
Rem Select new cell
	oController.select(oStartCell)
Rem "Deselect" it
	oController.select(ThisComponent.CreateInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub
2 Likes
#6

Thank you for that code. It doesn’t REALLY do what I want though… :slight_smile:
I wanted the SUM-formula-cell below the 4 cells and the value-copy to the right of THAT cell. And in the end the macro should move the cursor 2 cells down to get to the next 4 cells that need summing. :slight_smile:
I am shocked that the macro recorder is so extremely primitive! This makes Calc VERY limited if you ask me.
Another incomprehensible limitation is that assigning the macro to a keyboard combination is not part of the recording process itself.
I am sorry for asking a heretical question :slight_smile: :
How does LibreOffice Calc compare to MS-Office Excel when it comes to macro recording?

#7

Absolutely. Fully. Generally, different principles. Unlike Excel, whose macro recorder is inextricably linked to document architecture, Calca’s macro recorder uses Basic to record sequential calls to user interface commands. Not all, but only those with a not very complex set of parameters. This is similar to the simplest macros from “Excel for Dummies” - “paint the cell in red”, “make the font bold”.

If I didn’t know that I’m talking to a person who is taking the first steps in mastering LibreOffice, I would certainly take offense and write something like “Well, in this sad situation you should never ever try to use anything else but Microsoft” or even “Please, do us all a favour: Get a legal copy of Excel and a good book on spreadsheets”

The macro I wrote for you contains all the statements you need to accomplish this task. Examine the code, try to change it, make it work, and be glad you learned something useful. Good luck!

#8

Yes! You are right. That was not too hard. :slight_smile: Now it works perfectly.

#9

Since the problem has been solved, please mark the answer or comment that contains the solution.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.