Retrieving Properties from Large Range Objects using VBA in “One liner”

[DocAElstein]

Retrieving Properties from Large Range Objects using VBA in “One liner”

Hi .
. I have a couple of basic, but at the same time in depth questions. I kind of know the answers having really stretched my beginner’s brain on this one, but I could really do with a Profi Putting it clearly into basic words to finally get it clear in my head…

. I have been doing extensive test Looking at different ways to retrieve data from large Spreadsheet Range Objects..(often I have done empirical test as I have noted even experienced users have to resort back to that these days!!...).

. I think I have grasped (finally! ) that there is a basic process “allowed” that allows some things to be retrieved in a nice quick One Liner..

 [color=blue]Dim[/color] LargeRangeObject [color=blue]As[/color] Range [color=lightgreen]'One Range Object for entire range[/color]
 [color=blue]Set[/color] LargeRangeObject = RngName [color=lightgreen]'Direct assignmet to some existing Dimensioned Array[/color]
 
 Dim vTempIntermediateArray [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'.. return the array to a variable and then reference the elements through that variable[/color]
 [color=blue]Let[/color] vTempIntermediateArray = LargeRangeObject.Value2 [color=lightgreen]''Works vTemp is Array of variants values, a Data field.... Indeed... = LargeRangeObject.     is an efficient way of transferring a range of certain contents to VBA because you get a variant containing a 2-dimensional array of variants (So can for example also be ranges). This is much more efficient that looping on the cells one-by-one.[/color]

. I believe it can be done also for certain other things such as .Value .Text .Formula etc.

. I apologies for the naivety due to my lack of knowledge, but in the watch window I can see Array type lists of many other things such as the hyperlink addresses, (which are as an example currently of interest to me)

. But I am fairly sure I cannot obtain those “String” values with a simple one liner (Pseudo code)

  [color=blue]Let[/color] vTempIntermediateArray = LargeRangeObject.Hyperlinks().Item  [color=lightgreen]'Nothing like this works[/color]

. Can someone who has the time please answer the following as fully as you are able?
. Questions:

. 1) Confirm that I have no way to get at certain things like Hyperlink addresses in a One liner from a Large Range Object. (Please note I know extensively thanks to help here of solutions of the type “Evaluate Range type one Liners”, “ With / End With ” ; “.Formula” and am considering / comparing them separately)

. 2) (Assuming it is known at all anywhere?!) Can anybody put into fairly understandable words the exact processes going on which allow some things to be so obtained but others not.

. 3) Why do I not see an (Array) list of Values as I do Values2 in the Watch Window. (Interesting here that I have read many articles from experts explain that getting Value is for VBA quite a complicated process compared with value2. (Could the explanation be that maybe .Value is a Method which is used to get something similar to .Value2 which is then maybe a Property?.) This (naively) to me suggested VBA could do something similar in a One – Liner to get at things like hyperlink addresses. That is one reason why it is puzzling me ---

(.. this question could help me here as well (. 3b).. I am asking this in parallel somewhere else but it possibly appropriate to ask it here at this point.
.Value and .Value2 property or method or function?
. A trivial question maybe but it can throw a beginner off-course..
. I have seen experienced users refer to these two things as either property or method or function. And the Object window F2 is not a great help : Often the same name is given to properties and methods/Functions, which means you often need to know the answer before knowing where to look for the answer!!!! ) I expect one may say I should look in the Object Library. But I have been caught out in the past on this one as Methods and Properties sometimes have the same names and you then need to know the answer first to know where to look for the answer!! )


.- Thanks for any help. I have not been lazy on this. I have weeks of tedious measurements and contributions in Threads getting close to understanding. I need someone who really knows to state it a last time clearly for me..
.
. There is no urgency on these questions, and I would very much appreciate / prefer an in depth answer should somebody have the time.

Many Thanks
Alan.

 

[DocAElstein]

…….Day 22 Referencing Ranges with Arrays Ramblings
………………………………………My Brain Hurts​

. Hi. I appreciate that Duplicate posts are “Bad things” and personally avidly discourage them. However I think in this case it is appropriate to do so: Duplicate of post #31 here
Range Dimensioning, Range and Value Referencing and Referring to Arrays - Page 2



