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.

 

[Kyle123]

1. No, you can't

2. It's pretty simple, there isn't a built in property to return Hyperlinks. This isn't some dark art, think of Value2 beuing the base value and have a look at the below and think of the functions like this (the getters anyway)
Class myRange

Option Explicit

Private p_Range As Range

Public Property Get Value() As Variant
    
    Dim var() As Variant
    Dim temp As Range
    
    Dim rowCount As Long: rowCount = p_Range.Rows.Count
    Dim columnCount As Long: columnCount = p_Range.Columns.Count
    
    Dim x As Long
    Dim y As Long
    
    If (rowCount = 1 And columnCount = 1) Then
        Value = p_Range.Value
        Exit Function
    End If
    
       
    ReDim var(1 To rowCount, 1 To columnCount)
    
    For x = 1 To rowCount
        For y = 1 To columnCount
            Set temp = p_Range.Cells(x, y)
            
            If Left$(Evaluate("CELL(""Format""," & temp.Address & ")"), 1) = "C" Then
                Dim cur As Currency
                cur = temp.Value2
                var(x, y) = cur
                GoTo Continue
            End If
            
            If IsDate(temp.Value2) Then
                Dim dt As Date
                dt = temp.Value2
                var(x, y) = dt
                GoTo Continue
            End If
            
            var(x, y) = temp.Value2
Continue:

        Next y
    Next x
    
    
    Value = var

End Property

Public Property Get Value2() As Variant

    Dim var() As Variant
    Dim temp As Range
    
    Dim rowCount As Long: rowCount = p_Range.Rows.Count
    Dim columnCount As Long: columnCount = p_Range.Columns.Count
    
    Dim x As Long
    Dim y As Long
    
    If (rowCount = 1 And columnCount = 1) Then
        Value = p_Range.Value
        Exit Function
    End If
    
       
    ReDim var(1 To rowCount, 1 To columnCount)
    
    For x = 1 To rowCount
        For y = 1 To columnCount
            var(x, y) = p_Range.Cells(x, y).Value2
        Next y
    Next x
    
    Value2 = var
    
End Property

Public Property Get Text() As Variant

    Dim var() As Variant
    Dim temp As Range
    
    Dim rowCount As Long: rowCount = p_Range.Rows.Count
    Dim columnCount As Long: columnCount = p_Range.Columns.Count
    
    Dim x As Long
    Dim y As Long
    
    If (rowCount = 1 And columnCount = 1) Then
        Value = p_Range.Value
        Exit Function
    End If
    
       
    ReDim var(1 To rowCount, 1 To columnCount)
    
    For x = 1 To rowCount
        For y = 1 To columnCount
            var(x, y) = p_Range.Cells(x, y).Text
        Next y
    Next x
    
    Text = var

End Property


Private Sub Class_Initialize()
    Set p_Range = Sheets(1).Range("a1:b10")
End Sub

Module:

Option Explicit

Sub test()

Dim obj As myRange
Set obj = New myRange

Dim myArr
 
myArr = obj.Value
myArr = obj.Text
myArr = obj.Value2

End Sub

So it's pretty evident that you can't get hyperlinks like this

They're all properties of the Range object

N.B the code doesn't quite work, but serves as an illustration

 

[RoryA]

Value and Value2 are both exposed as properties. You can tell that from the icon in the Object Browser. You don't see Value in the Locals window because it takes an optional argument unlike Value2. (some would argue that anything that takes an argument is actually a method, not a property, regardless of what the OB indicates)

 

[Kyle123]

It's Lets where you can pass 2 parameters that really boggle my brain syntax wise!

 

[DocAElstein]

Hi again Kyle…

1. No, you can't
……

. That is very helpful… As I am clearly getting way out of my depth here, that at least prevents me trying endless experiments further to try and do it!

………2. It's pretty simple, there isn't a built in property to return Hyperlinks. This isn't some dark art, think of Value2 being the base value and have a look at the below and think of the functions like this (the getters anyway)……….

