+ Reply to Thread
Results 1 to 67 of 67

Range Dimensioning, Range and Value Referencing and Referring to Arrays

  1. #1
    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

    Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Range Dimensioning, Range and Value Referencing and Referring to Arrays

    Hi,
    . I have been learning VBA for about 6months now but still get a bit bogged down on getting to grips with understanding some basic Range assigning and Definition. I need help from someone that understands the ‘core’ as it were,. of how the computer or VBA actually works..
    . This post and the specific questions here follows on from a question that came up coincidentally yesterday here Post #10 - # 14
    http://www.excelforum.com/excel-prog...n-a-range.html
    ( In addition I think it is appropriate to reference two threads in which I have a lot of great help to get me this far in this general Theme)
    http://www.mrexcel.com/forum/excel-q...nomaly.html?&&
    http://www.mrexcel.com/forum/excel-q...-anomaly.html?
    )


    . If I may “Summarize” the storys so far as I see it, as it leads on nicely to my current problem:….

    . Say I have some arbitrary table in a spreadsheet, say in the Range C4 to E6. There are various ways to “capture” as it were this data efficiently (in an Array)**** for use in further data manipulation, sorting, re-arranging etc. etc.

    . A very common way frequently used and I think correctly and explicitly explained in the ‘green comments would be:

    Please Login or Register  to view this content.
    So my first question 1)

    …….as in this case we know the Array dimensions, why does the code below not work?

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

    My second question 2 a)

    . Here is what I thought would be a direct comparison with the first code except that I am trying to “Capture” in an Array an Array of Ranges. But I t does not work .. why?

    Please Login or Register  to view this content.
    .2 b) I note that this does work, but I had to comment out the Ubound lines or it errored there****

    Please Login or Register  to view this content.
    …….****.could the answer be then that I have fooled myself into thinking that I have an Array of Ranges, but in fact I have an Object, that is to say a Range Object.

    3) My final Question (Bit related to 1 and 2)

    This works as an alternative to the very first code…..

    Please Login or Register  to view this content.
    …… I note the results look exactly the same in the Watch Window for ScreenThings here as for ScreenThings() from the first code. So wot is going on here? - .. is this maybe just a case of VBA “guessing right” and returning an array. I note that most Profis would use this last code rather than the first.

    Many thanks.
    Alan Elston

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    1. You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic (yours is fixed) and the two arrays are the same type.
    2. Yes, what you get from Currentregion is one Range object. (a range object may contain one or many cells)
    3. The effect is the same as long as there is more than one cell returned by CurrentRegion; if not, the first one will fail whilst the second will not, but you will not have an array, only a single value.
    Remember what the dormouse said
    Feed your head

  3. #3
    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 roaringStomper..


    Quote Originally Posted by romperstomper View Post
    1. You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic (yours is fixed) and the two arrays are the same type.
    …..
    . Ok, so it is down to some rule….. “You can only assign one array to another directly (i.e. without looping) if the destination array is dynamic “ which I did know about. I (I fixed mine with the dimensioning). Got it. Thanks.


    Quote Originally Posted by romperstomper View Post
    ….
    2. Yes, what you get from Currentregion is one Range object. (a range object may contain one or many cells)
    ……..
    . Thanks Rory for the confirmation….

    …Just for anyone else interested.. check this out in the Watch Window..

    a)----Firsts look at ScreenThings that comes from my code in 2b) “ArrayRangeRangeCapture2” (which I should re-name “ObjectRangeRangeCapture)”,
    ..then:
    b)- check out and compare ScreenCapture that come from this code:
    Please Login or Register  to view this content.



    Quote Originally Posted by romperstomper View Post
    …….
    3. The effect is the same as long as there is more than one cell returned by CurrentRegion; if not, the first one will fail whilst the second will not, but you will not have an array, only a single value.
    . Got it. - Great, Thanks.


    . Thanks… Rory.

    ..Alan



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





    P.s. Just to get it straight in my head, and to help anyone else interested in understanding wot is going on here.. Here is a macro to demonstrate the 3 “Screen capture” ideas:- 1) Array of Values 2) One Range Object and 3) Array of Range Objects.
    . I recommend if you use it for demonstration purposes then
    .1) stick some random values and a bit of random formatting in range C4 to E6;
    .2) set a watch in the Watch Window on CaptureArrayValues, CaptureRangeObject and CaptureArrayRangeObjects;
    .3) put a stop at End Sub 'SpreadsheetTableCaptures() (so you do not lose the info in the watch window);
    .4) run the macro and;
    .5) compare the different things in the watch window.


    Please Login or Register  to view this content.

  4. #4
    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 a very quick append here to help stop anyone getting caught out for the case of when using my Capture Screen Value Method in conjunction with the Currentregion to “capture” an array of values for a range which may of only have one cell being occupied. (This might occur, for example as it just did by me as I was looping to capture many arrays of different dimensions, one of which just happened to be just one cell…..
    .
    . Despite the general idea seeing sound of assigning an array of only one value, as demonstrated by this ( which ‘works’)..

    Please Login or Register  to view this content.
    …. For some strange reason in the case of only one cell being occupied, the original lines of mine

    Please Login or Register  to view this content.

    ………. will give a type mismatch error.



    … just a small point to be aware of…

    Alan

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    It's not a strange reason.

    In this case, this:
    Please Login or Register  to view this content.
    is effectively equivalent to this:
    Please Login or Register  to view this content.
    which you wouldn't expect to return an array.

  6. #6
    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 for that. …

    … I guess it is down to knowing exactly how Excel does things.

    … Looking very stupidly and naively I could reply then why should this

    Please Login or Register  to view this content.
    .............................return an array? Presumably Excel checks to see if it has valueS rather than a value. It decides in the case of more than one value to produce an array. Otherwise it just returns a value. Sensible and reasonable I suppose as otherwise it would be creating one dimensional Arrays all other the place..!!. (And wot else should it do with more than one value?? – concatenate them in one cell? – obviously stupid!!)
    . I was thinking I guess the () tacked on the end might somehow “coerce” Excel to give an Array, albeit of size one!!. I guess that is a stupid argument as Excel does all of its “deciding” on the right hand side of the = , then tries to give that to wot is on the left of = , so then the mismatch comes

    . I am a bit slow. Thanks for putting me right (Again)

    (Sorry if it seems annoyingly obvious to you!)

    Thanks again
    Alan

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    Not at all - we all go through the same learning process!

  8. #8
    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 think this question may be relevant and appropriate to Post here as a Follow up question here. I think it is a good follow up to the 3 main methods for “capturing „a multiple a cell Range to an Array for further use / manipulation within VBA…..

    …..is this possible, and if so any previous knowledge or codes on it anywhere??

    Capture Large Spreadsheet Range as Range Object – Split the Object within VBA into an Array or Individual Cells as Array Objects (identical to those obtained by looping to bring in the cells as Range Objects into an Array. )

    . . One of the main points would be to keep the interaction with the spreadsheet to a minimum, ideally just the one line “Capture” as Range Object discussed as one of the main 3 “Range Capture” methods given at the end of Post #3 developed in this Thread.

    . This idea has been troubling me for some time and I have spent many hours if not days studying in great depth in the watch window the makeup of both A large Range Object of multiple cells and individual Cells within that range as Range Object. I believe most, if not all, info needed is there.

    Question 1) Can someone in the Know confirm that all the info is there?

    ……………………
    . I should like to develop a code to achieve the above Goal. Through studying and working in amongst these Threads ….
    http://www.excelforum.com/excel-new-...t-reorder.html
    http://www.mrexcel.com/forum/excel-q...objects-2.html
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    . …………. I believe could start doing it in a not to difficult way but maybe very tedious involving having very large arrays and or possibly in Microsoft Scripting Runtime Dictionaries of the various Methods Properties and Under Objects etc. as Arrays to then assign in an ordered item list to individual Cell Object Array indices etc…through some looping which in VBA itself away from interaction with the worksheet could be fast…. Questions 2:

    . 2 a) Can anyone point me in the direction of a list of all these items which are copyable – (so directions to the Object Library are I think inappropriate – a .jpg screen shot (or rather many of them!!) would not help me much further .. I think??)

    . 2 b) Has anyone out there already seen anything along the lines of what I am considering attempted before...Possibly there is some logical sequence (Maybe just Alphabetical!) that I should bear in mind to simplify the sequence of operations.. I did develop in the last code in
    http://www.excelforum.com/excel-new-...t-reorder.html
    An item reordering sequence to reflect the Rows then Column convention of VBA, but I expect a profi could advise of other important ordering of relevance to bear in mind ?

    . Possibly it my idea is just is not a realistic thing to consider.. If someone knows that for sure
    Question 3)
    then could they advise. At least that would be a help to avoid me looking further over the weekend (or next weeks)

    . I am still relatively inexperienced in VBA so any suggestions or comment on the merits of my idea would be very welcome. Please feel free to question my sanity at thinking of such an obscure idea if you wish to do so – any feedback is welcome.

    Many Thanks for taking the time to read this!!
    Alan..

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    I don't understand what the point of this would be? Whenever you interacted with a range object in the array, you would be interacting with the worksheet, so there would be no benefit to having an array of range objects rather than one.

  10. #10
    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

    Quote Originally Posted by romperstomper View Post
    …. Whenever you interacted with a range object in the array, you would be interacting with the worksheet, so there would be no benefit to having an array of range objects rather than one.
    . OK. Thanks. I was not 100% sure about that. I was thinking one could hold all that information exactly as it is, (or becomes after referencing) in an array.
    . So
    . 1) are we saying that what I have in an actual array of range Objects, (Or alternatively stored in a Microsoft Scripting Runtime Directory) is not really data but to a whole lot of “references” or “path directions” which will always take us into a Spreadsheet.
    . If this is so, I do not have then the advantages I thought there could in not referencing / interacting so much with the Spreadsheet.


    . Some of my recent speed investigations however indicated that referencing by .Value or (even .value2 ) in the case of individual cell range Objects might have speed advantages over referencing the .Value2 () from a large single Range object of multiple cells. (Because possibly of all the extra () stuff etc. )
    . The question is possibly somewhat academic, - I was just wanting to complete some time tests I am currently doing
    http://www.mrexcel.com/forum/excel-q...ml#post4074287
    by trying a last alternative which did the entire range capture in one code line as a single Object, and then somehow I was able to convert that into an equivalent array as obtained by looping them in one by one.

    ..Maybe I am asking
    2 ) if it is at all possible to do any conversion within VBA that some how does a quasi “screen shot” of whatever is actually “written “ as it were in code lines or whatever in that Large Range Object and without using the commands to do their normal purpose of referencing, simply split them up to put them in a form identical to how they are held or appear in an Array produced by looping them in one by one..


    Quote Originally Posted by romperstomper View Post
    I don't understand what the point of this would be……
    . My thinking behind was that if this could be done within VBA quickly, then having that initial one liner Large range capture rather than looping in one by one could have speed advantages.

    . I hope that makes sense. (or at least my logical, if perhaps somewhat naive due to my lack of computing knowledge..)
    .

    Alan
    Last edited by Doc.AElstein; 02-13-2015 at 10:15 AM. Reason: missed a bit

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    What you have in the array depends on how you populate it. If you assign the Value/Value2 property to the array then you have an array of values, not Ranges. If you need to process multiple values, it should be faster to load all of them into an array, process that and then repopulate than it will to read/write one value at a time.

  12. #12
    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

    Ok ,
    I think I grasp all that and what you are suggesting…
    . 1). So you suggest best method would be
    …. (Quasi Code)
    .. . a) A one liner input for whatever Property I am interested in. of the form..
    …… Dim Array() as variant
    ……. Let Array() = BigRange . WoteverProperty

    … b ) do any processing maybe in a VBA loop to get an OutputArray()

    .. c ) Output One liner
    OffsetBigRange.value = OutputArray()

    . 2a) So for every Wotever property I am here having to do extra one liners so lots of one liners, rather than a one liner complete range, - but I grasp the point t that by then having this Big range Object I need to reference for every .WoteverProperty the Spreadsheet again. So no advantage…
    ( and I have finally accepted that there is no “one liner” to output a Big Range as a range object – the nearest is Copy paste – but that again is referencing the spreadsheet!)
    . 2b) Just that last niggley point of possibility to do a quick VBA conversion of a Large Range Object collection in their “machine code” form to an array of individual cell range objects in the “machine code” form. Then the point is I can Knock out that “extra level of direction” () bit which my speed tests suggest form same weird reason mostly takes longer then when VBA guesses right and does not have the extra bit or direction given…. (I note that even in some simple codes , including that extra () when it is not needed sometimes increases time taken….)

    Alan
    .

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    1) For properties other than value/value2/formula (and sometimes formulaarray), an array is no real use.

    2a) I have no idea what you mean I'm afraid. You can output a range to a Range variable very simply using Set.
    2b) Ditto. I see no benefit in having an array of individual range objects versus one Range object. Either way you will be accessing the worksheet each time.

  14. #14
    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

    .1 ) Ok.

    .2a ) I Understand what you mean fully. . Sorry I just explained badly what I meant there.
    . I simply meant if I have an Array in VBA (Or a Microsoft Scripting Runtime Dictionary) full with cells as Range objects brought in by looping then I cannot stick that entire range in the spreadsheet in one go (What I agree there is no point in doing anyway, other than when I once wanted to do that to confirm in one line that I had got range objects in my dictionary.) . Even the individuals Ranges cannot be directly stuck (Pasted) in. You must Copy that Array item or dictionary item and paste that in. Again with no advantage over just doing that in the Spreadsheet
    …. I sorted that out in my head in pretty orange and red bits in my code in post #3 here
    http://www.mrexcel.com/forum/excel-q...-objects.html?

    .2b) …
    Quote Originally Posted by romperstomper View Post
    …. Either way you will be accessing the worksheet each time.
    . it was just that having to access the worksheet through
    .Value () (___) in the Large Range Object of multiple cells
    Rather than accessing the worksheet through
    .Value (____) for an individual Cell Range Object
    .. seemed to often make things a bit slower. ….

    Thanks
    Sorry to nerve (as always)

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    There's not a lot of point in using .Value () (___) as I mentioned previously. You should store the array in a variable and then access elements through the variable. Otherwise you are accessing a large range on the sheet each time in order to get one value which makes no sense.

  16. #16
    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

    .. Ok. Thanks,
    That makes sense.
    . I just had not seen an improvement in the time on that mod yet.
    . ...
    ... to that end i just looked a little further...

    In Post # 20 – Post # 24 here:
    http://www.mrexcel.com/forum/excel-q...objects-3.html
    you explained that mod to store the array in a variable .
    . applied to code L13_RangeObjectCapture here Post #87
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    is not giving me any time improvement for this code which works with the large Range Object of Multiple cells and is by far the one that take the most time so far. Hence still my obscure idea to change that large Range Object of Multiple cells somehow to the equivalent Array of elements of Cells as individual Range Objects

    Here the codes original and modified

    Please Login or Register  to view this content.
    .. But look, Rory, I am asking you to look at much to much here referencing Posts all other the Place.
    . You have been much more than patient and helpful and I have learnt so much already from you.

    . The penny will drop. I do not wish to annoy you any more on this.. My idea is probably totally impractical anyway.

    . Very many thanks again.
    . Have a good week end. I will keep on amusing myself for a while on this!!
    Alan

  17. #17
    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

    EDIT: I have seen my stupid mistake on that last one!!
    ….. L13c_RangeObjectCapture …

    Please Login or Register  to view this content.
    ……. Spot where the idiot put
    Let vTemp =
    In the second of the first two codes. Version c is now one of the fastest!!!!!

    . Still got my weird idea in the head….. but after the improvement in this last code to the speed – it is extremely unlikely to give any advantage. So even I may forget it (And save a years work!!)

    . Amazing Rory. (This application in the above code of the intermediate variable vTemp is not exactly the same but very similar to how you originally presented it to me) ... Your persistence with me has got me at lot further anyway again..
    . Alan
    Last edited by Doc.AElstein; 02-14-2015 at 06:46 AM. Reason: typo

  18. #18
    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

    Code For two Questions in next Post (#19)… It is basically for demonstration purposes of Questions in next post (#19)

    . It works on a lot of Rows in Column A to give an output in Column D (The Psuedo Code is:
    Range D = Left ( Range A , 4 )
    .
    . The code is working on up to 33928 Rows.
    . Input and output of this Form..


    A
    B
    C
    D
    19
    Range For Input
    Output Value
    20
    21
    Apfel
    Apfe
    22
    "Cordon bleu" vom Schwein, bofrost
    "Cor
    23
    "Peperonata" Paprikazubereitung Vogeley GV
    "Pep
    24
    "Pomona" Tomtenpüree-Konzentrat Vogeley GV
    "Pom
    25
    'Biene-Maja' Banane-Mandel Fruchtschnitte, Evers Naturkost
    Bie





    Code:


    Please Login or Register  to view this content.
    '_- 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 )

  19. #19
    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 romperstomper (or anyone else kindly taking the time to looking in to the latest ramblings of an Excel inflicted idiot)

    …… I am afraid I am back again here. I have the last stuff 98% clear now. If I can clear up the last 2% that is nagging me then I will hopefully most just come back here with some useful contributions (rather than questions) with some codes I have “waiting in the wings..” Comparing different range referencing and data thereof manipulating etc…
    . Anyways I will try and simplify and summaries my two questions…(Note Maybe the answer to Question #2) automatically answers #1) at the same time…!?!...
    . Here is a sample code , simplified to try and help explain my problem…. ###I appreciate there are many unnecessary and unneeded steps in the program. They are simply there to help clarify my two questions.. (###And I have it finally in my thick head that working with values generally is both more efficient and easier to get out… and I have many hours (Or days) of Speed tests to prove that!!!
    (. The Full code is Shown in last Thread due to Excel Forum Editor size constraints..)

    . It works like this
    .a) Captures a whole Range as a Big Range Object with all data that entails in one neat “one liner”...
    .b) Does another neat one liner to pull out a particular property from the Range object.
    ( ###Note I realize a) and b) could (and should!!) be replaced by one line in this simplified example… for demonstration purposes here I am doing it in this way..?!??!
    .c) In a loop an output Array is produced within VBA. For demonstration and comparison the vTempIntermediateArray is and / or is not used within the loop.
    .d) Finally and not important to the questions in hand the Array of Values is outputted in the typical one liner exclusively allowed (I think?) to values only

    (Some processing times for different code line variations in the loop for producing the output are shown in the comments and table below. These refer to 2 sheets with approximately 1654Rows and 33928 Rows (of Hyperlinks in this case.) )

    (Full Code is in last Post #18)
    Extract: (Pseudo Code)

    General outlook of Code Line (pseudo code Line)’: Seconds 1654Rows ; Seconds 33928Rows

    1 arrOut(___) = Object.Value2(_________) ‘: 3.85 ; 1651.5
    'Works

    2 arrOut(___) = Object.Value2 () (_________) ‘: 3.85 ; 1667
    'Works

    3 arrOut () (___) = Object.Value2(_________) ‘: 3.85 ; 991.5
    'Returns Empties

    4 arrOut () (___) = Object.Value2 () (_________)’: 3.85 ; 1696
    'Returns Empties



    5 arrOut(___) = vTemp(_________)’: .018 ; 0.325
    'Works

    6 arrOut(___) = vTemp () (_________)’: --- ; ---
    'wont work ????? #1 Error 9: Index out of valid Range

    7 arrOut () (___) = vTemp(_________)’: .089 ; 30.5
    'Returns Empties.

    8 arrOut () (___) = vTemp () (_________)’: ---- ; ----
    'wont work ????? #1 Error 9: Index out of valid Range



    ????? # 1)

    . One last bit I do not quite get is that this new vTemp(_____) will not tolerate such an assigning
    vTemp () (______)
    … … I have been happily for learning purpose including the extra () where it is not needed and seeing, for example occasionally increased times as a result. All suggesting to me that it is some sort of explicit that by the implicit default almost always gets it right and so therefore is only occasionally needed….
    ('.. because of how VBA “works”, This extra () will ensure that indices are always returned to the resulting array (and not occasionally, for example, arguments ) by any following method or property, or passed to any method or property)

    ????? # 2)
    I had mistakenly thought this
    Let arrOut(rws, 1) =
    .. and this
    Let arrOut () (rws, 1) =
    Were the same. (Again Thinking that it is some sort of explicit that by the implicit default almost always gets it right and so therefore is only occasionally needed……etc..) But in fact I was mistaken. The second sort of does not work and seems to mostly return an empty arrOut
    .. so I am not quite “with it” on those two points yet.. ?? (I do grasp that it is not always needed and mostly I thought understand why. I do not understand why it messes things up sometimes by being there ?


    … I realize “As a beginner I may be going a bit above my head!!.”....but I have a problem going further without at least having a bit of understanding of wot and why…
    …..
    . There is no rush in giving an answer here. I would be very grateful for explanations to the differences in workings in as much detail as possible to the 8 colorful code lines. And I think it could be a very useful contribution to understanding the general Theme of Range Referencing to Arrays.
    . I am answering the questions myself in parallel with endless empirical measurements, but another angle from someone in the know would be very welcome..


    .Many thanks again
    . Alan..

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

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

    It works when using .Value() since .Value is a function/property. A function that returns an array - the same thing is true with other functions that return arrays:
    Please Login or Register  to view this content.
    All you are really doing is accessing the returned array without assigning it to a variable first.

    vTemp isn't a function, it is an array - it doesn't return anything so additional parentheses will do nothing
    Last edited by Kyle123; 02-18-2015 at 10:43 AM.

  21. #21
    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

    Thanks Kyle.
    . An extra angle from someone in the know is always extremely helpful. I think it is helping me to finally get this one..It is helping me get the full picture. And it does tie up with an explanation already given to me. (Just hearing something similar helps to knock it in..)

    Quote Originally Posted by romperstomper View Post
    You do not ever need to do this:
    Please Login or Register  to view this content.

    The parentheses are only necessary for the .Value2 call to indicate that the last part is intended to be indices to the resulting array and not arguments passed to the Value2 method itself.
    … a couple of follow up questions..

    . 1 ) Is my last Red Code line working by Luck (The implicit default getting it right?). The original extra () thing came up because in something similar here (Post #13)
    http://www.mrexcel.com/forum/excel-q...bjects-2.html?
    Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2 () (rws - sr + 1, 1)

    It would not work without it?
    .
    .
    .
    .

    . 2 ) Your demo was good (Once I got it in my head what you meant..)…

    Please Login or Register  to view this content.
    … so how does this sound for an explanation here ?:

    The second parenthesis (1) (2) (3) (4) bit is the usual indicie for the Array. The first parenthesis just “belongs” to the function/property. By luck in 1) VBA just guessed right with
    CapturedRangeObject.Value2(rws - sr + 1, 1)
    But by bad luck did not guess right with
    Let arrOut(rws, 1) = dicLookupTable.Items(rws - sr).Value2 (rws - sr + 1, 1)
    ….. ?

    (P.s. Here – I saw some interesting speed improvements sometimes when I relied on the implicit guessing.. so sometimes it is maybe worth a try!!)

    . 3a) I think if I let it go painfully through my head for a week I may realize why the extra () on the LHS returns empties.. If you have time can you spare me the pain and explain that one.

    . 3b) Probably related to .3a) When I give vTemp additional parentheses it does not do nothing but rather gives Error 9: Index out of valid Range. Again if I go away for another week experimenting putting various indicies in there it may get me further to fully understand…. Any ideas on that one..(While I now play around experimenting….



    Thanks
    Alan..

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

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

    Scrap my earlier post, I'm wrong but the behaviour was right

    I'd never tried it before, but it appears that an specifying an indices immediately after an array accesses the given element - this acts as a readonly getter, not a setter. So your code simply doesn't work as implicitly accessing an array in that way is read only.

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

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

    Since it's a read only getter,I suspect the compiler interprets:
    Please Login or Register  to view this content.
    As:
    Please Login or Register  to view this content.
    Dodgy test to prove:
    Please Login or Register  to view this content.
    Last edited by Kyle123; 02-18-2015 at 01:09 PM.

  24. #24
    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
    Scrap my earlier post, I'm wrong but the behaviour was right

    I'd never tried it before, but it appears that an specifying an indices immediately after an array accesses the given element - this acts as a readonly getter, not a setter. So your code simply doesn't work as implicitly accessing an array in that way is read only.

    …. I am not quite with it here…

    … I thought we were all a bit in agreement with the idea…
    .". The parentheses are only necessary for the .Value2 call (.Value is a function/property. ) to indicate that the last part is intended to be indices to the resulting array and not arguments passed to the Value2 method itself.
    . vTemp isn't a function, it is an array - it doesn't return anything so additional parentheses will"
    ……………………… ah I see Yous have have just answered again… i will check that out

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

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

    .Value / .Value2 is indeed a function/property and my answer was correct, however you can use that syntax on any array as a getter, just not a setter

  26. #26
    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
    .Value / .Value2 is indeed a function/property and my answer was correct, however you can use that syntax on any array as a getter, just not a setter
    ?? does that mean
    = vTemp() (____)
    is "working", but we are not quite sure what it is doing
    and
    Let arrOut()(_____) =
    is also "working" but we are not quite sure what that is doing either??


    ..................


    .... I think it is still not clear if this workijng
    = CapturedRangeObject.Value2(rws - sr + 1, 1)
    and this not
    = dicLookupTable.Items(rws - sr).Value2 (rws - sr + 1, 1)
    is still down to the luck of VBA guessing right sometimes??

  27. #27
    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 think I will leave my thoughts on the () (_____) idea and the Thread as unsolved for a bit. Possibly some of the codes and experiments** I am doing in the next day or two may clear it up in my mind a bit. Then I will report back and possibly mark the Thread as solved..


    . I think for the time being a consolidation of the thoughts here is something like the following listed .1) – .3) below, (but it is not telling clearly the full story (for me) yet..)…..

    . 1) After a Property or (as in the case of .Value2) a Method, an extra parentheses may be needed. This is some sort of direction to make sure the next bit (______) is taken as indices (I struggle to see what else it could expect in the case of.Value2.. I cannot see any arguments for value2, but maybe VBA just looks often for some arguments (everywhere / anywhereas it feels like!?) )

    . 2) Sometimes you do not need it, and maybe VBA is just then guessing right with its implicit default. It seems initially to me that it is Always worth checking that if speed is an issue. Some preliminary experimenting suggest to me that maybe the implicit default is sometimes faster.. (.. I was wondering if there is a parallel with the idea of relying on an implicit default with (Pseudo Code) a quicker working one liner
    Copy Destination:-
    . compared to a sometimes slower 2 line equivalent
    Copy
    Paste Special Arguments:=
    ….….?.)
    … see RickXL Post #6
    http://www.mrexcel.com/forum/excel-q...-formulas.html
    …(In this case Rick explained that a bit is missed out by the Copy destination:-(The Clipboard here) ).
    )

    --- maybe then my problem with this working
    = CapturedRangeObject.Value2(rws - sr + 1, 1)
    but this not
    = dicLookupTable.Items(rws - sr).Value2 (rws - sr + 1, 1)
    ….Is that VBA has a default path / way for a Range Object, but does not have this for a Microsoft Scripting Runtime dictionary .item … (..just a quick note here.. I am seeing some amazing quick results when taking the default way for a Microsoft Scripting Runtime dictionary .key – when the key is made to be a full Big Range in conjunction with an VTempIntermediateArray of Range Objects….see Code G15 variation (34 G15) here..Post #88
    http://www.mrexcel.com/forum/excel-q...vlookup-9.html
    ….
    ….?.)


    . 3) There is some thought that for a non dynamic array the first ( ) will always expect the indices of the array in it. So that kind of clears up why my vTemp not liking a () (_____). What exactly happen when you do a () (___) for an Array is not yet clear (to me)

    Alan.

    **P.s. There may be some “Stuff” here worth a quick read….
    https://fastexcel.wordpress.com/?s=Text+vs
    Last edited by Doc.AElstein; 03-02-2015 at 11:14 AM. Reason: changed light grey to dark grey!!!(for Kyle!!)

  28. #28
    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, if you happen to look in here again..(Or anyone else dropping by…)

    … I’m afraid I am still trying to “set” or “get” a few things clear in my mind…

    . 1) …..”getter” or “setter)
    . …
    Quote Originally Posted by Kyle123 View Post
    .Value / .Value2 is indeed a function/property and my answer was correct, however you can use that syntax on any array as a getter, just not a setter
    . Could you elaborate a little on that? Maybe you are answering one of my previous questions there, but I am not too familiar with what the term setter and getter is saying here.

    . 2) .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 note that .Value is not to be seen in the Watch Window for a big Range Object. .Value2 is. So maybe .Value is a Method which is used to get something similar to .Value2 which is then maybe a Property?. Charles Williams is not sure and did some experiments like I did to try and find out. He found that Value2 is always better and quicker especially when used with the variant Array idea similar again to what I have been finding. So maybe it all ties up.
    ( I expect you 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!! )

    .3) A quick yes to this would do. Have I been continually fooling myself into thinking I have / can “capture” a big Range with all it’s info in it with…

    Please Login or Register  to view this content.
    But in fact I have nothing but a whole lot of “Directions” or “paths” or “references” which always take me to that Range and hence result in an interaction with the worksheet.?
    (…I have been told this I think a few times, for example..
    Quote Originally Posted by romperstomper View Post
    Whenever you interacted with a range object in the array, you would be interacting with the worksheet, so there would be no benefit to having an array of range objects rather than one…
    … .
    … but some of my weird experiments have turned up some anomalies… … using an intermediate step with a code which uses an array of range objects but stores them as full Objects as Keys along with an variant itermediateArray of range Objects almost works as fast as when ignoring this intermediate step which otherwise greatly increases times.. I have no idea what use this could be.. … yet!!.
    ….?.)


    . No Rush on any answers here, rather an in depth answer should anyone have the time.
    . Thanks, sorry for continual questions here, I hope to clear this one up in my head soon!
    Alan
    Last edited by Doc.AElstein; 03-02-2015 at 05:01 AM. Reason: darker shades of grey....

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

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

    1. Getter Setter is really Class/Object terminology, it just means whether you can set or get a particular value, for example:
    Class1
    Please Login or Register  to view this content.
    Module
    Please Login or Register  to view this content.
    Here we can get and set/let the value of obj. Try commenting out the Let property and try running the code again


    2. It's a Property according to the Object browser

    3. Yes, you just have a reference/path to the range on the worksheet, so you will still access the worksheet. Value/2 returns an array which is why is is much faster to work with.

  30. #30
    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 very much for the quick and very detailed reply. I realize I am laboring a few points in my quest to understand, but as things look with this stuff they are hardly learnable form books (at least some points), so I am very grateful for your continued help and patience here and in other Threads



    Quote Originally Posted by Kyle123 View Post
    ..1. Getter Setter is really Class/Object terminology, it just means whether you can set or get a particular value, for example:
    Class1
    …………

    Module
    ……

    Here we can get and set/let the value of obj. Try commenting out the Let property and try running the code again
    ……..
    . I am clearly way out of my depth here. I understand nothing… but I will keep at it… I have at least for the first time in my life made a “Class” Module… I have done the experiment you suggested and am making further experiments to try to understand what you have said here..

    . If you could spell out a bit clearer in more basic terms what you are saying I would be grateful. But please do not put yourself out. I expect I am way out of my “Class” here. I only have a very basic Idea of Objects and Classes. I did watch a you tube video on this some time back…
    https://www.youtube.com/watch?v=Cvug...2mkJC&index=68
    I guess I need to watch that again a few times when I can and then look again at what you have kindly written…..

    ………………………..
    Quote Originally Posted by Kyle123 View Post
    … 2. It's a Property according to the Object browser
    …….
    . Thanks. These things are not as obvious as they should be in books or the object browser. So It getting the answer as much as possible to a seemingly trivial question from a Profis is very helpful. And indeed necessary. Clearly such things are becoming less and less precisely defined as we “lose control” of how our software is working due to it’s complexity.
    . BTW (I assume you are saying both Value and Value2 are properties.?.)

    …………………….
    Quote Originally Posted by Kyle123 View Post
    … 3. Yes, you just have a reference/path to the range on the worksheet, so you will still access the worksheet…...
    .. Great. I had asked many times this and had answers which almost said this (or maybe did exactly say it!), .. . I think I finally have knocked this one into my head now… (At least until I maybe stumble upon another anomaly!!)


    .
    Quote Originally Posted by Kyle123 View Post
    … ….. Value/2 returns an array which is why is is much faster to work with.
    … Ties up with what I have been reading, for example the Charles Williams „Stuff“ on this..


    .

    Thanks again. I will keep at it and try to bother you as little as possible.
    . Alan

  31. #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:

  32. #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.

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

    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.

  34. #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!!

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

    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.

  36. #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

  37. #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.

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

    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

  39. #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

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

    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)

  41. #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

  42. #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

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

    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.

  44. #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.

  45. #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

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

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

    P.s. I think your Dim temp As Range in your Public Function is doing nothing?
    Yep, missed that
    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
    A property can be an Object, so yes, since we're passing in an Object, it's set

  47. #47
    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

    Thanks Kyle





    .....

    Quote Originally Posted by Kyle123 View Post
    Flog a dead horse
    OK. I'll go for that
    Last edited by Doc.AElstein; 03-09-2015 at 02:26 PM.

  48. #48
    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

    Dimensioning of variable for Array returned by VBA Filter method.


    Hi,
    . In preparing a code for answering another Thread a minor Dimensioning problem gave a problem. I found the solution, but I would like to understand what is going on..

    . In brief:
    . If arrFiltValues is my variable in which an Array comes using the VBA.Filter applied to another Array, then…
    This works
    Dim arrFiltValues As Variant
    This don’t
    Dim arrFiltValues() As Variant ( I get Error 13 ( Type incompatible))

    . That bugs my code ( and me ) especially after the following I did to try and see what was going on:

    . To elaborate:
    . consider a Simple spreadsheet of silly names, listed Horizontally and vertically in the first row and first column respectively

    Using Excel 2007
    -
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Silly Name F-Tang F-Tang OLE Wigy Wam Biscuit Barrel Du Wolly-Wop
    2
    F-Tang
    3
    F-Tang
    4
    OLE
    5
    Wigy
    6
    Wam
    7
    Biscuit
    8
    Barrel
    9
    Du
    10
    Wolly-Wop
    ASheetToKeep


    . In the first section .1) ( Lines up to 50 ) in the following code we “Capture” these ranges in two almost identical ways into 4 Arrays. This “Capture” method was discussed in detail in this Thread. These Arrays become “2 Dimensional Arrays of 1 column or 1 row , discussed for example here
    http://www.excelforum.com/excel-new-...1-2-3-4-a.html
    . I know that There are good reasons to Dimension all these Arrays as variant - this also was discussed in those Threads.
    . . By setting a watch in the Watch windows of all variables and stepping through the code with F8 one sees that the Arrays are of similar form , differing only if they are “vertical” or “Horizontal”
    This is regardless of if we do this
    Dim Array() as Variant
    . Or this
    Dim Array as Variant

    Code:

    Please Login or Register  to view this content.
    .


    . In section 2) , lines 60 to 90 ( excluding 70 ) we use various methods to change these Arrays to the “Psuedo horizontal” 1 dimensional form (1) ( 2) (3) (4) etc. which is Syntaxly required for the first argument of the VBA Filter Function. (Line 70 does not work supporting my debatable theory that the VBA 1 dimensional Array is “pseudo” “Horizontal” ?!?! )

    . Later in the program I use the VBA Filter Function to remove a Silly Name. Using again the “Step through with F8 while looking in Watch window” technique I see that this returns a very similar Array to its “Psuedo horizontal” 1 dimensional first Argument, simply reduced in ”length” by a Silly name

    (. As an intermediate Step, section 3) , lines 100 to 140, I can assign a newArray to the existing “Psuedo horizontal” 1 dimensional Arrays. Again the returned format is similar again and I note I can dimension my new Array as

    Dim NewArray As Variant
    Or
    Dim NewArray() As Variant

    . The results again are to produce a “Psuedo horizontal” 1 dimensional Array. )


    . So far so good. In section 4a) lines 150 t0 180 the Filtered Array is successfully obtained ( here I have dimensioned the New Filtered Array as
    Dim arrFiltValues1 As Variant )

    . What is troubling me is that section 4b) lines 200 to 220 errors as I have initially dimensioned the New Filtered Array as
    Dim arrFiltValues2() As Variant
    . taking into account all my experiments above, I cannot come up with an explanation for this.

    . Can anyone else

    . It is a niggly point again but , I think , a good insight and help in understanding how VBA is working.

    Thanks
    Alan

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

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

    Filter returns an Array of Strings, not an Array of Variants:

    Please Login or Register  to view this content.

  50. #50
    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
    Filter returns an Array of Strings, not an Array of Variants:

    Please Login or Register  to view this content.
    Great, Thanks, .. that explains it perfectly ( almost###)… this works!


    Please Login or Register  to view this content.
    Thanks kyle.

    .. I would never have thought of that as I see no logic to it.. or maybe I do….is it because the second argument is a string and so it follows it can only compare it with an Array of string types … ( obvious I suppose, I just went off in a weird direction thinking of how the Dimensioning had to fit seeing the Filter Method )
    . But### … Why did lines 160 to 180 work then.. ????

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

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

    Because lines 160 to 180 are using a Variant Not an Array of Variants - they aren't the same thing, a Variant can be anything (including an array of strings), a Variant Array is an Array of Variants.

    The logic is simple, filter is a function that compares a string to an array of strings, therefore returns an array of string matches. You may not always pass an array of strings in, but you'll always get an array of strings out.

    Since you like to go all in with referencing, it isn't VBA.Filter, it's VBA.Strings.Filter - does it make more sense in that context?a

  52. #52
    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
    Because lines 160 to 180 are using a Variant Not an Array of Variants - they aren't the same thing, a Variant can be anything (including an array of strings), a Variant Array is an Array of Variants.

    The logic is simple, filter is a function that compares a string to an array of strings, therefore returns an array of string matches. You may not always pass an array of strings in, but you'll always get an array of strings out.....
    . great that clears that up perfectly.. was along my last thought / suggestion. . Thanks a lot

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

    Since you like to go all in with referencing, it isn't VBA.Filter, it's VBA.Strings.Filter- does it make more sense in that context?[.
    ... It probably does........ I need to go away and think about what the .Strings is ( Object , Method, etc. )

    Thanks again.

    . It probably sounds very tedious to you, but it does help getting these things explicit sometimes I think, and not just relying / accepting ther "implicit default "

    Alan

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

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

    Shouldn't you also be declaring Worksheet as Excel.Worksheet then?

    It's an Object

  54. #54
    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
    Shouldn't you also be declaring Worksheet as Excel.Worksheet then?

    It's an Object
    Yep, I always did,
    Or rather I did
    Application.Excel.Worksheet etc
    but it upset so many people i dropped it

    . it is reasonable to drop that bit , as I am in Excel so it is a good assumption that I am in using the excel library as default…

    BUT it can be very important to include VBA.

    There have been threads started when things like .Left .Right etc did not work, due to VBA different versions mixing up their library references. Using VBA.Left instead of .Left usually solved these problems..

    Alan
    Last edited by Doc.AElstein; 05-26-2015 at 09:01 AM.

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

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

    Could you please point me in the direction of some?

  56. #56
    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

    Here are a few…
    http://www.mrexcel.com/forum/excel-q...eft-etc-2.html
    http://www.mrexcel.com/forum/excel-q...r-version.html
    http://www.mrexcel.com/forum/excel-q...ng-2013-a.html

    . I had exactly this problem running some XL 2010 Files in XL2007. My Right Left and Mid functions “did not work”, that is to say any code with lines using them Functions errored. Then adding the VBA. bit did the trick. There is a better way to overcome the problem I guess, if you know your way around referencing the different Libraries etc, but that is a bit above me.

    . I did this File to help an OP with the problem,…


    .. in the attached File for example I have this test code…( the File was made and saved in XL2010 )

    Please Login or Register  to view this content.
    .
    The code runs normally in XL2010


    If you open this file and run it in my XL2007 the code does not work., it crashes saying that the project or Library was not found.

    If you modify the code thus

    Please Login or Register  to view this content.
    … it does work.

    . I am sure my solution is very crude and you probably have a much better one. I guess it would be to be careful check exactly what Libraries are used / available etc.. etc… It could be that the original code will work if I disable some libraries… but then of course if it was a real File I may have need those etc.. etc.. I am sure you understand that better than me. (Please do not moan at me if my “VBA.” Solution is a bit naff!!)

    Alan
    Attached Files Attached Files
    Last edited by Doc.AElstein; 05-26-2015 at 10:39 AM.

  57. #57
    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 Doc.AElstein View Post
    ........... I need to go away and think about what the .Strings is ( Object , Method, etc. )........
    Edit.. I just googled a bit..

    The Strings is a Object full of Functions that typically work through looking for / working with Strings / character sequences .......so I a had better modify that last working code quick or Kyle will moan at me again

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

    And for completeness the last few code lines again from my original Code from Post #48, with Kyle’s explanations in the ‘green comments

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 05-26-2015 at 10:05 AM.

  58. #58
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

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

    You should fix the missing references really otherwise something else is still going to fail.

    Anyway, since you're using strings:
    Please Login or Register  to view this content.

  59. #59
    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 romperstomper View Post
    You should fix the missing references really otherwise something else is still going to fail.......
    . Sounds like putting "newer" libraries in older XL versions. Sounds a bit much for me.. I'll pass on that for now..


    Quote Originally Posted by romperstomper View Post
    ......
    Anyway, since you're using strings:
    Please Login or Register  to view this content.

    .. I have seen that $ bit before. I am not quite sure what it does... I will go off and "google it just now... do you have a simple explanation??

  60. #60
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,221

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

    Fixing the missing references is probably not going to involve putting new versions of the libraries in old XL versions.

    I'm pretty sure functionslike Left, Right etc are available in all versions of VBA.

    The references you need to 'fix' are more likely to be non-Excel libraries.

    For example if there was code in a workbook that was automating another application, eg Word, that could cause version problems.

    Mind you you could avoid the need for setting the references by using late-binding.

    PS Fixing the references usually just involves unchecking any that are marked as MISSING when you goto Tools>References...s
    If posting code please use code tags, see here.

  61. #61
    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 Doc.AElstein View Post
    .......
    .. I have seen that $ bit before. I am not quite sure what it does... I will go off and "google it just now... do you have a simple explanation??
    … just googled words to the effect that Left could return an empty ( so a variant type ) whereas Left$ returns a string only and will error for an empty..


    left$() will return a string or generate an error if passed NULL
    left() will return a string or NULL if passed NULL


    Sounds in my Naïve ‘ead a bit of a contradiction.. Left is a function of the Strings Object.. which works with and returns strings…
    Last edited by Doc.AElstein; 05-26-2015 at 11:08 AM.

  62. #62
    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 Norie View Post
    .....

    PS Fixing the references usually just involves unchecking any that are marked as MISSING when you goto Tools>References...s
    Thanks for the reply.
    . I think somewhere along the way I tried that one once, and it was an alternative solution... to get that code to work in XL 2007, but in a real file I had those referrences were needed again running in XL 2010, so it was a pain switching on and off as it were...
    The
    VBA.
    seemeed at the time for me a better working solution, but as I said the proper solution is probably a bit much for me, I probably end up deleting something important and crashing my excel..... or worse..

  63. #63
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,221

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

    References that are marked as MISSING are very unlikely to be the standard ones that are set by default.

    When you've had the problem what other references were being used in the workbook(s) apart from the standard ones?

    PS By the standard ones I mean Visual Basic for Applications, Microsoft X.0 Object Library, OLE Automation and Microsoft Office X.0 Object Library.

    In X.0 the X stands for the version, for example for Excel 2010 I believe it's 14.0, and you shouldn't change which version of the libraries a workbook
    is using.

  64. #64
    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 Norie View Post
    References that are marked as MISSING are very unlikely to be the standard ones that are set by default.

    When you've had the problem what other references were being used in the workbook(s) apart from the standard ones?

    PS By the standard ones I mean Visual Basic for Applications, Microsoft X.0 Object Library, OLE Automation and Microsoft Office X.0 Object Library.

    In X.0 the X stands for the version, for example for Excel 2010 I believe it's 14.0, and you shouldn't change which version of the libraries a workbook
    is using.



    . Your explanation sort of ties up, I think..… The File I downloaded in Post # 56 originally came from a German Excel Learn DVD which was aimed at all versions up to XL2013. I just looked and the “missing libraries” it reference when I look in my XL 2007 are Microsoft Outlook 14.0 Object Library and Microsoft Word 14.0 Object Library. I just quickly experimented… I have to uncheck both to get that test code to work without the
    VBA.
    Bit..

    . But I do not have a real problem here… I just brought it up and referenced my earlier problem as kyle asked me to in passing in post # 55.

    . But thanks very much for the replies..you explained very nice and clearly -- it is interesting now to get an idea what the problem was back then.. In the meantime I gave up learning with the DVD’s anyway and got further with my learning participating here and over at MrExcel. So I do not have the problem any more..



    Alan

  65. #65
    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 again,
    . One remaining point which got sunk in the Library references diversion.. I have got very confused trying my best to post a clearing up / closing Post..Any help Please

    . Quick recap……
    Post #58
    Quote Originally Posted by romperstomper View Post
    …....since you're using strings:
    Please Login or Register  to view this content.
    Post #59 and Post #61
    Quote Originally Posted by Doc.AElstein View Post
    .......
    .. I have seen that $ bit before. I am not quite sure what it does... I will go off and "google it just now... do you have a simple explanation??......

    … just googled words to the effect that Left could return an empty ( so a variant type ) whereas Left$ returns a string only and will error for an empty..for example

    left$() will return a string or generate an error if passed NULL
    left() will return a string or NULL if passed NULL


    Sounds in my Naïve ‘ead a bit of a contradiction.. Left is a function of the Strings Object.. which works with and returns strings…
    ………………………………………………………………………………
    .. Starting again simplified

    Please Login or Register  to view this content.
    … I have experimented some time now with endless variations similar to this:

    Please Login or Register  to view this content.

    . I have not ( yet ) got the code to error.. ( Other than with a line such as line 22, but then both Left and Left$ error, as expected *** )

    . My conclusions (were ******* )

    . 1 ) The reference I googled is wrong!!

    . 2 ) Actually Left$ is not of any significance here: ( as is probably the case of any Method from the Strings Object.. )….
    …. Because:

    . 3 ) We got out of the discussions that Left is a method of the Strings Object. In English this means it only ……looks at, plays with, has anything to do with, work through looking for, working with ….etc .etc…. Strings / character sequences. As part of it’s “working” it takes Anything (variable “Something” in my code ) and, if it can *** converts that to a string , as it takes a string only as a first argument. I will therefore only ever have an error or a string applied to the Left. It will never occur that a blank for example is passed,, so Left$ will never have any different effect..*******


    . It is a very niggley point, as always here but some good basic points and understandings are coming through. So if anyone can comment on this last point I would be very grateful. And please insult and correct me if appropriate – I expect my conclusions wrong….as I HAVE seen Left$ somewhere ( I think - I cannot remember where!! )

    Thanks in advance!!
    Alan

    P.s. I had a last thought. Maybe in some other libraries the Left Function performs differently . I repeated my experiments in that File that would not work for Left ( The one that needed VBA.Left or VBA.Strings.Left to work ). But my conclusions remained the same

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

    EDIT
    .4 ) -- ******* I just found a way to get Line 40 to error and at the same time Line 30 ( and line 31 ) not to error…

    Please Login or Register  to view this content.

    This is telling me I can somehow “force” / “coerce” the Left Function to work with and return a non String. So this contradicts the definition of the Strings Object? Also contradicts my idea that Something in the first argument would be converted to a string if it can be *** ? As I think I can convert a Null to a “” String ??

    Very confusing..
    … This would now suggest another conclusion.
    .. generally a Strings object is one which works through, ……. looking for, working with ….etc .etc……. Strings / character sequences. . However we can “Trick it into taking and returning something else, sometimes, as with the case of making something in it’s first argument a Null….. ( and BTW we have a clear contradiction here that the VBA Left Function takes a string as second argument!!! )

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

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

    So this contradicts the definition of the Strings Object
    Not really, Strings is just used as a generic namespace for all things String related - I'm not aware of a definition that specifies thats String functions must return strings, indeed Filter, Split, Len to name a few do not return strings.

    RE: passing Null, I believe that Null is just a special type of Variant, so it's the same as passing Something in your code. That's why it errors in Left$, I believe that Left simply coerces the first parameter into
    Variant, Left$ does not - that's why Left$ is preferable over Left, it's theoretically more efficient.

  67. #67
    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
    Not really, Strings is just used as a generic namespace for all things String related - I'm not aware of a definition that specifies thats String functions must return strings, indeed Filter, Split, Len to name a few do not return strings…….
    OK, thanks that is helpful. My “Definition was made up from googling everywhere and making an average of everything I read, and if you are not sure of a strict definition then there probably is not one, or whoever once knew has forgot, died, and never bothered to write it down, So I am near as probably possible to understanding that one ! ? Possibly my definition… ……looks at, plays with, has anything to do with, work through looking for, working with ….etc .etc…. …. Could be valid for a String Object that has the $ added ?? F1 gave me something near to my definition for the Strings Object , but some Experienced regulars tell me that the info there is often not too good.


    Quote Originally Posted by Kyle123 View Post
    ………………

    RE: passing Null, I believe that Null is just a special type of Variant, so it's the same as passing Something in your code. That's why it errors in Left$, I believe that Left simply coerces the first …
    So maybe the Left does its best to get at the “Left” of whatever is there, having various processes it checks through to achieve that..,.. one could be my idea of trying to convert it to a string, another could be to seeing if the argument can have a “sensible” result some other way… and/ or maybe it just has a list, and for a Null it says give a Null as left of nothing is nothing, sort of

    Quote Originally Posted by Kyle123 View Post
    ………………
    … why it errors in Left$, I………Left$ does not…..
    - that's why Left$ is preferable over Left, it's theoretically more efficient.

    . That could tie up with my idea, or something similar… with $ it just assume the argument is a string , does not do the other stuff and so is faster… The normal default error handler ( or one of the other two error handlers if we choose to enable one of them ) is then left to take over and handle the situation when something gets confused or does not work..
    )

    Thanks Kyle,

    P.s. I put the links in you asked for in Post #55 in Post # 56 , in case it got lost in the Library references diversion

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Referring to a named range
    By tradinup2 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2013, 08:26 PM
  2. [SOLVED] Referring to a range
    By Alexander_Golinsky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2012, 04:28 PM
  3. search a range and return a value from another range without using arrays
    By marcwagner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2009, 07:46 AM
  4. referring to named range
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2009, 03:18 AM
  5. referring to a range by variable
    By SuitedAces in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 12-27-2006, 07:17 AM

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