.. Some Feedback, Follow up and Questions…- As much as possible I try to ask the questions in such a way that a yes or no could suffice ! …….And there is no rush in answering. When someone has the chance to answer any question or any feedback here or elsewhere I would be very grateful.

……………………………………………………………………………………………..

.. Hi (Kyle ? or Rory? .. or anyone following these latest Ramblings of a Excel VBA inflicted idiot!!..


. 1) I think I have a very clear explanation now from You here as to what you (Kyle) were doing in the 3 “Public Property Get” s you gave me in Post #2.
http://www.mrexcel.com/forum/excel-...sual-basic-applications-%93one-liner%94.html?
Thanks to your help here and elsewhere I get the point now about them being an example of sort of “Getter” (read only) “Thing” :

….. all my code was an example to show a custom implementation of the functions you were discussing……so that I could demonstrate properties and match the syntax of a range object. All the Value property does in my code is augment the Value2 property to match the behavior of the built in Value (it returns dates and currency types where they are encountered) rather than just the values that Value2 does. It should be obvious from this why Value2 is faster than Value - it has less work to do.
………..

..Thanks, That all ties up nicely with that Charles Williams TEXT vs VALUE vs VALUE2 UDF Performance Stuff…...

https://fastexcel.wordpress.com/?s=Text+vs
https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/

… .. As you have clearly said, You have effectively mimicked as demonstration what is going on with the VBA Range properties .Text .Value and .Value2
. You have chosen to done this by Creating a new Object.

….
(…. To do this you have special type of Code Module available in VBA is made. This will have the name of a “type” or “Class” of Object. That is to say collected in this module will be the various “Things” you need. That Module and it’s associated name is not itself an Object but rather can be thought of as something along the lines of a “BluePrint” ; “collection of Tools, variables etc.. ” - Or basically everything you need for the type of Object you are envisaging to use. Or a template which Lets being set – up so that then it is ready to be Got (“Getted”) at
. Then in a typical normal module the usual sort of way for creating an object is used in a form like you showed similar (Here the similar code used by me for this Thread):

“Normal” (Sheet) Module Code:

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Sub[/COLOR] RetrieveLargeRangeObjectsPropsOneLiner() [COLOR=lightgreen]'Kyle Demo for http://www.mrexcel.com/forum/excel-questions/839369-retrieving-properties-large-range-objects-using-visual-basic-applications-%93one-liner%94.html[/COLOR]
 
[COLOR=blue]Dim[/COLOR] objRetProps [COLOR=blue]As[/COLOR] myRange: [COLOR=blue]Set[/COLOR] objRetProps = [COLOR=blue]New[/COLOR] myRange [COLOR=lightgreen]'Instantiate-"Bring it into existence" a specific Object of that "type" (Alternative Dim objRetProps As New myRange - Only difference is that it is automatically made when first used - Probably not really like Late boinding as intellisense works here also[/COLOR]
 
[COLOR=blue]Dim[/COLOR] myArr() [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
myArr() = objRetProps.Value [COLOR=lightgreen]'Kyle made property of class myRange[/COLOR]
myArr() = objRetProps.Text [COLOR=lightgreen]'Kyle made property of class myRange[/COLOR]
myArr() = objRetProps.Value2 [COLOR=lightgreen]'Kyle made property of class myRange[/COLOR]
myArr() = objRetProps.HypAddressKyle [COLOR=lightgreen]'Alan made property of class myRange[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

The more appropriate terminology used would be instantiate or “Bring it into existence” a specific Object of that “type” or “class” …..)


….Question 1a). Have I got that above rambling about right?

…………………………………..
…. Your(Kyle’s) .Value .Text .Value2 are specific properties “made” as it were in your code and “belonging only to the class “myRange”. So VBA will not confuse them (as an idiot like me would) with the .Value .Text .Value2 that “belong” to the Application.Excel. … .. .Range .
… In your case you could have .KyleValue .KyleText .KyleValue2 or similar.
….Question 1b). Have I got that right?

………………………………………………..
2). Broadly speaking the two most common ways to make a “Setter” and “Getter” Property in a Classe module is

. (i) A simple “Public one liner” using a Public variable of the form
Public strPropAnyName As String

