Finding the address of a range.

Resolved · Low Priority · Version 2007

Mon 16 Jan 2012, 08:30 Delegate Roger said...

Roger has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course

Finding the address of a range.

Sub FindAddress()
Dim abc As Variant
Dim R As Range
Set R = Range("TestRange")
abc = R.Address
End Sub

The VBA above puts the address of range ‘R’ into the variable abc. The Locals window lists R, but there are a vast number of characteristics associated with it. My original question was, where can I find the address of the range in Locals?- but now I realise that I have no idea how to navigate around all of R’s characteristics. Is there any logic to the way that they are aranged in Locals? Why are there so many ‘name’ variables? What is ‘item’? ...

Roger

Fri 20 Jan 2012, 09:20 Trainer Mark said...

RE: Finding the address of a range.

Hello Roger,

The items you are seeing are the properties associated with the object you set. At the first level are the properties available to the Range, when you expand to another level of an individual object, e.g. CurrentRegion, you then have a list of all the properties assosiated with that, and so on.
Impossible to explain all the items here, but there is a logical hirachy structure to the system, you may find an image mapping on the web.

The range is specified in the Name property of currentregion.

As for items, like others that repeat, these are not used unless requested. Similar to when you record a macro of a dialog box, even though you only use one thing in that dialog, the code will record all the options and properties available, even if unused.

I hope this helps your question, but can be daunting. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Mark
Microsoft Office Specialist Trainer