Range object - 2

In this module:

Range object
Range elements
Range to VBA array
- For...Next loop with dimension counter
- For...Next, and For Each...Next loops
- Process the array values
Clear the Immediate Window

1. Range object

The Range object hierarchy

Examples of the Range object hierarchy, Application to Range, are shown in code 1. The Cells.Count property demonstrates return information about the Range object.

Code 1: Sub RngObject2_1

' =================
' WS range
' Name: Data1
' RefersTo: =Sheet1!$B$3:$C$6
' Dimension: 4 x 2 = 8 cells (elements)
' Elements:
'       A   B   C
'   3       32  33
'   4       42  43
'   5       52  53
'   6       62  63
' =================

Sub RngObject2_1()
Dim RV(1 To 13) As String   ' Return Value array

' Reference name
    RV(1) = Application.Workbooks("xlf-range-object-2.xlsm").Worksheets("Sheet1").Range("Data1").Cells.Count
    RV(2) = Application.Workbooks(1).Worksheets(1).Range("Data1").Cells.Count
    RV(3) = Workbooks("xlf-range-object-2.xlsm").Worksheets("Sheet1").Range("Data1").Cells.Count
    RV(4) = Worksheets("Sheet1").Range("Data1").Cells.Count
    RV(5) = Range("Data1").Cells.Count
    RV(6) = [Data1].Cells.Count


    RV(7) = ActiveWorkbook.ActiveSheet.Range("Data1").Cells.Count
    RV(8) = FirstSheet.Range("Data1").Cells.Count

    RV(9) = "vbNewLine"

' Reference address
    RV(10) = Range("B3:C6").Cells.Count

    RV(11) = Range(Cells(2, 2), Cells(5, 3)).Cells.Count

    RV(12) = Worksheets(1).Range("B3:C6").Cells.Count
    RV(13) = Range("Sheet1!B3:C6").Cells.Count

' Print array
    Call Arr2IM(RV)

End Sub

Line 18: The Cells property returns a collection of cells (as a Range) associated with the Range("Data1") array. The Count property returns a Long value representing the number of items in the object collection. Use CountLarge if elements exceed the limit of Long (2^32)
The Cells.Count property for the Range object can be simplified. All of the statements in the code 1 procedure can be written in the style ...Range("Data1").Count

2. Range elements

This section examines referencing of individual elements and sub arrays of the Range object. The element Value property is assigned to the return value (RV) array.

2.1 Range item (element) assignment statements

Code 2: Sub RngObject2_2


Sub RngObject2_2()
Dim Rng As Range    ' Declare a range object variable
Dim Dat As Variant  ' Declare a variant array
Dim RV(21 To 310) As String  ' Return Value array

' Return a value - Data1 upper left
    RV(21) = Range("B3").Value
    RV(22) = Range("B3")
    RV(23) = [B3]

    RV(24) = Range("Data1").Cells(1, 1).Value
    RV(25) = Range("Data1").Range("A1").Value

    RV(26) = "vbNewLine"

' Return a value - Data1 bottom right
    RV(261) = Range("C6").Value

    RV(271) = Range("Data1").Cells(4, 2).Value
    RV(272) = Range("Data1").Range("B4").Value
    RV(273) = Range("Data1").Cells([Data1].Rows.Count, [Data1].Columns.Count).Value
    RV(274) = Range("Data1").Range("A1").Offset(3, 1).Value


' Assign the range to the object variable
Set Rng = Range("Data1")

    RV(279) = "vbNewLine"
    RV(280) = Rng.Cells.Count
    RV(281) = Rng.Cells(4, 2).Value
    RV(282) = Rng.Range("B4").Value
    RV(283) = Rng.Cells([Data1].Rows.Count, [Data1].Columns.Count).Value
    RV(284) = Rng.Range("A1").Offset(3, 1).Value

    RV(285) = "vbNewLine"

    RV(286) = Rng.Range("A1").Offset(3, 1).Value
    RV(287) = Rng.Cells(4, 2).Value
    RV(288) = Rng(4, 2).Value

'Assign the range to a variant array
Dat = Range("Data1").Value

    RV(289) = "vbNewLine"
    RV(290) = (UBound(Dat, 1) - LBound(Dat, 1) + 1) * (UBound(Dat, 2) - LBound(Dat, 2) + 1) ' Count elements
    ' Upper left
    RV(291) = Dat(1, 1)
    RV(292) = Dat(LBound(Dat, 1), LBound(Dat, 2))

    RV(299) = "vbNewLine"
    ' Bottom right
    RV(300) = Dat(4, 2)
    RV(301) = Dat(UBound(Dat, 1), UBound(Dat, 2))

    Call Arr2IM(RV)

End Sub

Line 51: Declare a Range object variable named Rng. Note: Object data types are stored as 4 bytes (32 bits)
Line 56 to 61: shows how to reference the upper left cell (home cell) of the Range
Lines 60 and 61: Cells(1, 1) and Range("A1") both refer to the home cell of the Data1 range (qualifier Range("Data3")).
Line 75: Assign the Range object to the Rng variable with the Set statement
Line 88: Address elements of the Rng object with Rng(Row, Column) index numbers
Line 91: Assign the Range to a variant array named Dat. Because the Range.Cells collection has base 1, the Dat array is also assigned base 1. Access the elements of the array with index numbers (row, column). Note: VBA arrays (ie. Dat) do not have a Value property.

The process of assigning a Range to a VBA array is continued in the next section.

3. Range to VBA array

3.1 For...Next loop w/ dimension counter

Code 3: Sub Rng2Array1

