# Off Topic > Tips and Tutorials >  >  The use of arrays

## CostCare

Friends,

I think that for many of you this will sound as an "open door", but the use of arrays can really improve the speed of the execution of your VBA code.

the reason i'm making this topic, is because of the fact that i think this can't be said enough. For the last couple of months i've been building an excel add-in for custumers, through wich they can calculate the costs of activites they perform. My add-in make a  lot of use of tables the user can import.

The heart of my add-in consitst of a lot of looping through these tables (worksheets). When i started building, i had heard of arrays, but never took the time to get myself started with it. As my customers demand calculation speed, the last couple of weeks i couldn't deny the fact that the use of arrays was something i had to get into. With succes!

For one of my big customers (in terms of the size of their data (rows and columns)) the calculation time for one of the biggest resources to activities, took about 6 to 7 seconds, before the use of arrays. After i implemented the use of arrays (and no longer did loops directly through the worksheet(s)), the calculation time dropped dramatically and endend at a total time of 0.8 seconds. I couldnt help to remove a small teardrop from my eye.. :-).

I really learned that the continues switching between VBA and worksheets costs a lot of calculation speed. It sometimes made me desperate, since my customers where irritated by the long performs of the code.

So, if you're building code that has as a main goal to loop through a lot of worksheets of data, i can really advise all of you to replace the worksheet(s) with an array, don't hesitate to ask questions :-).

Regards,

Bart

----------


## Greg M

Hi Bart,

Your point is well made!

Wherever possible I use an array for copying & pasting data values between ranges, i.e.:




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


This has the added slight advantage that (unlike a Copy & Paste operation) the destination range is not highlighted when the above operation has been completed.

Regards,

Greg M

----------


## Doc.AElstein

Hi Bart,




> ....
> I really learned that the continues switching between VBA and worksheets costs a lot of calculation speed. It sometimes made me desperate, since my customers where irritated by the long performs of the code.
> ..So, if you're building code that has as a main goal to loop through a lot of worksheets of data, i can really advise all of you to replace the worksheet(s) with an array, don't hesitate to ask questions ....



...Yep, I go along with all that. The Array issue has come up often recently
http://www.excelforum.com/excel-prog...ml#post4199233
Initially it takes a bit of getting used to. But after a while “Capturing” all data into Arrays, working internally, then pasting out in one go has  become second nature. The Array of Arrays is a good thing to bear in mind. Often I will use a 1 Dimensional Array whose elements are 2 Dimensional Arrays as discussed in the above Thread.... 

... Mostly the “Array” method is the one to be considered when mainly one is interested with playing around with values. Where Formulas, Formatting etc. is involved then sometimes spreadsheet “interactions” are worth considering the .PasteSpecial Xl_____  etc to get the exact type of output you want..  But then again you can, after some practice, get to alternative “Array” internal  working to build up the formulas ( Again can be pasted out later in one go, along with values ) ,........ 

http://www.excelforum.com/excel-prog...ml#post4198233
http://www.excelforum.com/excel-prog...ml#post4201260
OR
even use the array co-ordinates to paste out some formatting, what I sometimes have done when an OP later asks for formatting After I wrote an Array Values code for him
http://www.excelforum.com/excel-prog...ml#post4188420


Alan

P.s. I find an Array Method particularly good as an alternative to the AutoFilter for Filtering / Sorting based on column criteria.. I learnt that form Participating in threads here, having initially learnt the  more classic Spreadsheet methods..
http://www.excelforum.com/excel-new-...ba-arrays.html

P.P.s. Here is a good reference for Array Stuff:
http://www.snb-vba.eu/VBA_Arrays_en.html

----------


## Doc.AElstein

Hi Greg,




> .....
> Wherever possible I use an array for copying & pasting data values between ranges, i.e.:..



I love those “one liners” for capturing and pasting out large ranges, and they are well worth mastering, and yes..




> ....
> This has the added slight advantage that (unlike a Copy & Paste operation) the destination range is not highlighted when the above operation has been completed.....



... having to do  something like
Application.CutCopyMode = False
After a Copy & Paste operation To stop that screen highlighting Flicker is often forgotten  ( This is necessary to clear the clipboard i believe )
Alan

----------


## CostCare

Alan,

BTW: before i used arrays i tried the .SpecialCells(xlCellTypeVisible) trick after i set an autofilter on the column through which i wanted to loop. This also increased the calculation speed, but from 6 to 7 seconds to like 4 or 5 seconds..

Arrays just did the trick for me! :-)

Nice to see all the posts! Good learning stuff! :-)

Bart

----------


## Doc.AElstein

Hi Bart
 Thanks for coming back, ....yep all good learning stuff 




> .....: before i used arrays i tried the .SpecialCells(xlCellTypeVisible) trick after i set an autofilter on the column through which i wanted to loop. This also increased the calculation speed, but from 6 to 7 seconds to like 4 or 5 seconds.......Arrays just did the trick for me! :-)....



...I always wondered why my code of that type always seemed to work the same without the .SpecialCells(xlCellTypeVisible) bit. So now I know that is just a trick to get it a bit faster, but which as you say is hardly noticeable compared to the speed advantages of going over to Array methods..
Alan