. (ii) A “Let Get Pair of Property method Statements” with a linking variable. Something of the form

[COLOR=blue]Private[/COLOR] p_string [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Let[/COLOR] strPrivPropAnyName(val_in [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=lightgreen]'…….....[/COLOR]
'….
p_string = val_in
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]
 
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Get[/COLOR] strPrivPropAnyName() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] [COLOR=lightgreen]'......[/COLOR]
'……..
strPrivPropAnyName = p_string
[COLOR=lightgreen]'…..[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]

.. But You(Kyle) did a neat trick to simplify things a bit by replacing that first Public Property Let with a “Class Module Event” sort of a thing that makes (Sets in your case) the linking variable (in your case the p_Range). It is sort of made by at the instantiate step which has to be made anyway so it is a good place to “tag it on” or “sneak it in”. (Probably a good idea, only allowing things to be Got (“Getted”) and not Let – ing things be modifies to reduce the chances of an OP like me screwing things up!
. So your Public Property Get s, (which all share this same p_Range) are not in this case paired / matched with a with a Public Property Let, which is more usual

….Question 2a). Have I got that right?


. 3) I did a crude “Pair of Property methods” codes based on the various codes you gave me recently to give me another hyperlink Address Property similar to that which I did in Post #5
http://www.mrexcel.com/forum/excel-...isual-basic-applications-%93one-liner%94.html
(.. The code to run is in the last few code lines of the last Code I give here at the end )

. I did not want to push my luck and do try to do anything complicated so really the The Public Property Get is based on yours with all the difficult stuff in and mine matching Public Property Let is very simple..

. 3b). looking naively it seems to me that the complicated stuff could go in either or even be split between the two. ….Question 3b).. Is that correct, or is it more subtle..(just thought I would ask before I experiment that one further..)

.4).

….Apologies……
Using an object is probably a bit much for you right now, but I did it so that I could demonstrate ………….

I actually find it very helpful to see other angles and have been busy learning and the basics for Object to get my last code variations..
. - Quick “Object” question.. There is no increased efficiency by creating custom objects but just like Functions, it tidies up code, can make it a bit self documenting (Pretty useful for my messy Codes!!), and is particularly helpful in reusing codes or pasts of them.
….Question 4a). Have I got that right?


……Following on from 4a) I am fooling myself a bit to think that I have now achieved the (impossible) main Theme of this / that thread and, for example found a way to do a “one liner to get Hyperlink Addresses with my code line of the form discussed in Post #5
http://www.mrexcel.com/forum/excel-...isual-basic-applications-%93one-liner%94.html
myArr() = obj.HypKyle

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Sub[/COLOR] RetrieveLargeRangeObjectsPropsOneLiner() [COLOR=lightgreen]'Developed from Kyle Demo for http://www.mrexcel.com/forum/excel-questions/839369-retrieving-properties-large-range-objects-using-visual-basic-applications-%93one-liner%94.html[/COLOR]
[COLOR=blue]Dim[/COLOR] wks [COLOR=blue]As[/COLOR] Worksheet: [COLOR=blue]Set[/COLOR] wks = ThisWorkbook.Worksheets("KyleMrExcel")
[COLOR=blue]Dim[/COLOR] rngHyps [COLOR=blue]As[/COLOR] Range: [COLOR=blue]Set[/COLOR] rngHyps = wks.Range("A3:A10")
[COLOR=blue]Dim[/COLOR] objRetProps [COLOR=blue]As[/COLOR] myRange: [COLOR=blue]Set[/COLOR] objRetProps = [COLOR=blue]New[/COLOR] myRange [COLOR=lightgreen]'Instantiate-"Bring it into existence" a specific Object of that "type" (Alternative Dim objRetProps As New myRange - Only difference is that it is automatically made when first used - Probably not really like Late boinding as intellisense works here also[/COLOR]
 
