Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Finding the address of a range.
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Finding the address of a range.
Resolved · Low Priority · Version 2007
Reply Mon 16 Jan 2012, 08:30Delegate 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
For upcoming training course dates see: Pricing & availability
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
Reply Fri 20 Jan 2012, 13:25Delegate Roger said...
RE: Finding the address of a range.
Thanks, Mark. this makes life a lot easier.
I could have died of old age before finding it in the third level down!
Roger
|