Page 1 of 1
Adding Named Range "Dynamically"
Posted: Thu Jul 02, 2015 1:30 pm
by 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.
Re: Adding Named Range "Dynamically"
Posted: Thu Jul 02, 2015 1:55 pm
by Villeroy
You know menu:Insert>Names>Create...
You know menu:Insert>Names>Labels...
Re: Adding Named Range "Dynamically"
Posted: Thu Jul 02, 2015 2:11 pm
by FJCC
Try putting OutputsRangeAddress where you currently have OutputsString in the call to addNewByName().
Re: Adding Named Range "Dynamically"
Posted: Thu Jul 02, 2015 8:32 pm
by 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.
Re: Adding Named Range "Dynamically"
Posted: Thu Jul 02, 2015 10:44 pm
by 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
Re: Adding Named Range "Dynamically"
Posted: Thu Jul 02, 2015 10:53 pm
by 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.
Re: Adding Named Range "Dynamically"
Posted: Fri Jul 03, 2015 1:02 pm
by 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.