"Record Macro" recorded a null (no actions) macro

dhlocker1 · January 27, 2025, 1:02am

(Calc 24.8.3.2, Ubuntu 24.04)
Since it has been SO long since I did any macro programming, I decided to just record a simple macro to get started (consisting of the following keystrokes: <F2> <backspace> <move-left> <backspace> <Ctrl-Enter> <Enter>), but the recorded macro is basically a null function, as below. I also recorded the same macro, but with a trailing <move-down> keystroke and it actually moves down one cell after not editing the cell I started on.

Suggestions? (I tried reading the help but got lost in the object hierarchy; all I want to do is automate my editing of several hundred cells with extraneous whitespace.)

One last note - the material between the trailing space character and the other space character I was deleting is a link with text that differs from the URL. Regex substitution only preserved the text, not the URL.

REM  *****  BASIC  *****


sub Main
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 ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SetInputMode", "", 0, Array())


end sub

EarnestAl · January 27, 2025, 2:33am

I can’t help with macros but to remove all types of spaces, trailing, leading and internal

Select the cells
Click Edit > Find and Replace
In Find, enter \s
In Replace enter nothing, leave it blank
Tick the box Regular expressions
Click the button Replace All

For just trailing and leading spaces, the TRIM function works.

dhlocker1 · January 27, 2025, 10:20am

I could do a full regex search/replace, but wanted to develop a bit of facility with macros. Thank you.

Wanderer · January 27, 2025, 10:30am

I don’t know why the movements are not registered, but the macro-recorder is far from perfect.
.
May I suggest to record your search and replace.
Long time ago this worked for me and were the first macros I used and modified in LibreOffice.
.
PS: Please note: Recorded macros are quite different from macros wich use the api, because they mostly use dispatch-commands. So they are useful but will teach not much on “real” development of macros. There “Macros explained” by Pitonyak is still the first reference to start in BASIC.
.
PPS: Just guessing, but the macro recorder may fail to record edits inside cells. But it will usually catch the stuff you call from the menu. Would explain why search-replace is recorded but your edit not.

dhlocker1 · January 27, 2025, 10:53am

Thank you.

I’m finding that the Regex search/replace is somewhat lacking as well; it sure doesn’t behave like Emacs’s Regex S/R - inserting a LINE BREAK (newline) as \u000A inserts literal “\u000A” and is totally muddling the URLs (links) in the cells. I’m going to just go with manually editing the cells; save my programming efforts to the real-time test/control systems I usually work on. (Though I WILL check out Pitonyak; thanks for the clue.)