Problem with macros

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.

1 Like

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.

1 Like

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.