Adding Named Range "Dynamically"

[Keith Wild]

I am trying to add a named range using the method addNewByName - see this
By "Dynamically" I mean that the range (i.e. the second parameter) is not hard coded into the method as something like "Sheet1.C3:F7", but created depending on an input. I have tried this:

Code: Select all

Sub AddNR

Dim Doc As Object, Sheet As Object, Cell As Object
Dim L As Integer
Dim Outputs As Object, Outputs0 As Object	
Dim Outputs0Address As new com.sun.star.table.CellAddress 
Dim OutputsRangeAddress As new com.sun.star.table.CellRangeAddress
Dim OutputsString As String
Dim Converter As Object	

Doc = ThisComponent
Sheet=Doc.Sheets.getByName("AddNR")
Cell = Sheet.getCellRangeByName("NN")
L=Cell.Value


Outputs0 = Sheet.getCellRangeByName("OUT0")
Outputs0Address = Outputs0.CellAddress
OutputsRangeAddress.Sheet = Outputs0.CellAddress.Sheet
OutputsRangeAddress.StartColumn = Outputs0.CellAddress.Column
OutputsRangeAddress.StartRow = Outputs0.CellAddress.Row
OutputsRangeAddress.EndColumn = Outputs0.CellAddress.Column+3
OutputsRangeAddress.EndRow = Outputs0.CellAddress.Row+L-1
Converter = Doc.createInstance("com.sun.star.table.CellRangeAddressConversion")
Converter.Address = OutputsRangeAddress
OutputsString = Converter.UserInterfaceRepresentation
Doc.NamedRanges.addNewByName("OUTPUTS", OutputsString, Outputs0Address, 0)

End Sub

but get an error message. I have a sheet called AddNR in which cell A1 is called NN and contains the integer 5, cell C3 is called OUT0 and is blank. I run the macro by stepping through it with various variables on watch and I get a RuntimeException: see the attached.

Notes:
1.The Converter comes from section 6.6 of Andrew Pitonyak's Useful Macro Information.
2. I realise I could probably do it without a Named Range, but would like to know what is happening and why it doesn't work.

[Villeroy]

You know menu:Insert>Names>Create...
You know menu:Insert>Names>Labels...

[FJCC]

Try putting OutputsRangeAddress where you currently have OutputsString in the call to addNewByName().

[Keith Wild]

Thank you for your responses.

Villeroy: Would that not need to be done each time since the range sizes will vary? The point is that I want this to operate dynamically so that the variable size ranges are generated automatically.

FJCC: Tried your suggestion and got an error: "BASIC run-time error. Incorrect property value." The API does say that the second parameter should be a string, not a com.sun.star.table.CellRangeAddress.

[B Marcelly]

Code: Select all

    Sub AddNR

    Dim Doc As Object, Sheet As Object, Cell As Object
    Dim L As Integer
    Dim Outputs0 As Object   
    Dim Outputs0Address As Object
    Dim OutputsZone As Object
    
    Doc = ThisComponent
    Sheet=Doc.Sheets.getByName("AddNR")
    Cell = Sheet.getCellRangeByName("NN")
    L=Cell.Value


    Outputs0 = Sheet.getCellRangeByName("OUT0")
    Outputs0Address = Outputs0.CellAddress
    OutputsZone = Sheet.getCellRangeByPosition(Outputs0Address.Column, Outputs0Address.Row, _
      Outputs0Address.Column+3, Outputs0Address.Row+L-1)
    if not Doc.NamedRanges.hasByName("OUTPUTS")  then
      Doc.NamedRanges.addNewByName("OUTPUTS", OutputsZone.AbsoluteName, Outputs0Address, 0)
    else
      MsgBox("OUTPUTS already exists", 16) 
    end if

    End Sub

[Villeroy]

Varying sizes of spreadsheet areas are properly handled by cell insertion. Just turn ON Tools>Options>Calc>General:"Expand references when ....". This is a global option and most users want this set.

If you want to create a new name from scratch, define one manually and inspect its properties.

[Keith Wild]

I'm afraid I must apologise.

I now think the reason my original macro didn't work was that there was already an existing range called OUTPUTS in the spreadsheet. I had thought I had deleted it using removeByName, but I had made a spelling mistake and actually had:

Code: Select all

If Doc.NamedRanges.hasByName("OUPUTS") Then
	RefCellsO = Doc.NamedRanges.getByName("OUTPUTS").getReferredCells()
	RefCellsO.clearContents (5) '5 = 1(Values) + 4 (Strings)
	Doc.NamedRanges.removeByName("OUTPUTS")
End If

So this spelling mistake (OUPUTS instead of OUTPUTS) meant that the named range OUTPUTS was not removed and this presumably caused the problem. In any event, it now works. Sorry for wasting your time, but I hope you thought it was quite interesting. BTW I assume that no-one actually tried to run the macro, since you should not have had the problem with no OUTPUTS range in your own spreadsheet.