The use of arrays

Hi Bart,

Originally Posted by CostCare

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

Hi Greg,

Originally Posted by Greg M

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

Originally Posted by Greg M

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

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

Originally Posted by CostCare

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

Hi CostCare,

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

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. . ( Then use them anyway for most things .. ‘cos they are good ## )

Alan

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

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