As per the LibreOffice Calc Guide 7.2, the support for VBA is NOT complete but covers a large portion of the common usage patterns.
I want to be able to use the
Relative referencingfeature while recording my macro.
How may I be able to do so?
1 Like
Could you clarify your question?
- Macro recording with LibO never is VBA-like. It always uses the
.uno:commands (“slots”). - The macro recorder is NOT an interaction logger. If you expect any referencing related to the current view, this won’t work. References always are fixed at record-time.
- Only commands implicitly working with the CurrentSelection (like
.uno:Copy) don’t record that selection.
Was this what you meant?
What @Zizi64 is saying is that LO macro recording is nothing like MS Excel. In Excel we regularly record snippets then rebuild those bits into our own macros either line-by-line or by converting them into VBA functions. This works because, frankly, MS Excel’s macro recorder (for all its frustrations) is an amazing bit of engineering. It really is code generation [even without the .NET CodeDOM, etc.]
In LO the macro recorder is in between code generation and just keypress replay, like “macros” of yore. The recorder takes the magic hand of the UNO system (very roughly like COM for VBA) and replays the events that occur during recording. This magic hand is called the Dispatcher–a great name to use for a new sci-fi overlord! So if you do something, basically the macro will stuff a bunch of values into a property set then just call the Dispatcher, asking the Dispatcher to replay what you did. It is not code generation in the sense of Excel’s recorder at all. And lots of times it doesn’t even work.
The good news that @Zizi64 is saying, is that you can use the UNO API from LO BASIC in a way that looks a lot like VBA’s completed code. LO BASIC will convert what you write into UNO calls in the background for you. It’s just that the LO macro recorder is worse than useless (i.e. misleading) for showing you how.
Chapter 12 of the 7.1 Calc Book is a starting place: Chapter 12 Calc Macros. There is one big trick you need to know: Almost every “big” thing you manage is just Dimmed as an Object. No “As Range” etc. However, a few things you need to manage (like sizes holding height and width, etc.) are Structs, and might be handled as a whole as a Variant, but if you need to access a value in them (mySize.Width) then you may have to track down the com.sun.star… structure name and Dim myStruct As com.sun.star… Largely I do this by using XRay or MRI. MRI is now version 1.3.4. You might search AskLO for “MRI crashes” to find out how to adjust your Calc so that doesn’t happen. These tools expose the names of the structures and objects you are using via reflection and are very powerful…sort of like “hot” versions of the VBA Object Browser. As of Calc 7.2 there is also an object browser built right into the Tools menu (called Dev Tools, I think), but it doesn’t help you ferret out things in macros. In macros instead of Debug.Print…followed by something (as in VBA), after installing XRay or MRI, you quickly look at one of the XRay/MRI modules (to load the library for your session…you can also do a UNO LoadLibrary request, but…) then use MRI myObjectIDontUnderstand or XRay myObjectIDontUndertand and up pops a formatted, complete dump of any value, Variant, Struct, Sequence (UNO sort-of array), or Object. So it’s sort of like being sent right to the VBA Object Browser but loaded with the live data from that instance of the object (hence “hot” above). MRI will even give you the option of going straight to the online API guide for that object/type.
3 Likes
Use the arrow keys for relative macro recording.
For instance:
Click cell F5
3x ← go 3 cells to the left
Strg+C copy
5x → 5 cells to the right,
Strg+V paste
2x ← 1x↓ back to column F, one row down.
1 Like
We have a wonderful explanation by @joshua4 above, and there is a great solution by @PYS (Pierre-Yves Samin?) here to a different but related question.
The solution posted in this thread is surely good advice, but leaves questions unanswered.
I also can find a few reasons for sometimes resorting to .uno:Commands as used for recorded macros, and even for the recording itself in some cases.
My “generalised” question is therefore relevant for written and for recorded, but later somehow tidied and completed macros as well:
Is there a rather simple and reusable way to get a kind of interruption during the execution of a “macro” which allows attended execution with occasional user interactions.
Since I “do not like” (means: am unable in) dialog programming or explicit usage of listeners, I looked for a solution based on a simple FormControlwith its automatisms.
Adding the usage of named ranges I got what is demonstrated in the very primitive attached example.
Interested in comments, criticism, and enhancements.
nonmodalWaitHelper.ods (56.8 KB)
1 Like
This is very interesting. It would seem to have some clear use cases. Or perhaps you could create a game of Mine Sweeper.
It might be worth mentioning as additional information that in recordedUgly (the main routine) a person can manipulate the selection cells by hand as well as letting the Dispatcher take action on them:
REM Let dispatcher do the work
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())
REM Or can manipulate selection by hand
Selection=ThisComponent.CurrentSelection
If Selection.SupportsService("com.sun.star.sheet.SheetCell") _
Or Selection.SupportsService("com.sun.star.sheet.SheetCellRange") _
Or Selection.SupportsService("com.sun.star.sheet.SheetCellRanges") Then
For Each Cell In Selection
Cell.String = "Hello"
Next
EndIf
Also, a person can use a globally named target range for all of the Continue checkboxes. That is, one can have a single global value cell and point all of the Continue checkboxes on all the sheets to that cell. For example, have Continue checkboxes data referral be set to Sheet1.A1 where that is globally named globalLinkedCell under Named Ranges. Then in recordedUgly use
linkedCell = ThisComponent.NamedRanges("globalLinkedCell").ReferredCells
instead of linkedCell = activeSheet.getRangeByName(...)
It’s still not undoubtably clear to me what the OriginalQuestioner (@StevieBlueboat) meant by the term "realtive referencing".
If it is used in the sense of “a fix number of column-steps (left or right) and a fix number of row-steps (up or down) distance from the cell that last had the focus” then the solution @Villeroy proposed in his comment is definitely what one can get with a recorded macro without subsequent editing.
If the term is used in a different sense the actual meaning must be clarified.
Without clarification, the thread can only stagger around based on guesses.
Do not RECORD your macros. WRITE your macros based on the API functions.
Why shouldn’t LO allow Relative macro recording as a GUI macro recording function?
If LibreOffice were a different software, it might be able to record a different kind of “macros”. It isn’t.
…
None of the terms “relative macro recording” or “GUI macro recording function” has a clear meaning.
What comes next to it in my understanding is what I mentioned about 2.5 years ago under number 2. in my comment above as “interaction logger”. Serious problems and disadvantages are obvious.
…
Did your research find a lot of praise tor the feature “Use Relative References” of Excel’s macro recorder (versions unknown)? Did you study related explanations?
I am still trying to learn the ‘hyperlink’ concept you introduced me to, and cobble simple variations from that.
I am trying to make a hobby of all of this, as I am getting too old to sail. (I live on an old sailing ketch that is becoming more and more like me each day… needing to retire.)
—I think I should begin with learning Basic Programming Language and am looking for some sort of introductory online course for beginners—
My objective is to get more comfortable with being somewhat confident if writing macros.
My only past experience has been with simple .BAT programs in DOS in the early-mid 80’s, and I was able to do average dBase IV programming. As things went GUI and windows based, I had to focus more on family, real life in the medical arts and their applications, using, teaching, designing and selling MRI scanners. I am now retired.
Things I do now generally are personal and church-based volunteer stuff. (As a clerk, keeping track of membership and related finances.)
If you have any recommendations, I’d welcome your suggestions.
Thanks.
Why shouldn’t LO allow Relative macro recording as a GUI macro recording function?
It is possible for some actions. But it is better (more effective) to Write your macros instead of Record them.
Here is a very simple recorded macro. It will work with the actual active cell.
sub GoDownThreeRows
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(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args2())
rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())
end sub
Thank you.
This is what I was able to cobble and glean from what you have sent me. Great exercise! But when I look at the macro code, it may as well have been written in Bahasa Melayu.
“The Word of the Day” is in red… and is the ‘match’ word.
Thanks for all of your help.
/Steve
List MacroHyperlink.ods (23.5 KB)
Ola @Zizi64 , can be summarized like this:
sub GoDownThreeRows
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(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 3 '<==== just change this value.
args1(1).Name = "Sel"
args1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args1())
end sub
2 Likes
Yes, I know it, I see it
But that is a manually MODIFIED macro now, but not a pure recorded one.
Yes, manually, removed unnecessary instructions.
The “you” is a pronoun.
In this post it will be clear to everyone who is meant by “you”, because I reasonably can only refer to your quoted and labeled comments.
If such a reference is missing, only the author can know for sure who was meant by the pronoun, unless it’s disambiguated in addition.
In this case that would look like:
“You (@StevieBlueboat) missed to clarify who was meant.”
Please regard that somebody else may have commented a post before you did.