Why doesn't LibreOffice macros record relative references... only absolute references?

English
, , , , ,
#1

When I record a macro that will “cut & paste” data entries in adjacent columns and move them to different adjacent columns in the same row (Example - “cut & paste” E2:N2 and move to D2:M2)… the macro always does EXACXTLY that. In other words, I could be at N302 when I execute the macro I recorded and the data between E2:N2 would be moved to D2:M2… NOT the data between E302:N302 moved to D302:M302.

The attached ‘.ods’ spreadsheet works properly when opened in AOO (ver4.1.4 - where it was created), but not when opened in LO (ver6.4.7.2(x64)). Working properly means: after entering a # in column N and clicking on the push button… the contents of E-N are moved to D-M and the contents of column N is cleared. Column B averages the contents of Column D-M.

AOOMacro.ods
AOOMacro.odt

(Edit: tried to fix (one hidden) downloads links -AK)

1 Like
#2

In fact the question uses the terms “relative” and “absolute” wrongly in place of “at runtime” and " at recordingtime"

#14

Start recording
Select E2:N2
Copy
Hit left arrow key (one cell to the left)
Paste
End recording

#3

You need WRITE your macros instead of recording them - if you want to work with the macros efficiently.

The reference E2:N2 is always means the cell range E2:N2 in the recorded macro code (even if it is E2:N2 or $E$2:$N$2). It never will be recalculated automatically. YOU MUST create calculated “relative” references.

You can get the SELECTED CELL RANGE for the cut & paste function, or you can SELECT the desired cell range based on the reference information of the active cell.

#4

Thanks for the info. Although I’m self taught in using some basic formulas in a spreadsheet, I have no idea how to write/code macros. When I was using ApacheOpenOffice for this same spreadsheet I had no issues recording and running this macro.

1 Like
#5

“When I was using ApacheOpenOffice for this same spreadsheet I had no issues recording and running this macro.”

Please upload the macro code and a sample file here.
The macro recording methods of the AOO and LO are same.

#6

I feel like a dummy. Can’t figure out how to attach files, so I can upload my “.ods” spreadsheet file from LibreOffice and a text file of the Macro as it appeared in AOO.

#7

“I feel like a dummy. Can’t figure out how to attach files,”
Edit your question. You will find the Attachment icon at the top of the edit box.

#8

This may be a bug in recent versions of LO Calc, but until they fix it this is a way to record a macro for relative cell reference (e.i. one that applies to any cell):

  1. Click on any empty cell in your spreadsheet.
  2. Click on Record Macro
  3. If you are using the Linux version of LO Calc proceed with clicking on any key or combination of keys, record and save your macro.
  4. If you are using Windows version of LO Calc clicking on Record Macro seems to lock the keyboard and you can only start by a.) clicking on a cell, which makes the macro an absolute refrence to that cell, or b.) clicking on a tool Icon in the tool bar (e.g. Cut, Copy, etc.). If choose B, the refence is relative to the whole work sheet (assuming you cursor was on an empty cell before you clicked on Record Macro)

(Slightly edited by @Lupp for better readability.)

2 Likes
#9

In fact the question uses the terms “relative” and “absolute” wrongly in place of “at runtime” and " at recordingtime"
The actions concerning ranges of cells always are executed for the current selection by recorded macros. If you select a range during macro recording (multiselection disabled!) the range address is stored “looking absolute”. Since it isn’t changed at runtime, that’s meaningless. In fact the macro always makes a “GoTo” of that selection.
The only way to make a recorded macro “sensitive” for the CurrentSelection at runtime, is to rework it based on API usage like in ThisComponent.CurrentSelection, and to assignethe .AbsoluteName of a CellRange gotten this way to the respective argsX((n).Value.
(Recorded macros also don’t preserve the previous selection.)
If the execution of the macro for the SheetCellRange being CurrentSelection at runtime, you need to do the selecteion in advance of the recording!

#10

I find it interesting how simple this is in MS Excel. And, once created in Excel VBA it seems to run in Calc as it should.

Which (to me anyway) confirms that @Tamas’s answer of “it’s a bug” is confirmed. I’m glad I have Excel as a fall back even though I love and mostly use Calc.

#11

No, this only confirms Open/LibreOffice is not M$-Office. You may also deduct, a better Macro-Recorder is possible.

1 Like
#12

I assume you meant deduce, and yes here I am again a year later with a years worth of L.O. updates, and the same silly issue because Calc on Windows isn’t the same as Calc on Linux. So I deduce that the Calc development is Linux focused.

… again, cutting & pasting from Calc to Excel. A shame, imo.

#13

and the problem was/is:

So you think the “may be” is confirmed. Actually it is a missing feature. But as I see no trace here anybody created a report at the place where developers expect bug-reports as well as enhancement requests (Bugzilla) I would not expect much progress there…
.

Imho both come from the same source, but it seems you are the expert on conclusions here. There is one difference in the role of python, but as this is not used by the script-recorder it should not be important (and it is not the choice of TDF, but of maintainers for Linux distros -like the decissions to use Snap or cripple Base because of Java).

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