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

Hi everyone,

I was creating my vocabulary log using Libre Office Spreadsheet (ver 6.0.6.2) 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.

and here is my macro code

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!

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
Else
End If
next
End Sub

edit retag 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

An error will occur in line 14 when I run it

Which is the error exact text?

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

( 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
Else
REM ReportTheConflicct
End If

( 2018-10-22 10:51:24 +0200 )edit

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

( 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 : )

( 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.).

( 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!

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

Sort by » oldest newest most voted

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(...


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

more

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

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