+ Reply to Thread
Results 1 to 6 of 6

VBA: Working with "Areas" within 2d Arrays

  1. #1
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    VBA: Working with "Areas" within 2d Arrays

    Of late I have become more and more convinced that Index is the best "value for money" Worksheet Function in Excel.

    In worksheet function form the INDEX function can for example do the following (all context aside):

    a) return single Values

    Please Login or Register  to view this content.
    note: here the above acts like INDIRECT in so far as if row 4 is deleted the above would still return contents of the "new" A4 and not error
    (we are for sake of simplicity assuming Col A is not deleted in above example)


    b) return Array of Values for use in conjunction with other functions

    Please Login or Register  to view this content.
    c) be used to create Ranges, eg for use as Dynamic Named Ranges

    Please Login or Register  to view this content.
    which all in all makes it incredibly flexible and it does all of this whilst being non-volatile (post XL97) unlike equivalent function types (eg OFFSET, INDIRECT etc...)

    However, further to it's "native" flexibility it's also very useful in VBA specifically when working with Arrays.

    Let's assume we have a numeric 2d Array which for sake of "alternatives" is not sourced from a Range object.

    Using a very basic (and admittedly unrealistic) set up along the lines of:

    Please Login or Register  to view this content.
    Let's now assume we want to Sum the contents of the 2nd "Column" of our 2d Array without having to iterate each "item", how can we do this ?

    INDEX offers us a very simple method:

    Please Login or Register  to view this content.
    The same can be applied for a given "row" - say the 10th row of our Array

    Please Login or Register  to view this content.
    This is very handy but what if we wanted to sum an area within our Array - say from "row" 50 to "row" 15000 (inclusive) whilst omitting "columns" 1 & 4 ?

    In terms of the native function and a range this is relatively straightfoward, if we assume our 50000 x 4 matrix is A1:D50000 then:

    Please Login or Register  to view this content.
    but how can we reflect the same in VBA using INDEX ?

    Previously I thought this was not possible... then along came our very own lecxe with this very clever trick:

    Please Login or Register  to view this content.
    note: you could use shorthand for the Evaluate if preferred - ie [row(50:15000)]

    That's pretty darn clever !!!


    Better yet this technique can handle non contiguous parts of the source array - say we wanted the same rows but to sum columns 1 & 4 rather than 2 & 3:

    Please Login or Register  to view this content.
    that's super clever !!! especially when you consider the "native" INDEX equivalent:

    Please Login or Register  to view this content.

    Better still (is this even possible?!) pending actions we could even revert the ordering of the "columns"

    So let's now assume that we have a 5th Column in our Array which is populated with strings which may or may not repeat ("apple", "banana" etc)

    We now want to match "apple" in between "rows" 50 and 15000 in our Array and return the value associated with that match from "Column" 3

    Using the above technique we could create a right to left VLOOKUP using this sub array technique

    Please Login or Register  to view this content.

    If you feel you must reply to the above (not expected) please note the following:

    a) the above is not a question

    b) none of the techniques outlined above are being advocated as "best practice"

    c) the set ups above are entirely hypothetical
    Following on from the above:

    though there are countless alternative methods for all of the above they it is not the intention to discuss them here - this post is merely to demonstrate lecxe's Evaluate w/INDEX trick.


    The post in which this trick was first brought to my attention resides a) on another Excel Message Board and b) within a restricted forum of that Message Board so at this stage I can not provide a direct link unfortunately.
    Last edited by DonkeyOte; 01-01-2011 at 05:08 PM. Reason: typo in c) formula

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: VBA: Working with "Areas" within 2d Arrays

    Hi

    This is how I got to the vba statement that DO posted to sum some values in an array:

    Please Login or Register  to view this content.
    (I used just 10 rows to make it easier to test in the worksheet)

    It sums elements in rows 5 to 14 in columns 1 and 4 of the array.

    I was inspired by how the Index() function works in the worksheet with ranges.

    In this case, to add the values in the cells in rows 5 to 14 in columns A and D one could use an efficient formula like the one DO posted:

    =SUM(INDEX(A:A,5):INDEX(A:A,14),INDEX(D:D,5):INDEX(D:D,14))

    It's not possible, however, to reproduce it directly in vba.

    I thought then of another way, using Index() as an array function that returns an array, to extract the values into a contiguous rectangular range and then to sum the result range.

    For ex., for this vba statement:

    - select I1:H9
    - in the formula bar write: =INDEX(A:D,ROW($5:$14),{1,4})
    - confirm with CSE

    Now in I1:I9 you have A5:A14 and in H1:H9 you have D5:D14.

    The values in this rectangular range is what you get in vba with

    Please Login or Register  to view this content.
    ,you get a 2D array with these values.

    Now in another cell you'd use "=SUM(I1:H9)" to get the sum of these values. That's what you get with the vba

    Please Login or Register  to view this content.
    Although this way of calculating the sum of the values in the cells in the worksheet does not seem direct or efficient it has the advantage of allowing for a direct reproduction in vba in this simple and concise way:

    Please Login or Register  to view this content.
    This statement replicates exactly the same behaviour of Index() in the worksheet but in vba it can also be used with arrays.


    Happy new year!

    lecxe

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: VBA: Working with "Areas" within 2d Arrays

    @lecxe, thanks for this great post - I trust others who find it interesting will "tip your scales" accordingly.

    One final use for Index in VBA (which lecxe kindly reminded me of) is something I used earlier today, namely:

    circumventing the need for Transpose when creating 1d Arrays from Horizontal Range Vectors:

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 01-02-2011 at 06:04 PM. Reason: removed assertion re: Vertical Vectors as plainly not true (thanks lecxe ;)!)

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,551

    Re: VBA: Working with "Areas" within 2d Arrays

    A 1D array from a vertical range...
    Please Login or Register  to view this content.
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  5. #5
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: VBA: Working with "Areas" within 2d Arrays

    Quote Originally Posted by dangelor View Post
    A 1D array from a vertical range...
    Please Login or Register  to view this content.
    Hi dangelor

    Maybe in the case of a vertical range you don't need the Application.Index()?

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-01-2020
    Location
    Poland
    MS-Off Ver
    2002 2003 2007 2010
    Posts
    7

    Re: VBA: Working with "Areas" within 2d Arrays

    Using the
    Index( Arr() , Rws(), Clms() )
    technique seems to be very versatile, and just one example would be to do that transpose, or variations of it.
    vArray = Application.Index(Range("A1:A10").Value, Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
    vArray = Application.Index(Range("A1:A10").Value, Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front (Arse over Tit)

    And its probably worth noting that when you do it that way , sometimes things seem to work better when you use just Range
    vArray = Application.Index(Range("A1:A10"), Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
    vArray = Application.Index(Range("A1: A10"), Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front (Arse over Tit)

    Sometimes , better still, using Cells is another option
    vArray = Application.Index(Cells, Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
    vArray = Application.Index(Cells, Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front (Arse over Tit)





    A 1D array from a horizontal range...
    vArray = Application.Index(Range("A1:J1"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"), Evaluate("={1,2,3,4,5,6,7,8,9,10}"))
    vArray = Application.Index(Range("A1:J1").Value, 1, 0)








    Ref
    http://www.eileenslounge.com/viewtopic.php?p=271035#p271035

    edit: some more refs:
    Ref
    https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172
    https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
    https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html
    http://www.eileenslounge.com/viewtopic.php?p=271035#p271035
    https://www.ozgrid.com/forum/index.php?thread/1227920-slicing-a-2d-array/&postID=1239241#post1239241
    https://eileenslounge.com/viewtopic.php?p=274367&sid=6b84ff6917c71e849aaeaa281d06fc31#p27436
    https://eileenslounge.com/viewtopic.php?f=30&t=34217&p=265384#p265384


    https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/
    Last edited by Jewano; 10-25-2020 at 04:50 AM. Reason: błąd pisowni and new refs ,and ******* bad language

+ Reply to Thread

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