[COLOR=blue]Dim[/COLOR] myArr() [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
[COLOR=blue]Let[/COLOR] myArr() = objRetProps.value [COLOR=lightgreen]'Kyle made property of Class myRange[/COLOR]
[COLOR=blue]Let[/COLOR] myArr() = objRetProps.Text [COLOR=lightgreen]'Kyle made property of Class myRange[/COLOR]
[COLOR=blue]Let[/COLOR] myArr() = objRetProps.Value2 [COLOR=lightgreen]'Kyle made property of Class myRange[/COLOR]
[COLOR=blue]Let[/COLOR] [COLOR=orange]myArr() = objRetProps.HypAddressKyle[/COLOR] [COLOR=lightgreen]'Alan made property of Class myRange[/COLOR]
 
 
 
 
 
[COLOR=lightgreen]'Codes for other ramblings in post[/COLOR]
[COLOR=blue]Dim[/COLOR] objHypAlan [COLOR=blue]As[/COLOR] AlanRangeProps: [COLOR=blue]Set[/COLOR] objHypAlan = [COLOR=blue]New[/COLOR] AlanRangeProps
 
[COLOR=lightgreen]'Kyle Demo from Post #29 http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-2.html    "(Getter Setter is really Class/Object terminology, it just means whether you can set or get a particular value  )"[/COLOR]
[COLOR=blue]Let[/COLOR] objHypAlan.strPropAnyName = "(i) One line String Property (Any String)": [COLOR=blue]Let[/COLOR] wks.Range("A1").value = objHypAlan.strPropAnyName [COLOR=lightgreen]'Simplist example to demonstrate "Reading" "Letting" "Settter" : "Writing" "Getting" "Getter" - Uses....[/COLOR]
[COLOR=blue]Let[/COLOR] objHypAlan.strPrivPropAnyName = "(ii) ""Let Get Property Method Pair"" (String Length > 4)": [COLOR=blue]Let[/COLOR] wks.Range("B1").value = objHypAlan.strPrivPropAnyName [COLOR=lightgreen]'Typical example to demonstrate "Reading" "Letting" "Settter" : "Writing" "Getting" "Getter" -Uses the typical "Method Pair" in a Calss module[/COLOR]
    [COLOR=lightgreen]'        Let objHypAlan.strPrivPropAnyName = "Thi": Let wks.Range("B1").value = objHypAlan.strPrivPropAnyName 'Would return empty cell due to condition set in "Property Method Pair"[/COLOR]
[COLOR=lightgreen]'[/COLOR]
[COLOR=blue]Let[/COLOR] myArr() = objHypAlan.HypAddressAlanKyle '
[COLOR=blue]Let[/COLOR] objHypAlan.HypAddressAlanKylePair = rngHyps
[COLOR=blue]Let[/COLOR] [COLOR=orange]myArr() = objHypAlan.HypAddressAlanKylePair[/COLOR] [COLOR=lightgreen]'Alan made property of Class AlanRangeProps[/COLOR]
 
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

…. In the practice I have just tidied up (hid) the looping process, and there may or may not be speed improvements , probably on average about none..
….Question 4b). Have I got that right?

