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

**DocAElstein** · 03-01-2022, 12:10 AM

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

Item way of thinking as alternative to conventional row column looping

Current conventional way of thinking
This Blog post assumes that you have some basic Excel VBA programming experience, and that you are familiar with this general idea used typically when filling or getting data from cells
__For Rw = 1 To maximumRows
____For Clm = 1 To maximumColumns
_____( Rw, Clm )
____Next Clm
__ Next Rw
This basic process you should make sure you fully understand before reading further.
This Blog post suggest a slightly different way about thinking about that process

Introduction
Excel Spreadsheets can be turned into pretty pictures, computer graphics and games and “dashboards”.
But they are usually not the best at doing those things.
They are best for doing things with 2 dimensional “tables” of data.
They bridge the gap between
_ dead stores of data or long fast streams of string data (Computers),
and
_ relatively slow and static living humans interacting with them

I expect we are not a long way from interacting in a 3 dimensional way, but it will settle down, I think, to be useful in a limited way. More than 3 dimensions are difficult for humans to react easily with.
A 2 dimensional desktop is particularly convenient for us.
Smartphones are a trend that I think might go back a bit to being just glorified telephones, walkmans, live cameras and simple computer games.
I personally would say, live actively physically in 3 dimensions, forget smart phones, and sit down occasionally and watch the telly or a 2 dimensional PC or laptop to get some stuff done in your computer corner.

We are basically talking in this Thread about organising a string of data travelling at the speed of light into a static line or row of the data, then carrying the rest of the data back, feeding in another row or line or “going down”, then doing the same again.

I think for Excel formulas and VBA we should not take the core of the thinking much further than this.
For furthering the study and manipulation of static table data something else , another concept, such as Power Query / Get and Transform might be more appropriate.

Looping along columns for every row The conventional way
__For Rw = 1 To maximumRows
____For Clm = 1 To maximumColumns
_____do stuff for each Cell( Rw, Clm )
____Next Clm
__ Next Rw
This is what we have got used to, from reading books, to the workings of old printers, to how a screen is filed with data. It is an efficient and logical consequence that a spreadsheet, or a rectangular range in a spreadsheet, is filled and otherwise updated in the same way.
Usually we look at a cell when doing formula calculations, but if we increase the exposed area during a screen update, we can get an efficient, almost simultaneous, calculation across an entire range. This latter is the basis of the workings of the so called array formulas, ( “CSE” stuff ). We will come on to that in the next post

Looping the Items
Many things in VBA, and computers generally, are actually ordered somewhere by something similar to a unique item number , 1 2 3 4 5 6 … etc. ( Note: in some computer conventions the first may start at 0 so we have 0 1 2 3 4 5… etc. )
Conventionally, the Range Item numbers are also so organised, and further follow the generally accepted convention of
Across
_____<- back (Carriage Return)
Down (Line Feed)
Across
_____<- back (Carriage Return)
Down (Line Feed)
………etc.

( Conventionally in computer stream strings we may refer to those
backs as carriage returns, vbCr ,
and
the downs as line feeds, vbLf.
These may be referred to as “invisible characters”, as we do not typically see them, but they must be there in the electronic string stream to indicate the start of a new line or new row )

For example for a 2 row x 3 column range, this is where the item numbers are/ what cells the item number refers to
1 2 3 vbCr & vbLf
4 5 6
Furthermore, for many things that do not have such an item number they may have a consistent order such that we can assume or assign some simple count mechanism to give them an index that we can use in a similar way to the Item number

The crux of my suggestion in this Thread is to think of the basic process shown at the start of this Blog, in a slightly different way.
A couple of reasons I can think of for doing this
_ It may be helpful to think back to the more fundamental single string idea of what computers are, as the table type arrangement is only an interface for us, and is less representative of what is actually going on in a computer
_ In some situations the alternative looping may have some advantages. For example we would not be limited to a rectangular range. (See Post #3)

Some simple required maths
What we want to do is this sort of thing as an alternative to the basic process
__For Itm = 1 To maximumItems
___( Row, Column)
__Next Itm
Before looking at the actual solution, lets simply put in words a simple solution idea, and consider again a 2 row, 3 column range
1 2 3
4 5 6
In this case, our maximumRows will be 2 and the maximumColumns will be 3
We have those sequentially numbers, and we want to convert then to row and column indices that would give us that spreadsheet range.

Row Number from Item Number
If we were to divide the actual item number by the maximumColumns , 3 , and take the integer of the result we would almost be there. But that would actually give us
0 0 1
1 1 2
If we were to first subtract 1 from the Item number, then do the same again, divide that modified number by the maximumColumns , 3, and take the integer of the result we would have,
Integer ( ( ItmNumber -1 ) / maximumColumns ) =
0 0 0
1 1 1
So we now see that we only have to add 1 to the final results and we have the row number what we want.
RowNumber = [ Integer ( ( ItmNumber -1 ) / maximumColumns ) ] + 1
1 1 1
2 2 2

Column Number from Item Number
This is slightly more tricky. But not much. I do this second as we need some of the ideas from the previous maths.
An observation:
To get the column number from the item number, in the second row the item number needs to be reduce by 1 x maximumColumns , and
if we had a third row the item number would need to be reduced by 2 x maximumColumns, (and
for the first row, the item number needs to be reduce by nothing , so by 0 x maximumColumns ) … and so on.
Note also, that it also fits this same logic to apply a reduction of 0 x maximumColumns to the first row.
So we are looking for a multiplication factor based on the Item number which gives us that sort of number sequence like 0 0 0 ; 1 1 1 ; 2 2 2 etc. sequence
We can see from one of the stages in the maths from the last section where we determined the row from the item number, that we did actually already have it:
It was at this stage: -
__________________Integer ( ( ItmNumber -1 ) / maximumColumns )
___=___0 0 0
_______1 1 1
and that would have further gone on for more rows to
0 0 0 ; 1 1 1 ; 2 2 2 ; 3 3 3 … etc.

So we can see one formula possibility:
ItemNumber - [ Integer ( ( ItmNumber -1 ) / maximumColumns ) X maximumColumns ]
This would result in our required column Indicies of
1 2 3
1 2 3

_.______

Simple VBA macro examples

Code:

Sub ItmNumbersLoopToRng()
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim ItmIndx As Long
Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2
    For ItmIndx = 1 To 5
    Dim Rw As Long, Clm As Long
     Let Rw = Int((ItmIndx - 1) / maxClms) + 1
     Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)
     Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")"
    Next ItmIndx
End Sub

Sub ItmNumbersLoopToRng2()
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim ItmIndx As Long
Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2
    For ItmIndx = 1 To 5
                    '    Dim Rw As Long, Clm As Long
                    '     Let Rw = Int((ItmIndx - 1) / maxClms) + 1
                    '     Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) 
     Let Ws1.Cells.Item(Int((ItmIndx - 1) / maxClms) + 1, ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)).Value = " (" & Int((ItmIndx - 1) / maxClms) + 1 & ", " & ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) & ")"
    Next ItmIndx
End Sub