My third experience with OpenOffice V3 Macros

After my first expereince with OpenOffice V3 Macro has been a complete failure I investigated a little further and made a second experience: here I learned that the sample macros provided do work – but only for text documents, not for spreadsheets.

I still have been working on trying to get one of my simple sample MS Excel macros to work with OpenOffice V3. Both applications – and if we include Lotus Symphony there would be three – support BASIC as a language to write macros. The challenge lies in the different object models used by these applications how to access services and how to access documents and parts of a document like sheets or cells in a spreadsheet.

Let me explain first what my MS Excel macro is supposed to do. Basically it is supposed to convert something like this shown here on the left side to something like this shown here on the right side:

Imagine you receive a spreadsheet in which some columns are used for category or grouping values and to make it better readable the value is only present when it changes. This is nice unless you want to sort this spreadsheet by the first column which would create an incorrect output or unless you like to use that spreadsheet as an input for a database import which would cause problems due to missing values in the first column.

Here is how I solved this in MS Excel by writing this VBA macro:

   1: Sub CompleteCategoryRow()

   2: Dim c As Long

   3: Dim r As Long

   4: Dim contents As String

   5: If Selection.Rows.Count <= 1 Then

   6: MsgBox "Nothing selected.", vbOKOnly

   7: End If

   8: contents = " "

   9: For c = 1 To Selection.Columns.Count

  10: For r = 1 To Selection.Rows.Count

  11: If Selection.Cells(r, c) <> "" And Selection.Cells(r, c) <> " " Then

  12: contents = Selection.Cells(r, c)

  13: Else

  14: Selection.Cells(r, c) = contents

  15: End If

  16: Next r

  17: Next c

  18: End Sub

Little side comment: to still get a better readability we could add the following line right after the Else statement’: Selection.Cells(r, c).Font.Color = RGB(255, 255, 255). This would still add the contents but display it with white color. So as long as your background color is white you wouldn’t see it.

Trying to run my MS Excel macro in OpenOffice V3 simply would generate an error as shown below:

Since there is no easy way to convert those macros from MS Excel to OpenOffice I noticed this subject is going to become more complicated and I started to use a different approach in solving the problem: reading. Reading books is supposed to make you smart, isn’t it ? Somewhere I found this book being recommended: “Learn OpenOffice.org Spreadsheet Macro Programming: OOoBasic and Calc Automation: A Fast and Friendly Tutorial to Writing Macros and Spreadsheet Applications” by Mark Alexander Bain.

Chapter 1 on the book describes how to work with the IDE of OpenOffice, chapter 2 how macros are organized – in libraries, modules, sub routines and functions – and chapter 3 is about the OpenOffce Object Model. I found this chapter quiet hard to digest. It introduces UNOs—OpenOffice.org’s Universal Network Objects —and then it is about clients, interfaces, methods and services, thinks I actually did not really want to read about before being enabled to come up with my first OpenOffice macro.

In Chapter 4 it is getting more practical and the first working macro is shown in sub chapter “Manipulating Spreadsheet Cells”  – not a “Hello World” one, but one which opens up a blank spreadsheet and displays current date and time in the first cell of this sheet.

The follow-on chapters deal with subjects I might be interested in at a later time, like formatting sheets, working with databases, working with other documents, developing dialogs and creating a complete application. Chapter 10 sounds interesting and is about Using Excel VBA in OpenOffice, something for me to read in more detail soon. It mentions that there are plans that a future version of OpenOffice ( written after the book has been published, so may be this is V 3 already ? ) might contain VBA support.

The sample macro from chapter 4 was a good start for me but did not resolve major questions I have had at that point, like especially

• how to work on the actual spreadsheet already opened,
• and how to access a range in that spreadsheet currently selected.

The book also contains a link to the OpenOffice API documentation site and some hints how to use that. I googled through dozen of pages in the internet to find a way how to access a selected range in the current active sheet. I finally learned about the getCurrenSelection function in the XModel interface, but it turned out that this API documentation is insufficient: no examples provided how to use it, even no details about what type of object this function would return.

After more googling I was almost ready to give up for now until I got the idea to do what I sometimes do when there seems to be no information available about a particular program: I debug it. The need to start debugging is an indicator that knowledge management has failed so far in providing to you the knowledge you need. Thus: time to find out on your own and may be contributing to the incomplete body of knowledge out there in this particular realm.