----------


## xladept

Hi CostCare,

I posted this a while back - you might like it! :Smilie:

----------


## Doc.AElstein

*Hi*

This Thread is not solved, and if it were I would still post this follow up because The title could lead people here when considering the *Use of Array*, and I have modified slightly my opinions...
 I was ( still am ) an Excel VBA Novice / Part timer. 
* I was ( am still ) a keen advocator of Arrays in VBA.* But since this Thread I have had a bit more experience using Arrays both in answering Threads and on my own project. I have a few comments based on my experience.. I will not repeat again the exact details, I have blogged a bit in some Threads and I will try to reference them. This is just a summary of my experience. 

*_ First what are we talking about*..briefly,
__............The classic given advantage of using Arrays, regarding Looping or Looping with conditions: -  Initially it often seems a lot more efficient to capture a Range of values into an Array, using for example the .Value Method applied to a Range to return a field of Elements which can be directly assigned to an Array
Dim arrIn() as Variant  '  Varint required to match returned Element Field from .Value Property applies to Cells more than one
arrIn() =ARange.Value
Then you do everything quick with VBA Looping and VBA Mathematics , build an output Array, then paste it out in a similar One Liner way............._

_....
 I still think an Array can be a great way to organise things, such as using an Array of Arrays, where the elements of the Array can be something quite big like an object or an Area 
http://www.excelforum.com/showthread...33#post4199233
 Now I say an Area specifically here. Previously I would have thought immediately of this Area as values in a Worksheet. ( _which you then put in the Array in one go_..*arrIn() =ARange.Value*   ) I might still do that for large but not over large Arrays of values. Seems a neat idea. 
As example of what you can do with such a "captured" Array is this nice formula
*arrOut() = Application.Index(arrIn(), rws(), clms())*
(Here *arrIn()* is an Area of values)
http://www.excelforum.com/excel-new-...ba-arrays.html
 A big stumbling point I found there was the size restriction on *arrIn().* This size restriction could, to some extent, be improved by such a code line ( here For an entire Worksheet Range of cells as the Area  )
*arrOut() = Application.Index(ws.Cells, rws(), clms())*
( or here For a single Range of cells as the Area )
*arrOut() = Application.Index(ARange, rws(), clms())*
http://www.excelforum.com/excel-prog...ml#post4238685
Here the second argument can be taken now as the entire Spreadsheet with .Cells. ( But the output is still limited as before to values of no more than the earlier Worksheet size ( 65535r x 255 ) )_....

 But then a bit further along the line, what has caught me out, is that, especially in the case of a multi Dimensional Area, the way VBA seems to have been written, is such, that many things are optimised to work on a Spreadsheet. Maybe that should not be surprising. Excel is about a Spreadsheet!! But it is a bit more subtle than that. 
 For example, Trying to find things in an efficient Program to Match things up I originally did by capturing  two big Arrays. Then I did simple looping with comparisons etc... Doing this you can get a few problems, as I did,  with the Full Array approach:
*_a)* The Variant Types necessary to make the initial quick capture do not always act exactly the same as the would they if they were Declared as the Element types that they have in them.
http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
*_b)* Further you can get caught out with  VBA taking for the values returned in those above types of formulas *.Value or .Value2*
http://www.eileenslounge.com/viewtopic.php?f=30&t=22787
*_c)* Then what most changes my opinion with these things is that for large arrays when doing things which may involve a single row or column, then you can get some real speed advantages of doing  Row and Column things in a spreadsheet.
http://www.eileenslounge.com/viewtopic.php?f=27&t=22512

So my Area initially , what I might previously immediately have captured to an Array in one go, I might now rather consider as my Spreadsheet or a part thereof. And then still do large captures but not immediately. I will try to qualify that reasoning a bit now..
_..............

*What is going on in Excel to effect are choice*
It is very difficult but I try to  summarise or make a simple explanation here. 
Some very basic understanding of how exactly VBA ( or Excel generally ) appears to work can help utilise more efficiently the Arrays. 
 Excel will tend to work, I am finding, very efficiently when it deals with a Row or a Column. Intrinsically, internally, or in its brain, when it refers to a row it holds in its memory something different to us. 
You can test this yourself by a couple of ways.
_(i) Consider you have a formula that you know is going to return you a single row of values. Typically you would select an appropriately size row area in your spreadsheet, then type your formula into the formula bar and enter it in the CSE type way. Fine. But try giving ( selecting ) a much bigger area. You will see it errors in your extra rows, but in the extra columns you will repeat your row values.
http://www.mrexcel.com/forum/general...stops-%94.html    ( Post #3 )
_(ii) Similarly. VBA will, just from convention, allow you to put a 1 D Array into a Spreadsheet row. Once again if you extend the Area in your spreadsheet where you paste it you will error outside your row range but will repeat the rows in your extra columns. 
( For both these examples VBA conventionally takes Top Left as the start point )
The second example cannot be repeated with a 2 Dimensional 1 row Array. Excel will have done extra work to give the specific Array, ( with subsequent time costs )
http://www.excelforum.com/showthread...t=#post4380627

 Another way of saying the above is that VBA saves time in many Functions when it works for example with a co ordinate or pair of coordinates. Consider it as the difference between trying to read  2 Similar Maps. Both have x y co ordinates written on the x and y axis. One has a grid, one does not. We do not think of a row as a grid of extending columns ( Like a Brush, and each strand is a row extending over many columns ). VBA appears to think of rows in this way, as a sort of set of "vertical" Grid Lines.
