How do I alter the code in this BASIC macro to use named ranges instead of the cell numbers in the range?

English
,
#1

Please understand that I am specifically looking for BASIC as that’s what all the macros in my spreadsheet are in.

In the macro below the line aAllRangesAddress has a number of fields that are referenced by cell numbers. I’d like it to reference named ranges instead of the cell numbers.

REM  *****  BASIC  *****

Sub sumAndClearAllRanges3 'make sum from two ranges and put it to the second range
Dim oDoc As Object, oSheet As Object, data1(), data2(), oRange1 As Object, oRange2 As Object, i&, j&
Dim aAllRangesAddress As Variant 
	aAllRangesAddress = Array("A63:C72","I63:I72", "A74:C78","I74:I78", "A80:C90","I80:I90", "A92:C98","I92:I98", "A100:C118","I100:I118", "A120:C126","I120:I126", "N63:P74","V63:V74", "N76:P83","V76:V83", "N85:P88","V85:V88", "N90:P99","V90:V99", "N101:P109","V101:V109", "N112:P126","V112:V126" ) ' and all others
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	For j = LBound(aAllRangesAddress) To UBound(aAllRangesAddress) Step 2
		oRange1=oSheet.getCellRangeByName(aAllRangesAddress(j))
		oRange2=oSheet.getCellRangeByName(aAllRangesAddress(j+1))
		data1=oRange1.getData
		data2=oRange2.getData
		for i=lbound(data1) to ubound(data1) 'make sum 
			data2(i)(0)=Fix(data1(i)(0)+data1(i)(1)+data1(i)(2)+data2(i)(0))
		next i
		oRange1.clearContents(1) 'clear all contents in the range
		oRange2.setData(data2) 'set new data
	Next j
end Sub

So instead of ("A63:C72","I63:I72", "A74:C78","I74:I78") I’d prefer something like ("sGroup","sTotal", "bGroup","bTotal"
Part of the reason for this is I move the fields around every so often to make it easier for me to use and to add more stuff, but every time I do this I have to correct the macro above with the proper cell names. A named range works so much better because the named range moves with the cells in that range.
I first just tried changing the ranges to named ranges but that didn’t work. I tried fiddling with it using different methods in the few macros I have in this sheet but I can’t get any of them to work.

#3

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 …

#4

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.

#5

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

#6

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

#7

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

#8

I’m not understanding

#9

As we see you learned nothing!

#10

“I have been learning” does not mean in any way that I know everything already.

#11

How does what I have learned so far translate into what I need to do now?

#12

Sorry… I should be more precise, read: As we see you learned nearly nothing!

#13

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.

#14

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.

#15 
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()
#16

So I would use these different code snippets depending on what I was trying to do?

#17

Stop using code snippets.

#18

What?
Snippets = different code that is to be used in the overall code for different things.

#19

Start coding!

#20

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.

#21

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.