The OpenOffice IDE allows to run a macro step by step and to add watch points. Using that feature I explored the object returned by the  getCurrenSelection function and saw that it provides an attribute DataArray giving access to all the cells in a selected range.

With that previously missing piece of knowledge discovered through code debugging I was able to finally come up with this OpenOffice V3 macro BASIC code supposed to do exactly the same thing than my MS Excel macro:

   1: Sub CompleteCategoryRow

   2: xSelection = thisComponent.getCurrentSelection()

   3: If xselection.Rows.Count <= 1 Then

   4:     MsgBox "Nothing selected."

   5: End If

   6: contents = " "

   7: Data() = xselection.getDataArray()

   8: For c = 1 To xselection.Columns.Count

   9:     For r = 1 To xselection.Rows.Count

  10:         If Data(r-1)(c-1) <> "" And Data(r-1)(c-1) <> " " Then

  11:             contents = Data(r-1)(c-1)

  12:         Else

  13:             Data(r-1)(c-1) = contents

  14:         End If

  15:     Next r

  16: Next c

  17: xselection.setDataArray(Data())

  18: End Sub

And, Eureka!, it works ! My first OpenOffice V3 Spreadsheet macro is running, as you can see in this screen cast!

Update:

Pure VBA code works as well in OpenOffice V 3 ! Simply adding “Option VBASupport 1” to the original MS Excel VBA code does the trick, as I learned from chapter 10 of the book I mentioned above. Thus: OpenOffice V 3 includes Microsoft VBA support ! The code shown below works as well and does exactly the same thing than the code I showed above:

   1: Option VBASupport 1

   2:

   3: Sub CompleteCategoryRowVBA

   4: Dim c As Long

   5: Dim r As Long

   6: Dim contents As String

   7: If selection.Rows.Count <= 1 Then

   8:     MsgBox "Nothing selected.", vbOKOnly

   9: End If

  10: contents = " "

  11: For c = 1 To selection.Columns.Count

  12:     For r = 1 To selection.Rows.Count

  13:         If selection.Cells(r, c) <> "" And selection.Cells(r, c) <> " " Then

  14:             contents = selection.Cells(r, c)

  15:         Else

  16:             selection.Cells(r, c) = contents

  17:         End If

  18:     Next r

  19: Next c

  20: End Sub

Intelligent people are bad

Yesterday I finished reading "Lean Brain Management" by Gunter Dueck.  A book about the future which already has begun. A future we either might not want to live in if we don’t change what we are today, or a future which will change us into some(one)thing different. From the very last sentence of the book I conclude that even the author does not want to live in that world he is describing.

Two key messages from the book:

Let’s produce more fakes. They are cheaper to make and usually better than the orignal. We don’t need intelligent people anymore. We put all the intelligence into our business processes and systems.

My friend and his family visited us on Saturday. His kids are the best example: they definitely prefer some artificial food over any what my wife and I would call real food. Once my wife made mashed potatoes out of potatoes, you know, the real stuff with milk added where you really can taste the real potatoes in it. They didn’t like it. They are used to the artificial mashed potatoes you can buy in the super market, this yellow powder to which you add some water and then you get what is sold as mashed potatoes. A mashed potatoes fake. Everyone can prepare it, and obviously ( for most people ) it is even better than what you could produce on your own with real ingredients and a lot of effort. Fakes are the future !

And fakes are easy to produce. The procedure printed on the back of the package is easy to follow, a four-year-old could do it: put the powder in a bowl, add water, stir, done, enjoy. Simple steps, no training is needed to execute those. This principle needs to be applied to our entire life and to the business of every company. Business processes are designed in such a smart way that no smart people are needed anymore, except the few who would design those business processes and those intelligent systems behind. We have to achieve the ultimate level of specialism.

We don’t need IT architects anymore who know the entire portfolio of our and partners and competitors products. In the future you might become an expert how to install DB/2 version 9.5 on an AIX 6.1 machine. That’s it. Your training will take 4 hours and then you ( or everyone else ) can do the job. We don’t need skilled software developer who “speak” a lot of programming languages and can do software design as well, and testing and documentation of course. In the future programming is not needed anyway, you just sick a view prepared components together and that’s it. Or you become the expert for “for”-loops in Perl. Whenever a “for”-loop is needed somewhere in some perl code they call you. We don’t need project manager anymore who need month and years of training and expertise. We might not do projects anyway anymore, but if we do you might be the expert on how to fill out a change request and they will call you into the project if a change request needs to be filled out.

Intelligent people are bad. They make things complicated and cost a lot of money. To be competitive we have to get rid of them.

