Ask Your Question

problem with macros [closed]

asked 2018-03-08 20:41:15 +0200

Bo gravatar image

updated 2020-11-26 16:24:24 +0200

Alex Kemp gravatar image

I have a Calc workbook with multiple sheets a several rather simple macros for entering data into cells using inputboxes and copying selected cells to different sheets. The workbook comes from Excel, but I converted it into ODS format; macros are written in Excel VBA. Macros worked fine and in the meanwhile I've added a few sheets and decided to make new macros. After writing a new macro I found a problem: data are copied to different sheet than the one defined in the macro for pasting. Later I found that even the command


activated a different sheet than 12. What do I have wrong? Tested in LO v5.4.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-04-26 14:04:53.219667

1 Answer

Sort by » oldest newest most voted

answered 2018-03-08 23:04:57 +0200

Lupp gravatar image

updated 2018-03-08 23:15:59 +0200

Calc does not speak Excelian. There are some cases where you can get a module from Excel-VBA to work in Calc if you add a line Option VBAsupport 1 above all he other lines of code. And code exclusively doing calculations without any direct access to objects will mostly work, but ...

In LibreOffice BASIC is only one of the languages usable for added code, and therefor only very few objects and methods are directly introduced to BASIC as named entities. All the rest is done via interfaces and services which can also be accessed by a different programming system if a respective bridge is supplied.

A few remarks: ThisComponent.Sheets("12") is not the sheet with the name "12". (BTW: Don't use numbers as names.) The Sheets() property of the spreadsheet document expects a number enclosed between the parentheses for the shortcut access to a sheet. If there is one, ok. If there is a string an automatic conversion is attempted. In your case it is successful and the sheet with the internal number 12 is returned. Internal numbers of indexed properties start, however, with 0 in Calc. Therefor you get the 13th sheet in the order from left to right speaking of the tabs independent of the name.

You may use ThisComponent.Sheets.GetByName("12") to get what you wanted.
To activate a different sheet apart from VBAsupport you need to call the method SetActiveSheet() of the object ThisComponent.CurrentController ...

Of course this may sound strange to you - and in fact the concept of interface-oriented programming needs approaches you will not be familiar with.

Therefor: Start using Calc without any "macros" (or only with very few simple ones). From my point of view the continued encouragement to use macros with Excel is mainly a means to aggravate compatibility and a strategy to enforce user lock-in. Spreadsheets should work as spreadsheets: Find a new equilibrium based on formulas if some content was changed. Don't push contents elsewhere, but reference them from where you need them. If you actually want to use "input boxes": I rarely had use for these toys, but if I wanted one I simply used a 'Formatted Field' or a 'Text Box' control which both have a property 'Linked Cell' and to the cell given there by its address the entered value goes. No macro needed!

If you insist on learning how to pprogram for Calc in BASIC, start with the guide and with the famous documents by Andrew Pitonyak.

edit flag offensive delete link more


Thank you for your answer. I started the workbook as something I later found was practically identical to GTD methodology, where some kind of automation via macros is benefitial. The main problem is inconsistency: some things work OK, but others not... I'll have to delve into it to anyway. Thank you for the links. The problem is that Excel VBA, which I'm used to, is straigtforward, concise and transparent (more or less), and LO BASIC it the opposite at the first sight.

Bo gravatar imageBo ( 2018-03-14 22:14:56 +0200 )edit

Question Tools

1 follower


Asked: 2018-03-08 20:41:15 +0200

Seen: 1,497 times

Last updated: Mar 14 '18