Or another way: When I learnt in the army to read a map I went _along the columns then up the steps_ 
VBA is there from the start, at the appropriate intercept of its long extended columns on its rows, ( and the extended rows of its columns ) ( - It holds two Brushes together and notes where strands cross )

 That helps may be make VBA very efficient in working in 1 Dimension, so some things like *.Find* are very efficient in one direction, and an Index can be very efficient also as effectively it represents the row and columns as sets of column and row grids. ( two brushes )(- Here VBA somehow Transposes the last argument "Brush" so as to give an entiire Grid of intercepts and so has immediatel the entire Spreadsheet Range intercepts or co-ordinate pairs. My original formulas work by virtue of perverting this with a further Transpose..** )

  Further I am thinking that for some Worksheet Functions such as *.Index* , *.Match*, *.Transpose*, VBA , when working on Arrays, may be put them back, as it were, into  a Range before performing the Worksheet Function, then converting back to an Array. This then  leading to inefficiency 
  A side issue here, but relevant as it can help understand the size limitations is the following: That is that  as VBA is already holding  single values as grids, from which to get its intercepts then, handling Arrays can be very tricky, leading to a very complex set of offsets to be handled. ( Brushes tacked on to each brush strand!!  very confusing!! ). This can help explain that VBA does not like  directly to copy an Array an take by Value in  a Function, as some awkward interfering of the grids ( Brush strands ) along the way  could cause problems. !!!
This could also help explain a current Transpose Bug when using for Arrays
https://newtonexcelbach.wordpress.co...2013-and-2016/
http://excelmatters.com/2016/03/08/t...2013-and-2016/
I routinely now always use my own Functions for Transposing Arrays
http://www.excelforum.com/tips-and-t...e-byvalue.html
_...
 !!!The fact that putting an Array in a Variant allows it to be passed by value is possibly restricting some grid somewhere . But note you may  then in some situations give different results as when passing the same Arrray by Referrence. 

**Perverting the way VBA handles the complex offsets is the key I think to understanding the first *.Index* with Array arguments formulas given above
http://www.mrexcel.com/forum/excel-q...ml#post4375354
and further with the *.*Function the following is an interesting perversion:
http://www.eileenslounge.com/viewtopic.php?f=4&t=22534

_.............................

*Conclusions*
So I would modify my way of thinking a bit here as follows: If possible try to arrange your initial data such that you may need to process a lot of a single direction, and if possible a row. 
To find , or sort, by rows is possibly better to be done on a Range, using Worksheets functions. Conside that first.

 Capturing and Doing things within a Row of values does appear very quick and efficient. 

 In my example, I had ( have ) a very large Worksheet. I got excited about Arrays, and captured many big 2 Dimensional Ranges of values to Variant Element Arrays. ..

 I found later when doing many complicated things that I was hitting strange Limits, not just size, but some pretty Quirky things resulting from my 2 Dimensional Capture as I discussed above.

 I just spent some time modifying and greatly improving the efficiency of the code. I have manipulated the data such that I require manipulating values within long rows. These Rows can be captured in the way shown at the start of this Thread. Manipulation of those single   rows does appear very efficient. 

 Similarly, in some threads, for example
http://www.excelforum.com/showthread...45#post4326245
it appears taking in long rows or columns one by one, working on them in simple loops then pasting each one out can be very efficient and sometimes better than talking the whole Area in at once, doing all the working on that internally then pasting the full Output Array. ( And note using .Offset seems very efficient also when capturing and pasting out, as well as saving extra Range Declaration. Again I think this is something to do with how VBA works. It slides / offsets its "Brushes" to get the required intercepts )
 Doing as i suggest in this thread some initial work before on the Worksheet, possibly with Worksheets functions can add to the efficiency overall. Then capture and work on single rows if possible, etc... So that is basically my modification to the initial idea given right at the start of this post.


_. Of course this will be very dependent on what you are doing. And is the current stand. It Could all be made nonsense of if computers get even more faster, and Microsoft get better at their  mathematics and get their offset calculations right so as to remove the limits of things like the Worksheet Functions working on Arrays.....

_. I am just attempting to modify a bit my given opinions here on Arrays, based on further experience. Hope that may help a bit anyone stumbling along excited about doing everything with Arrays, - *Stop* and think a bit first.    :Roll Eyes (Sarcastic): . ( Then use them anyway for most things    :Smilie: .. cos they are good    :Wink:   ##  )

*Alan*

## http://www.snb-vba.eu/VBA_Arrays_en.html

----------


## FDibbins

Alan, seeing as you are adding to/"answering" and not asking, I see no problem here, so - post away  :Smilie:

----------