Sub Rng2Array1()
    Dim r%, c%
    Dim i%, j%
    Dim InArr() As Double

    r = Range("Data1").Rows.Count
    c = Range("Data1").Columns.Count

    ReDim InArr(1 To r, 1 To c)

    For i = 1 To r
        For j = 1 To c
            InArr(i, j) = Range("Data1").Resize(1, 1).Offset(i - 1, j - 1).Value
        Next j
    Next i

    Stop

End Sub

Lines 120 to 124: Is a For...Next loop that reads the Range elements row by row, that is, in the first row (i = 1), read all of the columns (j = 1 to c), next row (i = 2), read all of the columns (j = 1 to c), and continue to r
Line 122: Range("Data1") is a 4 x 2 array. The Resize(RowSize, ColumnSize) property returns a resized Range object, a 1 x 1 array at the Range qualifiers Home element (1,1). This is then used as the Range qualifier to the VBA Offset property. The Offset property returns a Range that is offset from the Range qualifier a set number of rows and columns Offset(RowOffset, ColumnOffset). The Range qualifier is equivalent to the Reference argument in the WS OFFSET function. The elements of both the Array and the Offset property have index (i, j) and dimension (r, c) counters

3.2 For...Next, and For Each...Next loops

The For...Next statement repeats the code statement(s) a set number of times, determined by the dimensions of the Range object. Similarly, the For Each...Next statement repeats the code statement(s) a set number of times, determined by the number of items in the (Cells) collection of the Range object. Equivalent to the dimension (number of rows times the number of columns)

Code 4: Sub Rng2Array2

Sub Rng2Array2()
Dim r%, c%, r1%, c1%
Dim i%, j%

Dim InArr() As Double
Dim Rng As Range, Cell As Range

    Set Rng = Range("Data1")
    With Rng
        r = .Rows.Count
        c = .Columns.Count
        r1 = .Row
        c1 = .Column
    End With

    ReDim InArr(1 To r, 1 To c)

        For i = 1 To r
            For j = 1 To c
                InArr(i, j) = Rng.Resize(1, 1).Offset(i - 1, j - 1).Value
            Next j
        Next i

        Call InArr2IM(InArr)

    ReDim InArr(1 To r, 1 To c)

        For i = 1 To r
            For j = 1 To c
                InArr(i, j) = Rng(i, j).Value + 2
            Next j
        Next i

        Call InArr2IM(InArr)

    ReDim InArr(1 To r, 1 To c)

        For Each Cell In Rng
            r = Cell.Row - r1 + 1
            c = Cell.Column - c1 + 1

            InArr(r, c) = Cell.Value + 3
        Next Cell

        Call InArr2IM(InArr)

    With WorksheetFunction
        Debug.Print vbNewLine & "=== " & Time
        Debug.Print "Min: " & .Min(InArr)
        Debug.Print "Max: " & .Max(InArr)
        Debug.Print "Average: " & .Average(InArr)
        Debug.Print "Std: " & Format(.StDev_S(InArr), "#0.0000")
        Debug.Print "Min: " & .Count(InArr)
    End With

 End Sub

About Code 4

Line 137: Using the Set statement, assign the range Range("Data1") to the Rng object variable
Lines 149: Loop through the items of the Rng object with the same technique as code 3 line 122 (Range.Resize.Offset.Value). The index counters are applied to the Offset property
Lines 159: Loop through the items of the Rng object, this time, with the counters applied directly to the Rng object Rng(RowIndex, ColumnIndex)
Lines 167 to 172: Forms a For Each...Next loop. On line 135 of code 4, the variable Cell is declared as a Range object. The For Each...Next loop reads each item (Cell) in the Rng range on a line by line basis. The r and c counters are only required to write to the InArr array
Lines 171: Loop through the items of the Rng object, this time, the counters are applied directly to the InArr array only

3.3 Process the array values

Add two private Sub procedures to write the array values to the Immediate Window (code 5 and code 6)

Code 5: Sub Arr2IM with .Cells and .Item

Sub Arr2IM(InArr As Variant)
Dim i%

    Debug.Print vbNewLine & "===  " & Time
    For i = LBound(InArr) To UBound(InArr)
        If Left(InArr(i), 5) = "vbNew" Then
            Debug.Print vbNewLine
        ElseIf InArr(i) = "" Then
            ' Exit if
        Else
            Debug.Print i & ": " & InArr(i)
        End If
    Next i

End Sub

Code 6: Sub InArr2IM with .Cells and .Item

Sub InArr2IM(InArr As Variant)
Dim i%, j%

    Debug.Print vbNewLine & "===  " & Time
    For i = LBound(InArr, 1) To UBound(InArr, 1)
        For j = LBound(InArr, 2) To UBound(InArr, 2)
            Debug.Print i & "," & j & ": " & InArr(i, j)
        Next j
    Next i

End Sub

4. Clear the Immediate Window

Code 7: Sub ClearIM clear the Immediate Window with keyboard Ctrl + G, Ctrl + A, Delete sequence

Private Sub ClearIM()
' Clear the Immediate Window
    Application.SendKeys "^g ^a {DEL}"
End Sub

Line 227: Clear the Immediate Window with keyboard Ctrl + G, Ctrl + A Delete shortcut sequence. SendKeys Method key combinations "^ (carat)" represents CTRL key, and "{DELETE}" or "{DEL}" is the keyboard Delete key
CAUTION: to avoid unexpected problems ensure that the VBE window is open. The procedure has been set to Private to prevent execution from the Excel window

Download the file xlf-range-object-2.xlsm [24 KB]

This example was developed in Excel 2016 Pro 64 bit.
Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]