Supppose you have a variable mySheet representing a sheet in the Calc document which actually contains the named range "mySpecialRange" (Regard the scope!).
myRg = mySheet.getCellRangeByName("mySpecialRange")
will then return the SheetCellRange object.
If you want to get a SheetCellRange for a named range (named "nrExample" e.g.) with globl scope, but you not yet know the sheet, you can get the NamedRange object by
namedRgObj = ThisComponent.NamedRanges.getByName("nrExample").
Inspect this object in the IDE (or use Xray or the MRI tool), and you will find
namedRgObject.ReferredCells.RangeAddress from where you can get .Sheet …
Since a named range always also is a named formula, the objcet also has a .Tokens property …
I am not sure how that would work with the example I post above of:
So instead of
("A63:C72","I63:I72", "A74:C78","I74:I78")I’d prefer something like("sGroup","sTotal", "bGroup","bTotal"
Are you telling me that I need to have a separate line of something like
myRg = mySheet.getCellRangeByName("mySpecialRange") for each and every named range in my spreadsheet? In this instance there will be 24 separately named ranges.
???
If you create a SheetCellRange object by
myNextRange = thisSheet.getCellRangeByName("A63:C72")
you assign the new object to a variable.
It’s the same if you use
myNextRange = thisSheet.getCellRangeByName("myRangeName")
And in both cases the object thisSheet can only create the range object inside the sheet.
This is the background of the only additional complication: To create the actual SheetCellRange object, you need to know (or find) the sheet which contains the range. I already showed you a way.
The code you posted doesn’t include sheet designators with the addresses. You need to make sure that the Sub only is called if the ActiveSheet actually is the one you want to operate on. I surely woudn’t do it this way, except, probably, I can assure that all my sheets can accept the procedure. Not likely in my eyes.
Named ranges come with some advantages, and one of these is that they know their sheets. They also come with disadvantages which are reasons for what I rarely use named ranges.
The macro is only activated in the sheet it is being used in, that’s why the macro I posted works just fine as I do not need to name the sheet at all. In fact none of the macros in this sheet need to name the sheet.
I prefer named ranges because they help identify better when I am looking at when I discover an issue and also make it easier when I need to know what the code is referencing at times.
I am not sure I understand the first part. There are 24 named ranges for this (as there are 24 separate ranges in the line aAllRangesAddress line.
Are you saying that I need to create 24 instances of the myNextRange line you posted above?
For example (taking the first 4 ranges in the code above):
myNextRange1 = thisSheet.getCellRangeByName("myRangeName1") myNextRange2 = thisSheet.getCellRangeByName("myRangeName2") myNextRange3 = thisSheet.getCellRangeByName("myRangeName3") myNextRange4 = thisSheet.getCellRangeByName("myRangeName4")
… and you create the ranges in pairs based on an index looping through a range using always the same variables oRange1 and oRange2. Yo can do it exactly the same way if the names aren’t range addresses in colon separated “A1” notation, but the names you defined for them via >Sheet>Named.... In fact there is the advantage that the Sub will exit with an error if you inadvertently call it for the wrong active sheetz.
I’m not understanding
As we see you learned nothing!
“I have been learning” does not mean in any way that I know everything already.
How does what I have learned so far translate into what I need to do now?
Sorry… I should be more precise, read: As we see you learned nearly nothing!
From the posted code:
Reworked as described:
Dim aRangeNames As Variant
aRangeNames = Array("sGroup", "sTotal", "bGroup", "bTotal" '... and all others'
oDoc=ThisComponent
oSheet=oDoc.CurrentController.ActiveSheet
For j = LBound(aRangeNames) To UBound(aRangeNames) Step 2
oRange1=oSheet.getCellRangeByName(aRangeNames(j))
oRange2=oSheet.getCellRangeByName(aRangeNames(j+1))
'...
Be aware of the fact that I didn’t check if the code can be expected to do something reasonable. Of course the named ranges must be defined properly.
I I figured it was something like that but it is beyond my limited understanding of how all this works together so far. The AllRangesAddress needing to be changed is something I thought should happen but I had no idea if that was a correct assumption or not. I will check this out later today when I get the chance. I have a few things that need to get done around the house and I need to work on my car a little. Unfortunately I haven’t been feeling well lately so things are taking longer than expected but hopefully whatever it is will pass soon.
xnames = ThisComponent.NamedRanges
named_value = xnames.getByName("named_cell").getReferredCells.getValue()
named_text = xnames.getByName("named_cell").getReferredCells.getString()
named_data = xnames.getByName("named_range").getReferredCells.getDataArray()
REM there are at least 2 different types of named ranges.
xnames = ThisComponent.DatabaseRanges
data = xnames.getByName("data").getReferredCells().getDataArray()So I would use these different code snippets depending on what I was trying to do?
Stop using code snippets.
What?
Snippets = different code that is to be used in the overall code for different things.
Start coding!
It’s your responsibilty to decied if any piece of code is reusable (after needed adaptions) in the context you want to place it in.
I still dont’t know (and don’t want to know) what you tried to do. What I posted is next to exclusively about how to get a SheetCellRange object for a NamedRange object of which you know the name, and which actually doesn’t describe anything but a SheetCellRange object. You need to know in what way you defined the NamedRange object.
[quote=“Lupp, post:13, topic:96497, full:true”]
This works perfectly! At first I was getting a runtime error but then discovered I failed to properly name one of the ranges.
Would the code snippets that @Villeroy posted above work in the same way?
May I suppose you still misunderstand the few lines of Basic code @Villeroy posted as “code snippets” or as a suggestion how to modify your code?
In fact they only exemplify how (in principle) you can get and use access to services of the LibO AP related to your concern. I also hinted in a previous post that the Calc document and every sheet contained in it have a property .NamedRanges - and where the relevant difference (scope) lies.
You can’t make use of such information by copy/paste. You need to study it to a certain level of understanding before you can make use of it. Some experiments (not done in a “production” document!) will be needed on the way.
Well‚ my own understanding of anything is also incomplete, but on no level whatever it can be shared by copy/paste.