I don’t know whether Gunter Dueck has written a book about SOA without knowing it. At least I can not recall that I have seen that acronym anywhere in the book. Wouldn’t SOA be the perfect platform to achieve Lean Brain Management (LBM) ? The more we take out those steps in a business process requiring human intervention, or the more we simplify those, the closer we get to the LBM world. And if we built all smarts into the business process like services disocvering each other automatically and connecting in the right way to execute the business process, or like a SOA Supervisor who keeps track of everything and makes corrections when needed, then we are almost there !

Are we there yet or even close ? The author has his doubts, and I have my doubts too, especially after I have just seen at the end of last quarter when I had to work over the weekend and stay in a manufacturing site in Hungary to help supporting their processes and logistic systems  how many intelligent people have to stand by during those critical days to ensure everything goes smooth and to correct all the unforeseen situations and complicated problems coming up.

LBM can be applied everywhere in our life and the second part of the books describes how LBM can be ( or already is ?) applied to military, science, sex, health, psychology, laws, religion, politics, and management of course.

May be human beings will always fight against LBM. May be it is against our nature. May be we are not willing to give up our brain and what we can do with it. But wouldn’t it bee the smartest thing to do to use our brain to finally get rid of it ?

“Britannica and I”

“Britannica & ich” is the book I am currently reading. Original title: The “Know-It-All“. It is about A. J. Jacobs who tries to become the smartest man on world and starts reading the entire Britannica: 33.000 pages, 65.000 articles , 24.000 illustrations, 32 books with a weight of 2 kilogramm per book, 44 million words.
This endeavour takes him 15 month of his life. He forces himself to read everything whether he is interested in or not and to stay in the strict alphabetic sequence the Britannica is organized after.
While he also tries to memorize everything he soon realizes that even with this immense knowledge ( called intelligence in this German translation of the book; I actually don’t agree that much knowledge and intelligence are the same thing ) he fails in

• winning a crossword competition because he did not focus on learning place names with only 2 letters or nouns with four letters and many vowels. And because of the irritating cough of the woman sitting next to him.
• being chosen for “Who wants to be a millionaire” since he simply fails the casting procedure for this TV show, may be because he might have the wrong face. ( May be he still will make it, I am just at letter “S” in the book )
• inflating an air mattress when friends come to visit his place

And his wife starts calling herself a “encyclopedia widow”.
The book is written in a very humorous way, but discusses also some of the articles he is going through in Britannica, thus the reader learns a small subset of what A. J. Jacobs is learning and starts feeling smart as well.

Let’s see: Wikipedia has 1.841.762 articles today ( in English language, only 600.075 in German language ). Being optimistic and assuming I would life 40 more years I would have to read 126 articles per day. According to the alphabetic index of Wikipedia I actually would have to start with an article about the number “0” ( wow, this is a long article already !), followed by an article about the national emergency number in Australia ( this article is not so long ). Since Wikipedia also deals with acronyms under “Aa” I would have to start with an article about Argentina’s largest airline (Aerolíneas Argentinas, quiet comprehensive article ), followed by an article about the largest airline in the world in terms of passenger-miles transported ( American Airlines ; a very comprehensive article), followed by Air America, an airline operated by the CIA, followed by the first non-airline article about an American railroad (Ann Arbor Railroad ). It would continue like this until “Aaron Fox“, the last entry under “Aa” about a music professor and guitarist ( this one is real short, only some seconds needed to read it ), would continue with “Ab” (Ab is a Dutch name, but does not have an extra article; first article here is about a steam locomotive class built for New Zealand ), to “AZ Village” ( actually Arizona Village in Arizona ), the last entry under “Az”, now followed by “A ” entries like “A & C Black“, a British book publishing company, to “Bank of America (BA)”, first article under “B”, and so on, until I would reach “zZz“, a Dutch band from Amsterdam, which is not really the last one. It is followed by “Z“, the atomic number, also known as the number of protons found in the nucleus of an atom, followed by some more articles starting with “Z “, until I finally would reach “Zadruga” ( a type of rural community historically common among South Slavics people ) and hopefully still would be alive ( without really understanding why “Zadruga” comes after “zZz” ).

Anyhow, this endeavour would have to work against a moving target. How many articles will be there in Wikipedia in the year 2047 ? And how would I keep track on what has been added after I went through a particular letter ?

I guess I better keep my fingers away from this adventure and better focus on playing squash as long as I can and may be golf a little later.