LibreOffice Calc Macro Question

[br4]

I have a workbook created in Excel that I've been using for a long time without any problems. In attempting to run a macro in Calc (first time in Calc), I'm getting the following error message:

BASIC runtime error.
'91'
Object variable not set.

It points to line 16 of the code:

8 Sheets("avgs").Select
9 Rows("3:3").Select
10 Selection.Insert Shift:=xlDown
11 Range("A4").Copy
12 Range("A3").Select
13 ActiveSheet.Paste
14 Dim rngC As Range
15 For Each rngC In Range("A3")
16 rngC = DateSerial(Year(rngC), Month(rngC) + 1, Day(rngC))
17 Next

Any ideas how to get this to run in Calc?

Also, is there a way to get the list of the workbook's macros to show immediately, without having to dive into the folders to find them?

 

[Barman58]

Just from a generic coding basis, (not used Libreoffice other than basic look at it)

Line 16 does show a number of spaces in the formula - 16 rngC»=»DateSerial(Year(rngC),»Month(rngC)»+»1,»Day(rngC))

Try removing those spaces that I have marked »

Some coding languages will ignore extraneous spaces, others will not which could be the case here

It could also be that the syntax for the line 14 Dim rngC As Range is incorrect or must be declared at the start of the code (some coding languages are a little pedantic), but this is not obvious as the variable rngC is used in line 15 without throwing an error

 

[br4]

Thanks for the reply.

I removed the spaces as you noted but that didn't work either. My workbook has two macros, one much simpler than the other. I re-recorded the simpler one and am amazed at the difference in code between Calc's VBA code and Excel's code. I would have thought that, with minor differences, the two sets of VBA codes would be very similar. That doesn't seem to be the case. Looks like I'll have to recreate each macro.

Does anyone know of a converter for Excel VBA to Calc VBA?

 

[Bree]

Does anyone know of a converter for Excel VBA to Calc VBA?

Some suggestions here: excel - How do I make vba code compatible with libre office - Stack Overflow

 

[br4]

Considering how complicated the VBA macro code is in LibreOffice, I've decided to stay with MS Office, at least for now. I'm very much surprised that its coding is not more closely aligned with that of MS Office.

Thanks for your replies.

 

[Barman58]

VBA is I believe still copywrite software, so it could be that companies / groups or individuals that are not able to licence VBA for use in their free software, would turn to one of the open source "BASIC" type languages to perform programmed tasks in their office suites

 

[GoldenDog]

The easiest way to fix LibreOffice problems is to switch to OpenOffice

 

[br4]

Thanks for the tip. I'll give Open Office a try and see how it compares.

 

[Bree]

Thanks for the tip. I'll give Open Office a try and see how it compares.

The two are almost identical, in fact LibreOffice is a fork of OpenOffce when it looked like OpenOffice was about to die off. That didn't happen, but to this day OpenOffice is in less active development than LibreOffice, tending to lag behind in both features and support.

One of the biggest differences between Apache OpenOffice and LibreOffice is the frequency of releases. LibreOffice is updated much more frequently than Apache OpenOffice, which means you'll receive new features and bug fixes more quickly...

...Although both LibreOffice and Apache OpenOffice can open and edit native Microsoft formats DOCX and XLSX, only LibreOffice is able to save to these formats.

Libreoffice vs OpenOffice | TechRadar

 

[br4]

A follow up...

I tried OpenOffice and was not impressed. I think I'd go back to LibreOffice vs OpenOffice, if I decide to abandon MS Office.

Getting back to macros, one more question if I may: In Excel, when I select the 'view macro' option, the resulting Macro box shows all the macros. In LibreOffice, I have to hunt for them. Is there a way to ensure that all the workbook macros are in the same place and the 'run macro' selection goes to that place?