Running several macros from another in Calc

English
,
#1

I’m new to the world of macros and have a simple problem that I cannot seem to find an answer for. I have created three separate macros that work, one called “AddNewRow”, one called “CopyCells1” and the other called “CopyCells2”. These reside in the Object Catalogue under My Macros & Dialogs/Standard. Each one contains one Sub that is called the same as the Macro name.
All I want to do is to have another Macro called “RunAll” that runs the other three, one after the other. The obvious (to me at least) way to do this would be:

Sub RunAll
	AddNewRow
	CopyCells1
	CopyCells2
end sub

Which does nothing.
Hoping someone can give me some help to resolve this.

Thanks

Complemento

Thanks for the suggestion but that doesn’t work either. I have looked through the “Go learn things” but trying to find a solution in 659 pages of something that is as clear as something that’s not very clear is not a preferred option.

Any further ideas would be most welcome.

To clarify my “New to Macros” comment, a long time back (I’m 68 so we’re talking 10-40 years or so) I wrote numerous Basic programs, numerous macros for Excel and several quite complex applications in Microsoft Access. Hence I have some basis for what to me seems obvious. I am however new to macros in LibreOffice and consider statements such as “dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)” and “dim args1(0) as new com.sun.star.beans.PropertyValue” to be the work of extra terrestrials with evil intent.

#2

Post the macros …

#3

I’m new to the world of macros…

The obvious (to me at least) way…

Really? If you are new, how can something be obvious? Go learn things. Your example has faulty syntax, it should be something as follows:

Sub RunAll
    AddNewRow()
    CopyCells1()
    CopyCells2()
end sub

Unless, of course, you need to pass arguments.

#4

Hello @SimonM, I totally agree with the mess of the Basic language, I believe that when triggering a command, for example COPY, Basic internally should trigger all the Libraries necessary for its execution, without me having to show the way.

.

Back to your problem, post the macros you made, or if you want to get in direct contact, see here: User:Schiavinatto - The Document Foundation Wiki, and send me the file, confidentiality guaranteed.

.

I’m not a programmer, but I’ve followed LibreOffice since your grandfather, StarOfice 1998.

#5

@SimonM, From what you mentioned I would do this: basic example, information is lacking to be functional.

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

Sub RunAll
	AddNewRow
	CopyCell1
	CopyCell2
End Sub

Sub AddNewRow
	GoToCell "Where"   ' Where = A1  or  A1:C3   or  Named area 
	Execute "InsertRowsBefore"
End Sub

Sub CopyCells1
	GoToCel "Where"   ' Where = A1  or  A1:C3   or  Named area 
	Execute "Copy"
End Sub

Sub CopyCells2
	GoToCel "Where"   ' Where = A1  or  A1:C3   or  Named area 
	Execute "Copy"
End Sub
#6

continuation…

''' SubMacrros
Sub GoToCel ( xLocal$ ) 
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint" : args1(0).Value = xLocal
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController _
.Frame, ".uno:GoToCell", "", 0, args1())
End Sub 

Sub Execute ( what$ )
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController _
.Frame, ".uno:" & what & "", "", 0, Array())
End Sub
#7

Show the code.

#8

Thanks for the quick reply. The first code example you give is exactly what I have done as a work around the problem. Whilst it does work well my dogged determination not to let an inanimate object beat me drives me on! The reason I wanted to have separate macros called by another is so that I can have each macro as part of a library that can be called upon as and when required.

Sorry, the continuation macro means nothing to me!

image description

AddNewRow adds a row into a sheet and works fine.
CopyCells1 copies a named range of cells into the new row and works fine.
CopyCells2 copies another named range of cells into the new row and works fine.
Module1 contains all the code from the above three macros as three subroutines with a fourth:

Sub RunAll

AddNewRow
CopyCells1
CopyCells2

end sub

This also works.

All I want to do is have this forth macro run the other three as separate macros not as sub routines within the same macro.

Module2 is just a test Module to play around with.

The solution is of course blindingly obvious and simple. Light bulb moment. In the Macro that I want to run the other three I was “calling” each Module but not telling each Module which Sub to run. Spotted this as I was assigning a macro to a form control push button!
The following code works:
Sub Main

AddNewRow		'Module to be used
	AddRow		'Sub to run

CopyCells1		'Module to be used
	Cells1		'Sub to run

CopyCells2		'Module to be used
	Cells2		'Sub to run

End Sub

#9

Your problem was that the library with three “little” macros was not loaded? Then the first operator of the “general” procedure should have been the line

GlobalScope.BasicLibraries.LoadLibrary ("Your_library")

Agree that keeping all libraries in memory even when they are not needed is a waste.

#10

@SimonM,

He said: “Sorry, the continuation macro means nothing to me!”

They are necessary to run the previous ones, GoToCel is to go somewhere and leave active, called Execute works for any comondo uno.XXXX that does not need an additional parameter. Identify 60+ commands that work.

#11

Still not working. Thanks for taking the trouble to try to help me out. I cannot believe that such a simple task as running 3 macros consecutively from a fourth “supervisory” macro can be so difficult. Surly this is one of the most important and common things a programmer would want to do in the interests of modularising a program into reusable, efficient units.

I sort of understand the need to load a library, though this really seems to be pretty daft as I would expect any decent program to default to the current location it is located in but what do I use as “Your_library”" I’ve tried several things but either get nothing or:
BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message:

I must be verging on senility but the “SubMacros” code remains a total mystery to me both in its need and function.

#12

No, no, don’t talk about senility - if you are able to write meaningful texts, then you will get the correct code, sooner or later. The link “go learn” from @gabix, which outraged you with its wording, leads to the page with Pitonyak’s books. Honestly, until I read chapter 5.29.3. The library container from the book Useful Macro Information, I also did not understand the internal structure and interrelation of libraries and modules in Basic. Now I know this, but cannot to explain, I have to write too much, the length of the comment will not allow it.

#13

Hi, @SimonM,

Post the macros, perhaps, it is a problem in it.

#14

@schiavinatto No, it seems to me that the texts of the macros will not help to find the source of the problem. Judging by the names of the procedures, this is something very simple. I think we also need a screenshot with the structure of libraries and modules, the location of these procedures.

#15

Thanks again! I’ll go away and have a look at chapter 5.29.3 as you recommend. I would add a screen shot showing the macro organiser but can’t seem to find a way to post it! Must be missing something else…

#16

@SimonM, edit your question and publish the image.
And macros too.

#17

When you read the Pitonyak book, use the search for the word BasicLibraries - information about libraries and how to access them is scattered in small portions in several places.

And yes - we really want to help and are waiting for the texts of your attempt and the image of the resulting tree.

#18

There is no “crime” in what you have just shown, everything is arranged absolutely correctly. The fact that individual procedures are placed in separate modules is an overkill, but this is a developer’s decision, you have every right to do so if it is convenient for you (it is usually more convenient to write thematically similar procedures in one module - in this case, CopyCells1 and CopyCells2 can definitely lie side by side). Library “Standard” also does not raise any objections - all libraries with this name are loaded automatically and do not require .LoadLibrary().

I have a version. Perhaps you started writing your RunAll macro in the MY MACRO TEST FILE V1 spreadsheet, then realized that you wanted to put it somewhere else, and wrote its text in “My Macros”. Please check to see if the Standard library in this book has a procedure with that name that does nothing because it doesn’t contain a single line of code.

#19

Good thought but the Object Catalogue shows Macro Test File V1 with nothing in its Standard library.

#20

Well, at least we checked. I have to agree with @schiavinatto - we need to code review of all procedures.

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