Correctly saving reference in memory instead of activating

Question

How do you correctly save the reference to a certain cell, like

Dim x As WHAT_TYPE_?
x = location_of_cell_in_memory

instead of activating it?

I don't want to iterate by activating cells (.Select or .Activate) and then using offset to move up or down. This should be done without anything happening on the screen, just retrieving and assigning values in the background, so the user can't click somewhere on the screen and ruin the script.

Or

do I really have to define some Pair-Datatype (x,y) myself and using that as Cell-representation?

Or

as a triple (sheet, x, y)?

I'm not even sure if that is even possible in VBA, I come from Java.

http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Axel Richter, Oct 31 '15 at 15:34

score 1 · Accepted Answer · answered Oct 31 '15 at 15:54

You don't have to activate or select a cell to assign a value. The value property can be read or written directly, given the proper object (Range or Cell, etc.). You can initialize a Range variable and let that hold the cell address you want to access later on.

The above said, if you just want to assign values over an iteration of cells, there's no need to really assign a dynamic range reference and use offset. A simple loop would do. See following code and example.

Sub IterateExample()

    'Initialize variables.
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Integer, y As Integer, i As Integer, j As Integer

    ' Assign to object.
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    ' Assign values.
    x = 10
    y = 10

    Application.ScreenUpdating = False 'Hide updates from viewer/user.

    ' Simple iteration.
    For i = 1 To x
        For j = 1 To y
            'i is row index, j is column index.
            ws.Cells(i, j).Value = i * j 'Use .Value directly, no need to .Select or .Activate
        Next
    Next

    Application.ScreenUpdating = True 'Return to original setting.

End Sub

Result is simple enough:

However, if you really have to have a range reference that updates, a loop can also solve that, just re-assign the object inside the loop.

Sub OffsetExample()

    'Initialize variables.
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim r As Range, i As Integer

    'Assign to object.
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    'Assign range.
    Set r = ws.Range("A1")

    Application.ScreenUpdating = False 'Hide updates from viewer/user.

    'Iteration of offset.
    For i = 1 To 10
        r.Value = i * i
        Set r = r.Offset(1, 1) 'Move range reference 1 row down, 1 column right
    Next

    Application.ScreenUpdating = True 'Return to original setting.

End Sub

Result is as follows:

Woah, thanks a lot. I have seen Range in use before, but I didnt realize it is can return an object with one or multiple references. This is exaclty what I was looking for. — Phil, Oct 31 '15 at 17:14

Correctly saving reference in memory instead of activating

1 Answers1