Ask Your Question
0

Running several macros from another in Calc [closed]

asked 2021-02-07 15:33:32 +0200

SimonM gravatar image

updated 2021-02-11 16:16:43 +0200

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.

edit retag flag offensive reopen merge delete

Closed for the following reason duplicate question by SimonM
close date 2021-02-13 16:41:06.636086

Comments

Post the macros ...

Schiavinatto gravatar imageSchiavinatto ( 2021-02-08 01:29:19 +0200 )edit

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.

gabix gravatar imagegabix ( 2021-02-08 07:20:28 +0200 )edit

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: https://wiki.documentfoundation.org/U..., and send me the file, confidentiality guaranteed.

.

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

Schiavinatto gravatar imageSchiavinatto ( 2021-02-09 12:11:40 +0200 )edit

@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
Schiavinatto gravatar imageSchiavinatto ( 2021-02-09 12:38:25 +0200 )edit

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
Schiavinatto gravatar imageSchiavinatto ( 2021-02-09 12:39:18 +0200 )edit

Show the code.

gabix gravatar imagegabix ( 2021-02-09 12:40:25 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2021-02-09 15:14:36 +0200

SimonM gravatar image

updated 2021-02-13 16:38:17 +0200

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

edit flag offensive delete link more

Comments

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.

JohnSUN gravatar imageJohnSUN ( 2021-02-09 15:30:31 +0200 )edit

@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.

Schiavinatto gravatar imageSchiavinatto ( 2021-02-09 15:49:03 +0200 )edit

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.

SimonM gravatar imageSimonM ( 2021-02-10 11:08:07 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2021-02-10 11:49:55 +0200 )edit

Hi, @SimonM,

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

Schiavinatto gravatar imageSchiavinatto ( 2021-02-10 12:13:13 +0200 )edit

@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.

JohnSUN gravatar imageJohnSUN ( 2021-02-10 13:06:07 +0200 )edit

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..

SimonM gravatar imageSimonM ( 2021-02-11 11:29:47 +0200 )edit

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

Schiavinatto gravatar imageSchiavinatto ( 2021-02-11 11:48:59 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2021-02-11 12:08:23 +0200 )edit

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 ...(more)

JohnSUN gravatar imageJohnSUN ( 2021-02-11 17:41:28 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2021-02-07 15:33:32 +0200

Seen: 94 times

Last updated: Feb 13