Item way of thinking as alternative to conventional row column looping.

[DocAElstein]

URL links to here: This is post: https://www.excelfox.com/forum/showt...1D-arr2D-arr1D
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=16456&viewfull=1#post16456
https://www.excelfox.com/forum/showt...ll=1#post16456
https://excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping
https://excelfox.com/forum/showthrea...ping#post16456
https://excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping#post16456

---

Computers v Humans

It can help a lot in understanding and producing advanced VBA solutions in thinking about the difference in
_ Computers, and
_ how we prefer to see things.

A computer is just a long string of characters. Very quickly. We find that difficult.
Excel makes it a bit easier for us to look at: it puts a bit of it on a row, notches that up ( "Line feed" ) , goes back ( "Carriage Return" ), and puts the next bit on the next row etc. Then it lets us see it all on a screen. (Or it is updating each bit so us stupid humans quickly that we think it’s there all the time. Some of my better solutions are based on tapping into that fast updating to do something most efficiently, making it seem like something across a large range or table is done all at once).


Its handy to have in your tool box at hand a way to move easily between the two ,
_ the long single string, and
_ the 2 Dimensional display.

That is what this series of Blogs/ This Thread, is all about

---

Thread Contents
It’s easy to get mixed up as we are mixing up Excel Spreadsheet and VBA things, so here is an overview to help get quicker to things you may be looking for.

Post #2 ( and Post #4 ) Item way of thinking as alternative to conventional row column looping in VBA
The traditional way to loop all cells is 2 loops, one inside the other, going for example along the columns for a row, then next row etc., pseudo
__ For Each row, r
____ For Each column, c, in the row
_____Cell (r, c)
____ Next column
__ Next row
All that is done in and with, VBA. We are looping through all cells
Post #2 primarily talks about an alternative single loop idea. This is possible as many things in Excel are also ordered in a sequential single item number, (sometimes referred to as index, to help confuse things later). This is done in a specific way for a range of cells, so because we know what this specific way is, it this allows to alternatively loop the item nubers instead, calculating if needed the row and column number, pseudo like
__For all items
___Cell(rcalculated from item number , ccalculated from item number )
You see, for each item number we are able to determine/ calculate the row and column associated with it. Each cell is assigned an item number, so looping the item numbers we can also be looping all cells, since we can calculate which cell is by the calculated row and column number for that item number.
__Next item
All that is done in and with, VBA
This Item way of thinking is generally useful to know about, and can have practice advantages, but it’s unlikely to have great performance advantages. If for example we have a two row, 3 column range, the convention of the item number is like
1 2 3
4 5 6
, then the conventional way does 3 columns twice and the item way does 6 items, so we effectively loop 6 times in either way. Simple example: For 6 items, 1 2 3 4 5 6 , then at each item loop, I needed to get at each item number loop, ( 1 to 6 ),
_ the row indices of 1 1 1 2 2 2 and
_ the column of where those things were was 1 2 3 1 2 3

The main purpose of Post #2 and # 4 is to calculate those row and column indicies

---

Post #5 and Post #6 and Post #7 and Post #8 VBA Range Evaluate(" ") ( 2Darray() to 1DArray() )
The relevance to this Thread is a bit shirttail, and this subject is tackled many tines elsewhere. It fits in here as
_ we share similar mathematics to the last post
_ It fits well to the concept of a different, arguably often better, approach to looping/ reduced looping, - in this case, .. none!!
We tap into the way Excel tries to put something almost simultaneously in a 2 dimensional picture that to us humans looks like a static immediate picture.
Taking again the 2 row 3 column example we take an array of numbers in an item type list, { 1 2 3 4 5 6 } and get the corresponding array
_ for the rows { 1 1 1 2 2 2 } , and
_ the columns { 1 2 3 1 2 3 }
We can mess about with Excel things and functions that more usually take single values, and give them arrays of values instead. We are not technically doing then what mathematically is strictly array calculations**, although we are doing something similar to get an array of results instead of a single value result. (** It’s often called array calculations which can be a bit misleading)
Post #5 is just one example of this VBA Range Evaluate(" ") idea, but is included here as it happens to use very similar mathematics to Post #2 and Post #4