# Off Topic > Tips and Tutorials >  >  VBA: Working with "Areas" within 2d Arrays

## DonkeyOte

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.

----------


## lecxe

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

----------


## DonkeyOte

@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.
```

----------


## dangelor

A 1D array from a vertical range...



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

----------


## lecxe

> 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.
```

----------


## Jewano

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/

----------


## Rick Rothstein

You can also use the Index function to rearrange columns of data very quickly. See my mini-blog article here...

https://excelfox.com/forum/showthrea...olumns-of-Data

----------

