+ Reply to Thread
Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 31 to 45 of 67

Range Dimensioning, Range and Value Referencing and Referring to Arrays

  1. #31
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    . 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 #10 here
    http://www.mrexcel.com/forum/excel-q...iner%94.html?&

    .. 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 here or elsewhere I would be very grateful.

    .. Hi (Kyle ? or RomperStomper? .. 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-q...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” :

    Quote Originally Posted by Kyle123 View Post
    ….. 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.
    ………..
    Quote Originally Posted by DocAElstein View Post
    ..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...w-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:

    Please Login or Register  to view this content.
    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
    Please Login or Register  to view this content.
    .. 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-q...-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).
    Quote Originally Posted by Kyle123 View Post
    ….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-q...-liner%94.html
    myArr() = obj.HypKyle


    Please Login or Register  to view this content.
    …. 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-q...e-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 (Post #32) Next (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:
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #32
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    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



    … 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:

    Please Login or Register  to view this content.

  3. #33
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Yes, you've got it (to all the questions), you've muddled the code at the end though:
    Please Login or Register  to view this content.
    The range and the getter are 2 different things so should have their own property and return/set type (that's why you've had to use a variant). One for the range, the other for the hyperlinks, following on:
    … 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
    You can't do that in VBA since it would require adding functionality to the Range object. This would be possible in normal OOP languages through inheritance, however VBA doesn't support inheritance (not in this context anyway), you can however do something like this:

    Class myRng
    Please Login or Register  to view this content.
    Module
    Please Login or Register  to view this content.
    It's worth noting that there are few instances in Excel where using Classes is actually required (though they do exist), so it's more a convention than anything else I like them since it allows you to encapsulate code, limit scope and breaks code into smaller testable units.

    A note about "sneaking in", I did this for simplification, but it is symptomatic of a minor annoyance of Objects in VBA - they do not support constructors with parameters. In something like VB.Net or C#, you could do something like this (note, this does not work in VBA)
    Class myRange
    Please Login or Register  to view this content.
    And the loop in the earlier code would be reduced to:
    Please Login or Register  to view this content.

  4. #34
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Hi kyle,
    . Thanks again very much for the quick and very detailed reply. I am very grateful

    Quote Originally Posted by Kyle123 View Post
    ……..
    you've got it (to all the questions), you've muddled the code at the end though
    ……..
    … I assume you are referring to my explanation in the comment, not the codes??
    . I mean…
    . (… -. sorry to send you around a bit hap hazardly, … some threads of mine have merged a bit and where to post is a bit tricky to decide just now .. at post #10 here
    http://www.mrexcel.com/forum/excel-q...-liner%94.html
    . there I rambled a bit this morning and thought I had this one straight.. that rambling ties up with experimenting… changing the One for the range to type Range in the declaration causes runtime error.. in English saying something like “The definition of the Property procedures are inconsistent.”….)

    . Is the answer that: As you say
    Quote Originally Posted by Kyle123 View Post
    ……..
    The range and the getter are 2 different things so should have their own property and return/set type (that's why you've had to use a variant).
    ……..
    .. BUT the syntax still requires the type declaration to be the same so if they are different types, then variant would have to be chosen??

    . maybe if you had time to re write my 'green comment bit…..
    Please Login or Register  to view this content.
    . …..I might at least have enough to set my mind at rest on that , even if I am struggling to grasp the full Theory just now

    ….. I am doing my best to do justice to your answers and follow the rest of what you have kindly written.. but am struggling!!
    ….But I will keep at it ..just 2 very quick and very vague (Due to my understanding level) comments


    . 1) I expect again I am going a step too far.. But there may be however some relevance to some Hyperlnk.Address Spped Tests which I also posted this morning ( #90 here:
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    .. I had to break off, or rather could not finish yet my measurements for HG13.. my computer has just as I write stopped calculating, giving me a time of about 60307 seconds. Here I had one loop which was creating an output array from looking at the Hyperlinks.item(1).Addrress from an entire OneBigRange. Strangely in this case, with other codes..(HG9 – HG12) where I initially in a first loop looped in each cell as a Range Object in an Array of Range Objects and then in a similar output loop produced an output array from Hyperlinks.item(1).Addrress from each individual cell Range Objects…. It was OK , reasonable speed!!! ??!!??? Quite back to front with the corresponding codes for the Left Function... but only using an Intermediate vTemp() for .Vaues2. Hence my attempt to somehow get an equivalent "vTemp()=CustomHyperlinkObject.____" to the vTemp() = BigRangeObject.value2
    … I guess this is all pointing at a higher level of understanding again exactly how VBA is working…and you seem to be one of the few people that come close to knowing this..



    . 2) . I note you are using Keys again in MSRD as full range objects..

    On both my Left function and Hyperlink.item((1).Address codes ( L15 G15 HG15 - MSRDKeys (As Full Range Objects) ; etc. ) with an intermediate Array.
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    .. I had some strangely quick speed times, so hitting on something special purely by experimenting..
    But I am really Scratching my understand here (and do not quite know what I am saying myself!!).. ***


    ..I shall continue to examine closely your last answers, but I think this time I have really …".As a beginner I may ………have gone way above my head!!......"

    Thanks again
    Alan

    P.s. ***
    (Incidentally I did extra codes based on what I have learnt form you on object work.
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    – Their times are fairly reasonable.)
    Last edited by Doc.AElstein; 03-06-2015 at 09:14 AM. Reason: wrong Thread referrence!!

  5. #35
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Sorry, I really can't follow your so called "ramblings" , I will attempt to answer the direct questions as I see them though. Firstly, as a word of advice, decouple your desire for the fastest code whilst learning the basics. It complicates things since the fastest way of doing something is often less logical.

    Once you understand the basics, if performance is an issue, then optimise - keep things simple then refactor as necessary, premature optimisation is a waste of time.

    BUT the syntax still requires the type declaration to be the same so if they are different types, then variant would have to be chosen??
    Whilst true, what you are doing is illogical. A property of an object relates to a specific aspect of that object. If you had an object representing a person, you would not try and set their name using the shoe size property. Quite rightly a shoe size property would have a different type to a name (number vs string), the same is true of the object I used in the Example. To set a range in the object, you would expect a Range property, not a property that returns hyperlinks. A setter for the hyperlink property may take an array and write it to the range in the object (much like you can read and write values to the Range Object using the .Value property - you don't try and pass a range to it).

    Therefore you would have something like:
    Please Login or Register  to view this content.
    In summary the Hyperlinks property sets the hyperlinks of the Range, not the Range itself

    Does that make sense?
    Last edited by Kyle123; 03-06-2015 at 10:00 AM.

  6. #36
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Kyle123 View Post
    ……..
    Does that make sense?
    ……..
    . Not really.. But Thanks very much again Kyle, for the quick answer. I think I have finally really overstretched myself here this time !! I will have to pull myself back a bit!!

    But I am trying to follow your explanations and will continue to do so.
    .. Some extremely minor comments / points I have noticed

    . 1)
    Quote Originally Posted by Kyle123 View Post
    ……..
    You can't do that in VBA since it would require adding functionality to the Range object. This would be possible in normal OOP languages through inheritance, however VBA doesn't support inheritance (not in this context anyway),
    ……..
    … I have sometimes read articles were experts occasionally say “VBA is not a proper OOP Language”, not sure if this has relevance here..

    . 2) A very minor point. I am attempting to understand your demonstration and to that end have prepared this Range
    Excel 2007
    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Apfel "Pomona" Tomtenpüree-Konzentrat Vogeley GV
    2
    "Cordon bleu" vom Schwein, bofrost 'Biene-Maja' Banane-Mandel Fruchtschnitte, Evers Naturkost
    3
    "Peperonata" Paprikazubereitung Vogeley GV 'Flip' Apfel-Birne Fruchtschnitte, Evers Naturkost
    4
    5
    3-Korn-Mandel-Müsli, naturkornmühle Werz 3-Pfeffer Remoulade, Kattus/Lacroix
    6
    3-Korn-Puffer, Alnavit 3-Pfeffer-Rahmsauce, bofrost
    7
    3-Korn-Waffeln, Mini, AlnaturA 3-Pfeffersauce mit rosa Beeren "Noblesse" (zub.) Vogeley GV
    8
    9
    10
    'Maja-Willi-Flip' Multifrucht Fruchtschnitte, Evers Naturkost 1.2.3.-Steakhouse-Frites TK McCain
    11
    'Willi' Apfel-Orange Fruchtschnitte, Evers Naturkost 10 For Two, Nestle Schöller
    12
    1.2.3.-Chef-Frites TK McCain 1000-Islands-Dressing flüssig KnorrGV
    13
    1.2.3.-Frites Deluxe TK McCain 11 plus 11 Rabenhorst
    14
    1.2.3.-Frites Hot & Spicy TK McCain 11 plus 11-leicht Rabenhorst
    15
    1.2.3.-Frites Italian StyleTK McCain 13 Kräuter Essig Hengstenberg
    16
    1.2.3.-Frites TK McCain 3 Minuten Apfelrotkohl, Hengstenberg
    17
    1.2.3.-Frites Western Style TK McCain 3 Minuten Rotkohl, Hengstenberg
    18
    1.2.3.-Golden Longs TK McCain 3 Minuten Schnellkoch-Pasta, Birkel
    19
    1.2.3.-Kroketten TK McCain 3-Fit-Brot Weizenmischbrot, Kamps
    20
    1.2.3.-Rösti TK McCain 3-Korn-Flocken AlnaturA
    21


    … I then think I noticed a small Typo, Your class Module should have been named myRange rather than myRng. Further to avoid confusion I used with the Class modules you previously gave me I have used the name myRange2. So the code modified is then.

    Please Login or Register  to view this content.
    … but that is an extremely minor point. I am sure there is a wealth of info in what you have tried to demonstrate to me. But I think I ( or my current understanding ) may not be worthy of it. I will continue to try to do justice to your efforts!

    Thanks again
    Alan

  7. #37
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Hi.
    . Just Some minor further Feedback..

    . I do see that you have probably done something very clever in an attempt to answer my question . 5) from Post 31.. – Possibly something “clever” occurs when then further in the code the With End With bit is applied.

    . 1) .. I am lost as to why you are demonstrating with 3 Ranges. Is there a particular point there you wish to bring out?

    . 2) Although As I have often mentioned I have indeed seen some very strange improvements in speed with the use of MSRD Keas as full Range Object, I do not see why you are using it here

    . 3) The code is clearly applied to the Active sheet. I wanted to try to be explicit to assign it to a particular sheet to further experiment with it. But I cannot see how to do that with this code. Is it at all possible?

    . I apologies for what must seem to you to be obvious questions. Without explanations (or ‘comments) to those questions and all that you previously explained I remain totally lost. But as always am extremely grateful for your efforts. I try at least to give the best feedback I am able! Sorry I am unable to give better Feedback this time.
    . Don’t feel obliged to reply this time. You have been extremely helpful, Clearly I am stretching myself to far this time. There are too many unknowns to me here in what you have written.
    . But I, and I expect many others will benefit in the future from the inputs to this Thread.
    Thanks again
    Alan

    P.s. I do now at least have another code version for my many code over at Post #90
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    . I will call it something like HG8d KyleSetGetPair. (It “Works”, very similarly to my HG8c) I will leave it at that for a while for the sake of my sanity. I will hopefully come back in a year or two to this Thread when I have hopefully developed enough to understand a bit better your last couple of Posts here!

    Code

    Please Login or Register  to view this content.

  8. #38
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    1) .. I am lost as to why you are demonstrating with 3 Ranges. Is there a particular point there you wish to bring out?
    Not particularly, I was just putting it in context. You wanted a dictionary example, so that's what I gave you - there would be little point in using a dictionary if the ranges were continuous.
    2) Although As I have often mentioned I have indeed seen some very strange improvements in speed with the use of MSRD Keas as full Range Object, I do not see why you are using it here
    Laziness, meant I didn't have to define a unique key.
    . 3) The code is clearly applied to the Active sheet. I wanted to try to be explicit to assign it to a particular sheet to further experiment with it. But I cannot see how to do that with this code. Is it at all possible?
    Just change the range, this only serves as an example and shouldn't be used for speed testing, it's slow for looping through arrays

    Finally:
    I have sometimes read articles were experts occasionally say “VBA is not a proper OOP Language”, not sure if this has relevance here..
    Yes, that's exactly what they mean

  9. #39
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Hi
    . Thanks Kyle. Appreciate you coming back again here.
    . I probably have confused the issue referencing different Threads etc. I was not particularly looking at dictionaries here, but my fault – I am confusing myself switching between the Threads!
    . But Thanks for clearing those points.



    .....


    Quote Originally Posted by Kyle123 View Post
    ......

    Just change the range, .........
    . Very sorry to nerve, bit I am struggling with my last question 3. I think this is because I am struggling generally to understand exactly what is going on and so I am unable to see clearly which things are actually ranges that I could modify to specifically refer to a particular sheet).

    . If you have time, could you modify your module and Class codes from #33 ( Note the Typo I mentioned in #36 – I think you meant to call your class code myRange, not my myRng? ) (As I noted I am calling it myRange2 as I have a precious class module myRange kindly given by you earlier ))

    . Sorry to nerve. I will not trouble you again here. I will go away and read up on the Theory and come back again when I have time to this Thread and try to fully understand those last “Set Get” Pair.
    . If you had a chance to modify them to apply to some specific sheet, say “KyleExcelForum” then I have it working fully as I would like for now. Maybe that extra bit will also help the Penny to drop with me to understand the codes a bit better.

    . Alan

  10. #40
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    As requested, simply:
    Please Login or Register  to view this content.
    Any clearer?

    Nothing needs changing in the Class (aside from my typo in the name)

  11. #41
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Kyle123 View Post
    ......
    Any clearer?
    Thanks for the quick reply.... I just did this myself..

    Please Login or Register  to view this content.
    ......I'll take a look at yours now!
    Thanks Again

  12. #42
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Hi

    . I think the penny may have suddenly dropped. Seems fairly obvious now. There were lots of small points combining to throw me off.

    . How does this sound. (As an explanation of your codes (class and module) initially given in Post #33 in this Thread

    . Comparing with your class module myRange from Post #2
    http://www.mrexcel.com/forum/excel-q...-liner%94.html


    . and my “Hyperlinks” property codes from Post #5 there….
    http://www.mrexcel.com/forum/excel-q...iner%94-2.html

    . and further codes H8 from post #90 here:
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html


    …….
    . the new thing introduced by you in Post #33 was making a **quasi “Range” Property using “Set Get Pair” in a class module. Having Set that it could then be used by additionally a second Get for a Hyperlink property which mirrors closely the various “Hyperlink” Property Gets I did….

    .. My first one used for its range that given in your Class_ Initialize Sub, so had no Let
    .. My second one used the more common “Let Set Pair”
    .. The Third, from you utilizes the range from your “Set Get” Pair. ( As you demonstrated in your module code that gives an additional way to get at things like .Value2 with that “range” property
    ( ** In fact it is normal range rather than quasi… again you are referencing the sheet each time)

    . I have it now I think. I very much confused things referencing different Threads. I will some time in the future give those 3 versions clearly, with summarizing comments etc, for me and anyone else following or referencing this Thread in the future.
    . Thanks for sticking in with me on this one.

    Alan

    P.s.


    One very last very minor question. One of the many small things that helped throw me off is that most people seem to write the Let (or Set) in a “Let (or Set) Get Pair” Second. Logically that seemed to me to be upside down. In the Class module you must Let or Set before Get has what it needs. Indeed in your last Class module myRange2, the Hyperlinks Property requires that p_Range obtained through the Set

  13. #43
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,101

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Glad it's clicked, thin kyou were just biting off more than you could chew

    Quote Originally Posted by Doc.AElstein View Post
    One very last very minor question. One of the many small things that helped throw me off is that most people seem to write the Let (or Set) in a “Let (or Set) Get Pair” Second. Logically that seemed to me to be upside down. In the Class module you must Let or Set before Get has what it needs. Indeed in your last Class module myRange2, the Hyperlinks Property requires that p_Range obtained through the Set
    This makes no difference at all, VBA subs/functions etc don't run sequentially one after each other, they run in the order they are called. As long as the range is Set before trying to call it, it doesn't make any difference which order the code is written in. I could have written the below that does the same thing, but does not expose any Get/Set:

    Please Login or Register  to view this content.
    Though not good practice the above will work. The point here is that using Getters and Setters allow you to sanitise input to variables and allow you to create read only Properties (like Hyperlinks)

    My original point was that there is nothing intrinsically special about the Value/Value2 properties etc, they're just properties of the Range object. My example was to try and explain why there is no Hyperlinks property that you can get as a one liner. None of this really requires a class, the below is perfectly permissible and probably easier to understand:
    Please Login or Register  to view this content.
    Last edited by Kyle123; 03-09-2015 at 08:32 AM.

  14. #44
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Quote Originally Posted by Kyle123 View Post
    Glad it's clicked, thin k you were just biting off more than you could chew
    .....
    … Your right!!...

    . and I confused the issue referencing different Threads: Your last two codes are very similar to those of the Public Function used in some code alternatives considered at post #90
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    and in the File Dropped off by me at post # 32 here
    here again for completeness… ‘' Commented to death by me!!!


    ….
    Please Login or Register  to view this content.
    …….

    And a typical calling code used in Post #90
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html


    Please Login or Register  to view this content.

    .. so all tying up


    . Thanks again for persevering with me on this one.
    . Sorry again for confusing things.
    . Much appreciated
    . Alan

    . P.s. I think your Dim temp As Range in your Public Function is doing nothing?
    Last edited by Doc.AElstein; 03-09-2015 at 11:08 AM.

  15. #45
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Range Dimensioning, Range and Value Referencing and Referring to Arrays

    . I am almost afraid to ask… Is Property Set rather than Property Let used in your last example, as this is simply the syntax for returning an Object rather than a Property . Or have you a reasonably “Alan Understandable short ‘' Comment to explain that. ( If not I fully understand !!! )….
    …. just biting off more than .. I … can chew
    .....
    Alan

+ Reply to Thread
Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1