So it's pretty evident that you can't get hyperlinks like this

They're all properties of the Range object

N.B the code doesn't quite work, but serves as an illustration

. I very much appreciate you taking the time to produce here, what must be a very Full and rich explanation. I am afraid I do not understand at all what you are saying here. But that is purely down to my ignorance in advanced VBA. I will certainly be coming back and going through what you have written many times. Hopefully one day I may have a chance of understanding it. For now I only catch some very vague ideas which seem to be along the lines of that Charles Williams’ Article. I believe he is a very renowned expert, but was experimenting to find out what is going on here.. And I believe with some things he even stated he was still not sure. – Maybe I can take some comfort in that!!

………….

. The best I can do (because of my ignorance) is just “experiment”…. I took your “Public Property Get Value2()” and fiddled around with it to get this,

[color=blue]Public[/color] [color=blue]Property[/color] [color=blue]Get[/color] HypKyle() [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 = 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]
        Value = 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
   
    HypKyle = var
   
[color=blue]End[/color] [color=blue]Property[/color]

………………

…. It sort of “works” such that the final MyArr here below gives me the final results I am looking for

[color=blue]Sub[/color] test()
 
[color=blue]Dim[/color] obj [color=blue]As[/color] myRange
[color=blue]Set[/color] obj = [color=blue]New[/color] myRange
 
[color=blue]Dim[/color] myArr() [color=blue]As[/color] [color=blue]Variant[/color]
 
myArr() = obj.Value
myArr() = obj.Text
myArr() = obj.Value2
myArr() = obj.HypKyle
[color=blue]End[/color] [color=blue]Sub[/color]

……………………………….
Interestingly there are some obvious similarities to a Public Function given to me by Rory

