+ Reply to Thread
Page 1 of 5 1 2 3 ... LastLast
Results 1 to 15 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.

+ Reply to Thread
Page 1 of 5 1 2 3 ... LastLast

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1