Ask Your Question

[Solved][Calc] Having trouble adding range names using LibreOffice Basic Macro [closed]

asked 2018-10-22 04:33:16 +0200

Jerrison gravatar image

updated 2018-10-23 02:08:01 +0200

Hi everyone,

I was creating my vocabulary log using Libre Office Spreadsheet (ver recently, and now I have several sheets with about 50 vocabulary in each of them. I would like to create a range name for each of the word using macro. For example, I would like to add a range name "apotheosis" for the range $2:$2, and "arch" for the range $3:$3... etc.

image description

and here is my macro code

image description

An error will occur in line 14 when I run it, can someone tell me what the last half 2 arguments in the addNewByName parentheses mean? or what am I missing? (I've tried the method on this page, but it doesn't work. I also read the official guide too, but it doesn't work either)

Thanks a lot!

===Updates 2018.10.22===

I'm really grateful for the quick reply from everyone. The following is the revised version:

Sub Main
Dim Doc, MySheet, MyRange, TheRange, Cel as Object
Dim i as integer

Doc = ThisComponent
MySheet = Doc.Sheets(14)
TheRange = Doc.NamedRanges

For i = 1 to 51 
    Cel = MySheet.getCellByPosition(0,i)
    MyRange = MySheet.rows(i)
    If NOT TheRange.hasByName(Cel.string) Then
        TheRange.addNewByName(Cel.string, MyRange.absoluteName, Cel.CellAddress, 0)
    End If
End Sub
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-10-24 20:54:57.695466


Please provide missing information:

An error will occur in line 14 when I run it

Which is the error exact text?

I've tried the method on this page, but it doesn't work

How did you try it, and in what way doesn't it work (with messages)?

Mike Kaganski gravatar imageMike Kaganski ( 2018-10-22 07:22:32 +0200 )edit

I would judge it a really absurd idea to post Basic code as an image. What's the intention?
Basically equivalent code is running for me as expected.
Of course, I would include something like:

If NOT ThisComponent.NamedRanges.HasByName(Cel.String) Then  
  REM CreateTheRange  
  REM ReportTheConflicct  
End If
Lupp gravatar imageLupp ( 2018-10-22 10:51:24 +0200 )edit

@Mike Kaganski the exact text is as following: BASIC runtime error. An exeption occurred Type: Message:. The way I tried is substituting the third argument and the fourth, which I've found several different versions of.

Jerrison gravatar imageJerrison ( 2018-10-22 21:22:11 +0200 )edit

@Lupp Sorry I'm new here, and I didn't know how to post code here. And Thank you! it works : )

Jerrison gravatar imageJerrison ( 2018-10-22 21:24:16 +0200 )edit

I posted probably too harsh. Sorry!
One remark in addition: Apart from ThisComponent.NamedRanges there exists a sheet.NamedRanges container for each sheet. This way the objects are seperated concerning their scope. (Document or Sheet.).

Lupp gravatar imageLupp ( 2018-10-22 22:48:08 +0200 )edit

Haha, don't worry about that. : ) I would probably keep posting image if no one pointed that out. And thanks for the information!

Jerrison gravatar imageJerrison ( 2018-10-23 01:58:38 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-10-22 07:59:30 +0200

JohnSUN gravatar image

In your case, you need to look in the direction of the two most common mistakes: the named range "apotheosis" already exists (it was created when the macro was successfully run last time) or in cell A2 it is written "apotheosis " (the name contains a space or other invalid characters). In the first case, add to the line for adding a named range a condition

If Not TheRange.hasByName(Cel.string) Then TheRange.addNewByName(...

In the second case, the Trim () function can help you.

The third error is that the word in the cell matches with the possible cell address of the sheet (for example, ALI2)

edit flag offensive delete link more


Thank you so much~! It works! It seems to be that it already exist when it was successfully executed last time.

Jerrison gravatar imageJerrison ( 2018-10-22 21:31:12 +0200 )edit

Question Tools

1 follower


Asked: 2018-10-22 04:33:16 +0200

Seen: 319 times

Last updated: Oct 23 '18