Calc macro tutorial

users
#1

I want to automate a Calc spreadsheet to do some data analysis. I need
some help finding a complete tutorial on Calc macros. Looking for
something like the books published for Excell.

I read the documentation, but it says user created macros are beyond the
scope of the documentation. REALLY! What the heck is the documentation
for then?

Tried the chat, wants to know what channel. What the heck is that?

Tried the web version of this mail group, but it would not accept the
catcha code. I'm growing frustrated and very angry. This is supposed
to be user support?

Dave,

#2

Dave, I am copying you directly since I do not know if the email client I am currently using will copy to the list; sorry about that, but I thought you might like my response faster than several hours from now when I am using my standard email client.

I want to automate a Calc spreadsheet to do some data analysis. I need
some help finding a complete tutorial on Calc macros. Looking for
something like the books published for Excell.

Not familiar with them

I read the documentation, but it says user created macros are beyond the
scope of the documentation. REALLY! What the heck is the documentation
for then?

Done by volunteers and the documentation at which you looked was beyond their scope.

http://www.odfauthors.org/libreoffice/english/calc-guide/published-lo-4.1

Chapter 12 specifically says "Macros"
http://www.odfauthors.org/libreoffice/english/calc-guide/published-lo-4.1/cg4-1-ch12-calc-macros/view

Chapter 13 contains numerous macro tutorials and examples (well, it did when I wrote the first draft some years back)
http://www.odfauthors.org/libreoffice/english/calc-guide/published-lo-4.1/cg4-1-ch13-calc-as-simple-database/view

Or, try here:
http://www.pitonyak.org/oo.php
This site contains numerous documents. You might want to look at these two documents. The first is a book and the second is more of a list of macros that accomplish certain tasks. It is kind of a roughly sorted brain dump.

http://www.pitonyak.org/OOME_3_0.odt
http://www.pitonyak.org/AndrewMacro.odt

Tried the chat, wants to know what channel. What the heck is that?

No idea.

Tried the web version of this mail group, but it would not accept the
catcha code.

Hate those!

I'm growing frustrated and very angry.

Wait until you try to write macros..... I mean, ummm, sorry you are frustrated and angry.

LO Macros are very powerful in that they directly use the internal object structure and it is easy to add support for other languages. The disadvantage is that it has a steep learning curve, especially if you do not already understand software development. I usually recommend that people begin with an example that is close to what they need. The general language structure is essentially identical, but, for the most part, document manipulation methods is very different.

This is supposed to be user support?

It is community support. You did not pay for the product and the people who will now try to help you are all volunteers; they do it because they like to help people as part of a community.

If you do not care for email type support, be sure to check out this forum

https://forum.openoffice.org/en

They have some very competent Macro people hanging out in the macro forums.

Best of luck!

Dave,
--
  dave boland
  dboland9@fastmail.fm

Andrew Pitonyak

#3

Andrew,

