Calc insert column via Macro function Issue

[jaM2]

LibreOffice Version: 5.4.6.2 (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
   Type: com.sun.star.uno.RuntimeException
   Message: .

5. file “test.ods” attachedtest.ods

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

(minor editing for clarity - Ratslinger)

[Ratslinger]

Hello @jaM2,

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.

[jaM2]

Thank you very much…suggestion worked perfectly

1. can you enlighten me of how to provide an acceptable format on the questions from windows…it 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”

[Ratslinger]

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.

[jaM2]

thank you very much for your time and enlightment…

[Ratslinger]

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.