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?
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
Do not RECORD your macros. WRITE your macros based on the API functions.