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

[Jerrison]

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!

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

[mikekaganski]

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)?

[Lupp]

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

[Jerrison]

@mikekaganski 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.

[Jerrison]

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

[Lupp]

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

[Jerrison]

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

[JohnSUN]

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)

[Jerrison]

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