Understanding VBA Range Object Properties and referring to ranges and spreadsheet cells

**DocAElstein** · 01-25-2017, 04:55 AM

A quick graphic demo of Range Item Property arguments. ( Aka , as many people still think of as
Cells( takes arguments rows, columns ) Property. ( Which does not exist ) )

The full code descriptions and arguments and explanations thereof are around here:
http://www.excelforum.com/showthread...11#post4551080
and here:
http://www.excelforum.com/showthread...11#post4551509
and here:
http://www.excelforum.com/showthread...11#post4555023

An arbitrary Range object, that associated with the spreadsheet cells in a single area top left:bottom right of B3:C4 is shown highlighted in Yellow.
In each cell shown, some of the Range Item Property argument options are indicated.

This Post is intended to indicate the required syntax required in the brackets in order to use the a to refer to any cell in the worksheet in which the Range object is in.

To summarise:
We may use the VBA Range Item Property ( https://msdn.microsoft.com/en-us/lib.../ff841096.aspx https://msdn.microsoft.com/en-us/lib...ffice.15).aspx ) applied to a single Area of Range object to refer to any single cell in a worksheet. That is to say we use the VBA Range Item Property applied to a Range object to return a new Range object associated with any single cell in a worksheet.

For the Range Item Property we have different argument options available.
The syntax is based on
either
a two argument row, column coordinate system , like (1, -2) or (3, “B”)
or
a single argument sequential Item number ( Index ), like (3)

In both cases the origin is taken as top left of the Range object to which the Property is applied.
So if our original Range object variable was declared and then “filled” such_..
Dim Rng1 As Range
_ Set Rng1=Ws.Range(“B2”)
_..then we may obtain a new range object thus:
Dim Rng2 As Range
_ Set Rng2=Rng1.Item(1, 2) ’ Returns Range object associated with cell C2 in worksheet Ws ( same row, one ”to the right” )
or this
_ Set Rng2=Rng1.Item(1, “B”) ’ Returns Range object associated with cell C2 in worksheet Ws ( same row, one ”to the right” )
or this
_ Set Rng2=Rng1.Item(0, 0) ’ Returns Range object associated with cell A1 in worksheet Ws ( one “back up”, and one “back to the left” )
or this
_ Set Rng2=Rng1.Item(2) ’ Returns Range object associated with cell C2 in worksheet Ws ( one ”to the right” )

( In all the code lines the .Item can be removed as the Range Item Property is the default property for a range Object. So we could write like this:
_ Set Rng2=Rng1(1, 2)
( Often the .Cells property is included in such a code line. That is completely redundant in such a case as the .Cells Property returns the Range object of all the cells in the Object to which it is applied. So in such a code line the .Cells Property applied to Rng1 returns Rng1, but it looks nice:
_ Set Rng2=Rng1.Cells(1, 2) http://excelmacromastery.com/excel-v.../#comment-2891 ) )

To explain how we return the Range object associated with a single cell, such as like C2 or A1
( __In the screenshots below, the ( ) brackets like (1, 2) are as we would use them in a code line like we had above: Rng2=Rng1(1, 2) __)

For the two argument option, we can refer to rows and columns by numbers which can also be negative which allows this option to refer to any single cell anywhere in the spreadsheet. Every row number and column possibility is shown from code line 20. ( It is also possible to use a column letter enclosed in quotes as an alternative, but this is limited to “+ve” letters ).

All 2 argument options ( row number, column number )

_Row\^Col	A	B	C	D
1	Rng_Item( -1, 0 )	Rng_Item( -1, 1 )	Rng_Item( -1, 2 )	Rng_Item( -1, 3 )
2	Rng_Item( 0, 0 )	Rng_Item( 0, 1 )	Rng_Item( 0, 2 )	Rng_Item( 0, 3 )
3	Rng_Item( 1, 0 )	Rng_Item( 1, 1 )	Rng_Item( 1, 2 )	Rng_Item( 1, 3 )
4	Rng_Item( 2, 0 )	Rng_Item( 2, 1 )	Rng_Item( 2, 2 )	Rng_Item( 2, 3 )
5	Rng_Item( 3, 0 )	Rng_Item( 3, 1 )	Rng_Item( 3, 2 )	Rng_Item( 3, 3 )

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

For the single argument option, all Item number possibilities are shown from code line 30. We start at top left of the original Range object and follow the typical spreadsheet convention of along all columns then down to next row. We can refer to all cells in a column of width equal to that of the original Range object area, extending down to the bottom of the worksheet in this column then row convention. Effectively only positive Item numbers ( Indexes ) are possible, but strangely we have Items outside the original Range object. The alternative column letter option is also shown over the single argument ( Index) area, but note that the column letter option is also available to the right of the Index area: The complete “+ve” letter range is shown in red text

All single argument ( Index ) options
Some 2 argument ( row number, column number ) options
Some 2 argumant ( row number, column number ) options
(complete column letter option range is shown in red text

_Row\^Col	A	B	C	D
1	Rng_Item( -1, 0 )	Rng_Item( -1, 1 )	Rng_Item( -1, 2 )	Rng_Item( -1, 3 )
2	Rng_Item( 0, 0 )	Rng_Item( 0, 1 )	Rng_Item( 0, 2 )	Rng_Item( 0, 3 )
3	Rng_Item( 1, 0 )	RngItm(1, "A") and RngIem( 1 )	RngItm(1, "B") and RngIem( 2 )	Rng_Item( 1, 3 )
4	Rng_Item( 2, 0 )	RngItm(2, "A") and RngIem( 3 )	RngItm(2, "B") and RngIem( 4 )	Rng_Item( 2, 3 )
5	Rng_Item( 3, 0 )	RngItm(3, "A") and RngIem( 5 )	RngItm(3, "B") and RngIem( 6 )	Rng_Item( 3, 3 )

_....

Simplified code:

Code:

Sub Funky2Tests2() '  http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4566077#post4566077
Dim RngObj1Area As Range ' Arbritrary Test Range
 Set RngObj1Area = Range("B3:C4") '
 Range("A1:D5").ClearContents: RngObj1Area.Interior.Pattern = xlNone: Let Range("A1:D5").Font.ColorIndex = xlAutomatic 'Prepare demo range
 Call RangeItemsArgumants2SHimpfGlified2(RngObj1Area, Range("A1:D5"))
End Sub
Sub RangeItemsArgumants2SHimpfGlified2(RngOrg As Range, RngDemo As Range)
10  Let RngOrg.Interior.Color = 65535  'mark original Range Object spreadsheet cell area
20  Let RngDemo.Value = Evaluate("=" & """Rng_Item( """ & "&" & "(Row(" & RngDemo.Address & ")" & "-" & "Row(" & RngOrg.Item(1).Address & ")" & ")+1&" & """, """ & "&" & "(Column(" & RngDemo.Address & ")-Column(" & RngOrg.Item(1).Address & "))+1" & "&" & """ )""")
30 Dim RngItmLtrRow As Range: Set RngItmLtrRow = RngOrg.Resize((((RngDemo.Row + RngDemo.Rows.Count) - 1) - RngOrg.Row) + 1, RngOrg.Columns.Count): Let RngItmLtrRow.Value = Evaluate("=" & """RngItm(""" & "&" & "(Row(" & RngItmLtrRow.Address & ")" & "-" & "Row(" & RngItmLtrRow.Item(1).Address & ")" & ")+1&" & """, """"""" & "&" & "MID(ADDRESS(1,COLUMN(" & RngItmLtrRow.Address & ")-COLUMN(" & RngItmLtrRow.Item(1).Address & ")+1),2,(FIND(""$"",ADDRESS(1,COLUMN(" & RngItmLtrRow.Address & ")-COLUMN(" & RngItmLtrRow.Item(1).Address & ")+1),2)-2))" & "&" & """"""") and RngIem( """ & "&" & "(Column(" & RngItmLtrRow.Address & ")-Column(" & RngItmLtrRow.Item(1).Address & "))+1+" & "(((Row(" & RngItmLtrRow.Address & ")" & "-" & "Row(" & RngItmLtrRow.Item(1).Address & ")" & ")+1-1)*" & RngOrg.Columns.Count & ")" & "&" & """ )""")
40 Dim RngCL As Range: Set RngCL = RngItmLtrRow.Resize(, ((RngDemo.Column + RngDemo.Columns.Count) - 1) - RngItmLtrRow.Column + 1): Let RngCL.Font.Color = -16776961

















' Rem Ref
' http://excelmatters.com/referring-to-ranges-in-vba/
' https://powerspreadsheets.com/excel-vba-range-object/
' http://spreadsheetpage.com/index.php...your_vba_code/   
' http://stackoverflow.com/questions/2...91641#41491641
' http://www.excelfox.com/forum/showth...eadsheet-cells
' http://www.excelforum.com/tips-and-t...eet-cells.html

End Sub