Thanks for the note.  I actually do software development (Java right
now) and enjoy reading documentation (no, not crazy - I've been tested).
But when I read a document called "Getting Started With Macros", I
reasonably expect it to cover the basics of how to create a macro from
scratch - like I would with a VB program (did that for 6 years).  I was
disappointed to see that it was about using the macro recorder, which
will not work for me.

I get that the authors are volunteers, and that generally the do a
fantastic job.  However, in this area, not so much.

I've decided that in the short term Calc (and Base) will not fit my
needs (documentation, support concerns) and will move the data to
Excel/Access.  Not what I wan to do, and not what LO community should
like, but it seems like the best course of action for this project.

Dave,

#4

For most users, the recorder is a decent solution.

As for support,
do not expect that from MS unless you have a credit card. I found some
bugs in their C++ compiler about 15 years ago. 5 to 10 years later I
thought for sure they would have fixed them. Nope, I had to work around
them yet again. It had to do with their streaming classes. There was a
failure when the <CR><LF> boundary was split by a buffer read for some
built-in text reading routines. Not like I could just call MS and get
support.

For sure they have a better object layer for manipulating
documents. Not even close. I have never looked at their Macro
programming resources, but, if possible, be certain to use the .NET
classes since VBA is still littered with bugs. Last time I wrote a VBA
application, half my code was related to working around bugs in their
language implementation. These bugs initially existed in OpenOffice.org,
but were fixed years ago; for example, bugs related to determining an
arrays size in certain circumstances. I had to write special routines to
deal with these issues. Moving to the .NET versions made things really
nice, however, with full access to the .NET container classes.

If you
have no particular need to run where MSO is not supported, not a bad
move. Good luck on your project.

Andrew,

Thanks for the note. I actually do software

development (Java right now) and enjoy reading documentation (no, not
crazy - I've been tested). But when I read a document called "Getting
Started With Macros", I reasonably expect it to cover the basics of how
to create a macro from scratch - like I would with a VB program (did
that for 6 years). I was disappointed to see that it was about using the
macro recorder, which will not work for me.

I get that the authors

are volunteers, and that generally the do a fantastic job. However, in
this area, not so much.

I've decided that in the short term Calc

(and Base) will not fit my needs (documentation, support concerns) and
will move the data to Excel/Access. Not what I wan to do, and not what
LO community should like, but it seems like the best course of action
for this project.

Dave,

Dave, I am copying you directly since I do

not know if the email client I am currently using will copy to the list;
sorry about that, but I thought you might like my response faster than
several hours from now when I am using my standard email client.

I want to automate a

Calc spreadsheet to do some data analysis. I need some help finding a
complete tutorial on Calc macros. Looking for something like the books
published for Excell.

Not familiar with them

I read the

documentation, but it says user created macros are beyond the scope of
the documentation. REALLY! What the heck is the documentation for
then?

Done by volunteers and the documentation at which you

looked was beyond their scope.

http://www.odfauthors.org/libreoffice/english/calc-guide/published-lo-4.1
[1]

Chapter 12 specifically says "Macros"

http://www.odfauthors.org/libreoffice/english/calc-guide/published-lo-4.1/cg4-1-ch12-calc-macros/view
[2]

Chapter 13 contains numerous macro tutorials and examples

(well, it did when I wrote the first draft some years back)
http://www.odfauthors.org/libreoffice/english/calc-guide/published-lo-4.1/cg4-1-ch13-calc-as-simple-database/view
[3]

Or, try here: http://www.pitonyak.org/oo.php [4] This site

contains numerous documents. You might want to look at these two
documents. The first is a book and the second is more of a list of
macros that accomplish certain tasks. It is kind of a roughly sorted
brain dump.

http://www.pitonyak.org/OOME_3_0.odt [5]

http://www.pitonyak.org/AndrewMacro.odt [6]

Tri

ote

type="cite" style="padding-left:5px; border-left:#1010ff 2px solid;
margi

idth:100%">

Tried the web version of this mail group,

but it would not accept the catcha code.

Hate those!

--

dave boland

dboland9@fastmail.fm

--
http://www.fastmail.com

- Access all of your messages and folders

wherever you are

Links:

#5

For most users, the recorder is a decent solution.

As for support, do not expect that from MS unless you have a credit
card. I found some bugs in their C++ compiler about 15 years ago. 5 to
10 years later I thought for sure they would have fixed them. Nope, I
had to work around them yet again. It had to do with their streaming
classes. There was a failure when the <CR><LF> boundary was split by a
buffer read for some built-in text reading routines. Not like I could
just call MS and get support.

I agree that their support also has issues, but the good thing is the
number of printed resources available.  Did I mention I like to read
documentation?

A number of years ago I completed a very large VB6 project.  A month
later MS announced the end of VB as we knew it.  That is when I decided
to go with open source where ever possible.  I still feel that way, but
sometimes it just does not work out.

#6

Tried the chat, wants to know what channel. What the heck is that?

I wonder if you mean

*IRC (live chat)*

Join us on IRC at freenode.net <http://webchat.freenode.net/>. The #libreoffice
channel <irc://chat.freenode.net/libreoffice> is for user support and
general discussion. (If you're a coder and want to get involved in
development <http://www.libreoffice.org/developers/>, join #libreoffice-dev
<irc://chat.freenode.net/libreoffice-dev>.)

Howard.

#7

Dave Boland wrote:

I read the documentation, but it says user created macros are beyond the

scope of the documentation. REALLY!

Macros, as described in books such as _Six Sigma Statistics with Excel
and Minitab_ by Issa Bass (ISBN 0-07-154268-X) or _Spreadsheet Modelling
in Corporate Finance_ by Craig W Holden, is a somewhat specialized topic.

With LibO, one can legitimately treat books such as _Automated Data
Collection with R_ by Simon Munzert et al (ISBN 9781118834817) as the
extended documentation for LibO, precisely because one can use R as the
preferred macro language for LibO.

On second thoughts, Munzert's book isn't an appropriate example, because
it is about web scraping. On the gripping hand, extensions such as SMF
are, for all practical purposes, webscrapers.

Point is, with LibO, if you think that APL is the perfect macro
programming language, go for it.

What the heck is the documentation for then?

For people to learn what functionality is available.

Should the documentation team be creating the LibreOffice equivalent of
_Credit Risk Modeling using Excel_ by Gunter Loeffler, or _Quantitative
Models for Performance Evaluastions and Benchmarking_ by Joe Zhu?

Or would it be more suitable for the team to leave an individual or
organization to pursue the goal of "Transform finance education to be
based on LibreOffice"?
(Slogan is a deliberate misquote of Craig Holden's organization
SpreadSheet Modelling.
(Now wondering if he'd put an individual who created all of the
spreadsheets in _Excel Modelling in Corporate Finance_ using
LibreOffice, on his Honour Roll.))

jonathon