PDA

View Full Version : Are these multiple range objects or multiple names



Mister_joe
04-12-2014, 05:47 AM
Hi guys,
Please, look at the codes below:


Option Explicit
Dim rangeCounter As Long
Public Sub DefineSuperRange()
dim superRange as range

rangeCounter = rangeCounter + 1
Set superRange = Worksheets("Sheet1").Range("A10:D1000")
With SuperRange
.HorizontalAlignment = xlRight
.ColumnWidth = 10
.NumberFormat = "###0.00;-###0.00"
.Name = "Range" & rangeCounter & "Range" & rangeCounter - 1


'Give focus to the first cell of the mainRange
.Cells(2, 1).Select
End with
End Sub


Private Sub Workbook_Open()
'Define the super range of data
Call DefineSuperRange
end sub


My worry? Each time I run the Workbook_Open() procedure, I see a new name in the Name Box. See attached image. Do these names represent different range objects or are they different names for the same range object?

SamT
04-12-2014, 07:55 AM
Just looking at things, I would say that all of them refer to Range("A10:D1000"). Not sure if they all refer to the same worksheet, only because I don't know what else is happening the all the code in the workbook.

Range5 may be different. The name is certainly different in composition.

Further questions are; Where does rangeCounter get its initial value, because it sure looks like it is the actual count of Range Names in the Worksheet, (but, due to the presence of Range5, not the count of Names in the workbook.)

Now, look at this bit of code

'Give focus to the first cell of the mainRange
.Cells(2, 1).Select
mainRange looks like a different range than superRange. If superRange is (A10:D1000), then .Cells(2, 1) is Worksheet Cell (A11)

Somehow, I get the feeling that you are not showing us all the code there is, and I cannot make a recommendation about what to do without seeing how all the procedures in the code interact with each other.

Mister_joe
04-12-2014, 10:47 AM
Thanks, Range5 is in the list because when I ran the code the first time, I used .Name = "Range" & rangeCounter.
I expected the first name in the list to be Range1, but then I realized that I had actually ran the code a couple of times and there were no entries in the Name box until I gave the range a name. That's why the count start from Range5. After that, I changed the code to .Name = "Range" & rangeCounter & "Range" & rangeCounter - 1. I ran the code a couple of times and new entries Range6Range5 . . . Range11Range10 joined the Name box list. After this, I closed the Excel and opened the workbook again. The entries then started from Range1Range0 .... and so on.

The mainRange was the variable name I initially thought of, but I changed it to SuperRange eventually. There is no other worksheet. I am merely using this code to try and understand whether the range objects are accumulated or simply renamed. I have a much larger code that has this issue.

SamT
04-12-2014, 02:45 PM
That makes more sense. Those are multiple Name objects with identical Refers To addresses.

IOW, there is one Range with multiple names.

In your code example above, "SuperRange.Name" is the equivalent of the fully qualified, "SuperRange.Name.Name"

SuperRange is a Range Object.
SuperRange.Name is a Property that returns the Name Object associated with SuperRange.
SuperRange.Name.Name is the Name Property of the Name object associated with SuperRange.

So; There are only three objects in your code: SuperRange, Range(A10:D1000"), and Name, (the blue one.)

Here's where it gets tricky. Name is a property of both the Range Object and the Name Object so Range.Name = "someName" is getting the Range Property "Name," which returns the Name object and then returns the default "Name" Property of the Name Object and sets that Property to "SomeName."

Edited to remove "default" in "Name is a default property of both the Range Object" above. All credit to Mark for catching this error. All other errors are purely mine.

Paul_Hossler
04-12-2014, 03:15 PM
Mister_joe -- what are you really trying to do in the WB_Open?

As you found out, this


.
Name = "Range" & rangeCounter & "Range" & rangeCounter - 1



just adds additional Names to the same range of cells

Different example --



Option Explicit
Sub test()
Dim i As Long

For i = 1 To 3
ActiveSheet.Range("A1:Z26").Name = "AtoZ" & I
Next I
For i = 1 To ActiveWorkbook.Names.Count
MsgBox i & " -- " & ActiveWorkbook.Names(i).Name
MsgBox i & " -- " & ActiveWorkbook.Names(i).RefersTo
Next i
End Sub



Paul

Mister_joe
04-13-2014, 05:59 AM
Thanks everyone. I just wanted to be sure that a new range object is not created each time the sub procedure is called. What am I trying to do? Well, I have this large set of data. From this data, I would copy a subset of it and paste in another area of the worksheet and somehow associate the name of the source with the name of the destination. So, for example, Range2Range1 means that Range2 was derived from Range1. I can also decide to copy from a subset to form another subset. The current subset would be Range3Range2Range1. This way, I know that Range3 came from Range2 which came from Range1. When I see the name of a sub range, I should be able to tell its family history. Another code snippet would then split the name into its component parts and use for further code execution. The approach I have adopted is probably not the best, but feel free to suggest other ways.

Paul_Hossler
04-13-2014, 06:49 AM
Having the same data is not the same as being the same object, since the "Is" operator compared the pointer to the object (ObjPtr)



Option Explicit
Sub test()
Dim R1 As Range, R2 As Range, R1A As Range
Set R1 = ActiveSheet.Range("A1:Z26")
Set R2 = ActiveSheet.Range("A1:Z26")

Set R1A = R1 ' Same pointer

R1.Name = "ONE"
R2.Name = "TWO"

MsgBox "Is R1 the same object as R2 = " & (R1 Is R2)
MsgBox "Is R1 the same object as R1A = " & (R1 Is R1A)

MsgBox (ActiveWorkbook.Names("ONE").RefersTo = ActiveWorkbook.Names("TWO").RefersTo)
MsgBox (ActiveWorkbook.Names("ONE").Name = ActiveWorkbook.Names("TWO").Name)

MsgBox ObjPtr(R1)
MsgBox ObjPtr(R1A)
MsgBox ObjPtr(R2)
End Sub




I would think that the naming convention you're using would get unwieldly after 5 or 6 generations, i.e. "Range6Range5Range4Range3Range2Range1myRange"

Paul

snb
04-13-2014, 07:59 AM
Sub M_snb()
On Error Resume Next
If Intersect(Cells(1), Sheet1.Range("snb_002")) Is Nothing Then Sheet1.Range("A1:K10").Name = "snb_002"
End Sub

Mister_joe
04-13-2014, 04:29 PM
That's right. The name will get very long very quickly, but I will leave it for VBA to worry about for now. I do not intend to go too far into the generations; just a proof of concept.
I really appreciate the responses.