[color=blue]Public[/color] [color=blue]Function[/color] GetURLRoaryA(cell [color=blue]As[/color] Range, [color=blue]Optional[/color] default_value [color=blue]As[/color] [color=blue]Variant[/color]) [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'When an Array is assigned through Let to this function, it returns an Array which then through effectively A "Let One Liner" becomes a modified Range based on the Supplied range and any Additional Arguments. It can be thought as a normal Function working on a input Range. A Particular characteristic here is that the Output is created in a loop which specifically assigns each cell within the range. This probably ensures that VBA in any further workings "Know" or "allows" for an Array and so for example ensures that this Function can be used in Evaluate Function "One liners" without the usual "coercing stuff". I think variant is the only type of Function capable of returning an Array. By val use the value held in the value within the sub (Function) , rather than referencing the allocated source "bucket" of the variable. So any given value to the Variable outside the Sub (Function) are not changed[/color]
[color=lightgreen]'     'Lists the Hyperlink Addresses for a given range[/color]
[color=lightgreen]'     'If cell does not contain a hyperlink, return default_value[/color]
[color=lightgreen]'                                    Dim rCell As Range'...." started out with a simpler version and then decided to redo it to handle ranges with more than one row/column but forgot to remove the variable declaration."[/color]
    [color=blue]Dim[/color] vOut() [color=blue]As[/color] [color=blue]Variant[/color]
    [color=blue]Dim[/color] x [color=blue]As[/color] Long: [color=blue]Dim[/color] y [color=blue]As[/color] Long [color=lightgreen]'We build a collection of output by looping into an Array. So these variables will be used for both Row,Column indicies for the cell Range coming into the [color=blue]Function[/color], as well as the Array Indicies. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.[/color]
    [color=blue]If[/color] IsMissing(default_value) [color=blue]Then[/color] default_value = vbNullString [color=lightgreen]'I guess this is important to give it an empty cell rather than not yet anythng, as not yet anything may cause strange errors somewhere[/color]
 
    [color=blue]If[/color] cell.Count = 1 [color=blue]Then[/color] [color=lightgreen]'This IF bit has the effect of getting the function just to return a single string in the unique case of only one cell. It will work as well without this extra IF bit. But it is probably just good practice to avoid unecerssary looping ang making of an Array for this simplist case.[/color]
        [color=blue]If[/color] cell.Hyperlinks.Count > 0 [color=blue]Then[/color]
            GetURLRoaryA = cell.Hyperlinks(1).Address [color=lightgreen]'The 1 in ( ) is just to get the syntax right. It can only be 1 for 1 cell. But for a given range of n cells it could be 1 to n[/color]
        [color=blue]Else[/color]
            GetURLRoaryA = default_value [color=lightgreen]'Something to return for No Hyperlink in the (one in thisd case) cell..[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Else[/color] [color=lightgreen]'For the case of more more than one cell we effectively loop throught each cell and put the required Hyperlink in the appropriate place in the (Re)Dimensioned Array[/color]
        [color=blue]ReDim[/color] vOut(1 [color=blue]To[/color] cell.Rows.Count, 1 [color=blue]To[/color] cell.Columns.Count) [color=lightgreen]'We must give our Array a size, or we cannot add to parts of it. The ReDim rather than Dim is used as Dim only accepts Numbers, not variables as ReDim does.[/color]
        [color=blue]For[/color] y = 1 [color=blue]To[/color] cell.Rows.Count [color=lightgreen]'Using for fun the convention of cell numbers in a spreadsheet..start in the first row..[/color]
            [color=blue]For[/color] x = 1 [color=blue]To[/color] cell.Columns.Count [color=lightgreen]'.... go along the .....[/color]
                [color=blue]If[/color] cell(y, x).Hyperlinks.Count > 0 [color=blue]Then[/color] [color=lightgreen]'Always check / allow for no Hyperlink or the next line could error..[/color]
                    vOut(y, x) = cell(y, x).Hyperlinks(1).Address [color=lightgreen]'Worth remembering the extra Thing(y, x).Stuff rather than Thing and /n or just Stuff.. a taypical mistake, (ar least by me!!)[/color]
                [color=blue]Else[/color]
                    vOut(y, x) = default_value [color=lightgreen]'Something to return for No Hyperlink in cell(y, x)..[/color]
                [color=blue]End[/color] [color=blue]If[/color]
            [color=blue]Next[/color] x [color=lightgreen]'....coulmns in current row....[/color]
        [color=blue]Next[/color] y [color=lightgreen]'....then go to next row down ..and start going along... etc..[/color]
        GetURLRoaryA = vOut [color=lightgreen]'At This point GetURLRoaryA becomes an Array or Rather an Object with a collection of values. By Virtue of a typical let Rng.Value = RoaryLeftPubic(Rng ,   ____) the range will be filled  with the values from the Array. This would be the normal one line allowed exclisively for puutting just values in this given Rng[/color]
 
    [color=blue]End[/color] [color=blue]If[/color]
End Function [color=lightgreen]'GetURLRoaryA[/color]

. As you can see, It is going to take me some time before I can destroy your codes ( or at least the “Public Property Get Value() As Variant” one ) with ‘comments to the extent I have done with Rory’s in order to understand them.
. Of course I would not complain if you had the time to either put some ‘comments in or explain things a bit more.
. But I do not want to push my luck. Clearly Through your efforts I have a new…. “Public Property Get HypKyle()” and an associated sort of “One liner”

myArr() = obj.HypKyle

…. To add to my list of ideas to experiment with.

. (Not too sure if I am happy or sad about that given how long I may be on it!! …..
.. Bin on this “BigRangeReferrencing and Arr () (___) stuff” about 18 days now.. about the limit before my wee brain gives up.. , ),

..But thanks very much again for your efforts. I really apprieciate it.

Alan..

 

[DocAElstein]

Hi Rory, thanks for the Reply

Value and Value2 are both exposed as properties. You can tell that from the icon in the Object Browser. …...

. Thanks I sort of saw that but for reasons I mentioned it is very helpful to get it said ”from the man” as it were..

…. You don't see Value in the Locals window because it takes an optional argument unlike Value2……...

. Thanks, again that may be written somewhere, but I could not find it, but again the Subject is so vast you often need to know the answer to know the right search criteria for “Googling”!

…. (some would argue that anything that takes an argument is actually a method, not a property, regardless of what the OB indicates)

. Glad you said that. It is a bit encouraging that my thoughts on that……

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

…. Maybe were not too stupid.

.. Thanks

Alan..

 

[Kyle123]

Apologies, all my code was an example to show a custom implementation of the functions you were discussing.

Using an object is probably a bit much for you right now, but I did it 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 behaviour 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.

If you want to simplify the hyperlink property, simply take it out of a class and make it into a function (where you pass in the range object) that returns an array of hyperlinks - exactly as Rory's does. They accomplish the same thing.

 

[DocAElstein]

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


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

…….Using an object is probably a bit much for you right now…….

.. maybe if I get the basic idea after watching those videos again.. (….
Range Dimensioning, Range and Value Referencing and Referring to Arrays - Page 2
https://www.youtube.com/watch?v=jHa8W52mD1k&index=65&list=PLS7iHfqXNVhK3yzd_4XS5k4zsvnu2mkJC
- https://www.youtube.com/watch?v=CvugIQKce1k&index=68&list=PLS7iHfqXNVhK3yzd_4XS5k4zsvnu2mkJC
…) . Then I will just about have enough understanding at least on my “Public Property Get HypKyle()” and an associated sort of “One liner”
myArr() = obj.HypKyle
…. To add to my list of ideas to codes I am currently finishing off……
..
(. I am basicaslly doing a “Hyperlink.Address” version of the Left Function experiments I did around the last few posts here:.
http://www.mrexcel.com/forum/excel-...ic-applications-evaluate-range-vlookup-9.html
…..
…. The “Public Property Get HypKyle()” and an associated sort of “One liner”
myArr() = obj.HypKyle
Would fit in nicely after the code 7 types which are basically doing I think more or less what you are suggesting)

…….
If you want to simplify the hyperlink property, simply take it out of a class and make it into a function (where you pass in the range object) that returns an array of hyperlinks - exactly as Rory's does. They accomplish the same thing.

….


… you never know… Through the mysteries of how VBA is working the almost identical functions, yours and Rory’s Public Function GetURLRoaryA() may give different interesting results (or not!?)

..
Alan

 

[DocAElstein]

..

.......
…. The “Public Property Get HypKyle()” and an associated sort of “One liner”
myArr() = obj.HypKyle
Would fit in nicely after the code 7 types which are .........

...QUICK EDIT: Should read...

…. The “Public Property Get HypKyle()” and an associated sort of “One liner”
myArr() = obj.HypKyle
Would fit in nicely after code 8 which is......

 

[DocAElstein]

It's Lets where you can pass 2 parameters that really boggle my brain syntax wise!

.. I might be rambling again in the wrong direction here..
.. If you are talking about multiple arguments in a Property Lets(and Sets).. It occurred to me as well that it is a bit dopey.. the syntax I mean.. If I understand you can have as many arguments from one onwards , as you want. ( that is to say in Property Let n + 1 , where n =>0 and n is the number of arguments in Property Get)
… But the last one in Let(or Set) ,( the “+1” ) is important and I think sort of both a) “links” the Property Let with its Property Get and b) is the one set by the calling procedure.. ( I think all the others just “link” two paired Let Get Property Method Statements Things”. ) . It strikes me as it is “special” it should not be grouped with the other ones. You could make it “stand out a bit” by not dimensioning it….… This is possible as although strictly (“Explicitly!”) speaking the syntax requires that the data type of each argument in a “Property Let Method thing” and its corresponding Property Get Method thing” should be the same… BUT in the case of the last special Let one I guess here it is that “aaarrrrggghhh Implicit default” is doing something that it don’t for the others


“But as Beginner … I am probably rambling in the wrong direction again..

… and if it confuses you Kyle.. I have no chance!!!