Ask Your Question

Calc insert column via Macro function Issue

asked 2018-04-11 18:30:52 +0200

jaM gravatar image

updated 2018-04-11 18:55:05 +0200

Ratslinger gravatar image

LibreOffice Version: (x64)
Windows 10 Pro - 1709 - build 16299.371

1) macro definition

Function inscol()
  Dim oSheet: oSheet = thisComponent.CurrentController.getActiveSheet() 
      oSheet.Columns.insertByIndex(1, 1)
End Function

2) new file created with values 'B','C','D' populated in cells B1 thru D1

3) in cell A1 "=inscol()"

4) throws error on calculation:

BASIC runtime error.
An exception occurred 
Message: .

5) file "test.ods" attachedC:\fakepath\test.ods

Please advise as to cause of error: Thank you in advance.

(minor editing for clarity - Ratslinger)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-04-11 19:13:01 +0200

Ratslinger gravatar image

Hello @jaM,

When dealing with a Function in a cell, it is to get something back to the cell with something returned from the function. In this case you are actually dealing with the Sheet - trying to add a column to it. This is done with a Sub and there is no return. Also, this would not be performed from a cell but rather initiated from a button or a toolbar. With those changes, the macro works without problem.

edit flag offensive delete link more


Thank you very much...suggestion worked perfectly 1) can you enlighten me of how to provide an acceptable format on the questions from seems the CR/LF were thrown away on the submission, but not on the input dialog. 2) for my clarity; is it correct to say "sheet operations cannot be performed from a macro" for example: "testing of the existence/non-existence of data within a cell would trigger an insert of column or row"

jaM gravatar imagejaM ( 2018-04-11 20:14:21 +0200 )edit

You're welcome. Formatting of questions - sometimes you need to add an extra return to get the proper spacing. Use the preview (displayed below your typing) to see how the display will look. Also use toolbar to get items such as code to display in proper boxes.

For you second item, let's be clear. You should avoid using macros whenever possible. I am not proficient in Calc or Writer (deal mostly with Base) but I understand it reduces the portability. Use only when no other method.

Ratslinger gravatar imageRatslinger ( 2018-04-11 20:44:50 +0200 )edit

thank you very much for your time and enlightment...

jaM gravatar imagejaM ( 2018-04-11 20:48:21 +0200 )edit

As for this particular case, it is just as easy ti right click a column & insert a new column as it is to use your macro. What to use & when to use depends entirely upon just what is being done. Your stated example in the comment may be a case for Conditional Formatting. If you question something, present what you are attempting and not how you are attempting it. You will probably get better non-macro answers.

Ratslinger gravatar imageRatslinger ( 2018-04-11 20:50:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-11 18:30:52 +0200

Seen: 227 times

Last updated: Apr 11 '18