…..
. 5) Maybe not too important as This one I am still working actively on just now. Maybe I will think of a few more ways to try.. But..Along the way through a few Threads similar to this I have been able to assign an TemporaryIntermediateArrayvariant to a collection of Ranges. This, for example proved useful for getting a Microsoft Scripting Runtime Dictionary items (which were Ranges in that case shown in the Watch window instead of as usual the Keys…
http://www.mrexcel.com/forum/excel-...ionary-store-then-retrieve-range-objects.html
. A simple one liner was then
Dim rResults() As Variant
Let rResults = dicLookupTable.Items()
. Then rResults could be looked at in the watch window revealing the full range info held in the Microsoft Scripting Runtime Dictionary items “dicLookupTable.Items”
. Also I could access Range properties then through use of rResults(___).Value2 … etc.

… I was kind of hoping I might be able to get a “Let Get Pair of Property method Statements” to return a collection of hyperlinks get myArr() as a collection of hyperlinks, - something like
myArr()=obj.hyperlinks
… maybe this is stupid for some reason?? Maybe I have missed something obvious again..??
… (maybe as a beginner I am really over doing it here… might be getting into the area of using Property Set to point to other objects??....)


Thanks
Alan

Below (is my class Module code ( “AlanRangeProps” ) (With my last “Let Get Property Method Pair” ( HypAddressAlanKylePair ) towards the end ): and This a screen shot of the small example file prepared for this post #31:

File, Code abd Table referrenced in Post #31

Row\Col	A​	B​
1​	"(i) One line String Property (Any String)"	"(ii) Let Get Property Method Pair (String Length > 4)"
2​
3​	Apple fresh	Apfel
4​	'Bierwurst' (coarse heat-treated sausage in bladder and smo	"Cordon bleu" vom Schwein, bofrost
5​	'Breslauer' Lyonaise	"Peperonata" Paprikazubereitung Vogeley GV
6​	'Gaisburger Marsch' (potatoes with beef) (1)	"Pomona" Tomtenpüree-Konzentrat Vogeley GV
7​	'Göttinger Blasenwurst'/Krakauer	'Biene-Maja' Banane-Mandel Fruchtschnitte, Evers Naturkost
8​	'Heaven and earth' (apples and pot.) with blood sausage (3)	'Flip' Apfel-Birne Fruchtschnitte, Evers Naturkost
9​	'Jägersoße' (thickened brown sauce with mushrooms) (5)	'Maja-Willi-Flip' Multifrucht Fruchtschnitte, Evers Naturkost
10​	'Kaiserschmarrn' (crêpes) (5)	'Willi' Apfel-Orange Fruchtschnitte, Evers Naturkost

<tbody>
</tbody>

… as hyperlinks are involved and are inactive in the Range screenshot I enclose File incase that helps.. (XL 2007 VbaProblem 01.03.2015.xlsm) (Sheet for Screenshot KyleMrExcel) Macros in Class modules or Sheet Module KyleMrExcel)
https://app.box.com/s/7t6aglt3s0am2bb4nmgtdmxie7vysyvo


Class Module AlanRangeProps:

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
[COLOR=blue]Private[/COLOR] ppInitEvent_Range [COLOR=blue]As[/COLOR] Range [COLOR=lightgreen]'This belongs here ###, but code errors if there[/COLOR]
[COLOR=blue]Private[/COLOR] p_Range [COLOR=blue]As[/COLOR] Range
[COLOR=lightgreen]'1) Simplist example to demonstrate adding a property to the "Class collection"[/COLOR]
[COLOR=blue]Public[/COLOR] strPropAnyName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] [COLOR=lightgreen]'which can be both"Reading" "Letting" "Settter" : "Writing" "Getting" "Getter"..This can be both, no restrictions or checks..[/COLOR]
 
[COLOR=lightgreen]''
'
'
'
'2) More Typical comnprising two "Property Method Pairs" to do similar to 1). Logicaly ordered here, but can have the get first.stangel?.....[/COLOR].
[COLOR=blue]Private[/COLOR] p_string [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] [COLOR=lightgreen]'Just a Variable to link the Method pairs (Note this is coincidentally At the start to conform to this syntax ###[/COLOR]
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Let[/COLOR] strPrivPropAnyName([COLOR=blue]ByVal[/COLOR] val_in [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=lightgreen]'Bit confusing, but strPrivPropAnyName is a better defined version of strPropAnyName[/COLOR]
    [COLOR=blue]If[/COLOR] Len(val_in) < 4 [COLOR=blue]Then[/COLOR] [COLOR=lightgreen]'Just to demonstrate that with a "Property Method Pair" we have a bit more control[/COLOR]
        MsgBox "String less than 4"
    [COLOR=blue]Else[/COLOR]
        p_string = val_in
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Get[/COLOR] strPrivPropAnyName() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    strPrivPropAnyName = p_string
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]
 
 
[COLOR=lightgreen]'
'
'
'3) Kyle Quasi "Method Pair" "Setter Getter" (No Setter available in spreadsheet - replace that with the event type function which occure when a Class is first called. So a write only)[/COLOR]
[COLOR=lightgreen]'  ### Private ppInitEvent_Range As Range'Errors if here[/COLOR]
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Class_Initialize() [COLOR=lightgreen]'Replaces Typical Public Property Let[/COLOR]
    Set ppInitEvent_Range = Worksheets("KyleMrExcel").Range("A3:B10")
    [COLOR=lightgreen]'            [COLOR=blue]Set[/COLOR] p_Range = Worksheets("KyleMrExcel").Range("A3:B10")[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Get[/COLOR] HypAddressAlanKyle() [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
 
    [COLOR=blue]Dim[/COLOR] var() [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR]
    [COLOR=blue]Dim[/COLOR] temp [COLOR=blue]As[/COLOR] Range
   
    [COLOR=blue]Dim[/COLOR] rowCount [COLOR=blue]As[/COLOR] Long: rowCount = ppInitEvent_Range.Rows.Count
    [COLOR=blue]Dim[/COLOR] columnCount [COLOR=blue]As[/COLOR] Long: columnCount = ppInitEvent_Range.Columns.Count
   
    [COLOR=blue]Dim[/COLOR] x [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] y [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
   
    [COLOR=blue]If[/COLOR] (rowCount = 1 And columnCount = 1) [COLOR=blue]Then[/COLOR]
        HypAddressAlanKyle = ppInitEvent_Range.Hyperlinks(1).Address
        [COLOR=blue]Exit[/COLOR] [COLOR=blue]Property[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
   
      
    [COLOR=blue]ReDim[/COLOR] var(1 [COLOR=blue]To[/COLOR] rowCount, 1 [COLOR=blue]To[/COLOR] columnCount)
   
    [COLOR=blue]For[/COLOR] x = 1 [COLOR=blue]To[/COLOR] rowCount
        [COLOR=blue]For[/COLOR] y = 1 [COLOR=blue]To[/COLOR] columnCount
            var(x, y) = ppInitEvent_Range.Cells(x, y).Hyperlinks(1).Address
        [COLOR=blue]Next[/COLOR] y
    [COLOR=blue]Next[/COLOR] x
   
    HypAddressAlanKyle = var
   
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]
[COLOR=lightgreen]'[/COLOR]
'
'
'
'
'
[COLOR=lightgreen]'[/COLOR]
'
'
 
 
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Let[/COLOR] HypAddressAlanKylePair([COLOR=blue]ByVal[/COLOR] Rng_in [COLOR=blue]As[/COLOR] [COLOR=blue]Variant[/COLOR])  'Variant is required to match Public Property Get. It remains a [COLOR=blue]Variant[/COLOR] type, a collection (of Ranges here). It can however be ommited!? "Implicit Default?"
 
    Set p_Range = Rng_in
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR]
[COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] [COLOR=blue]Get[/COLOR] HypAddressAlanKylePair() [COLOR=blue]As[/COLOR] Variant [COLOR=lightgreen]'[COLOR=blue]Variant[/COLOR] is the only variable that will return a collection (Array)[/COLOR]
 
    [COLOR=blue]Dim[/COLOR] var() [COLOR=blue]As[/COLOR] Variant
    [COLOR=blue]Dim[/COLOR] temp [COLOR=blue]As[/COLOR] Range
   
    [COLOR=blue]Dim[/COLOR] rowCount [COLOR=blue]As[/COLOR] Long: rowCount = p_Range.Rows.Count
    [COLOR=blue]Dim[/COLOR] columnCount [COLOR=blue]As[/COLOR] Long: columnCount = p_Range.Columns.Count
   
    [COLOR=blue]Dim[/COLOR] x [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] y [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
   
    [COLOR=blue]If[/COLOR] (rowCount = 1 And columnCount = 1) [COLOR=blue]Then[/COLOR]
        HypAddressAlanKyle = p_Range.Hyperlinks(1).Address
        [COLOR=blue]Exit[/COLOR] [COLOR=blue]Property[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
   
      
    [COLOR=blue]ReDim[/COLOR] var(1 [COLOR=blue]To[/COLOR] rowCount, 1 [COLOR=blue]To[/COLOR] columnCount)
   
    [COLOR=blue]For[/COLOR] x = 1 [COLOR=blue]To[/COLOR] rowCount
        [COLOR=blue]For[/COLOR] y = 1 [COLOR=blue]To[/COLOR] columnCount
            var(x, y) = p_Range.Cells(x, y).Hyperlinks(1).Address
        [COLOR=blue]Next[/COLOR] y
    [COLOR=blue]Next[/COLOR] x
   
    HypAddressAlanKylePair = var
   
End [COLOR=blue]Property[/COLOR]