CODEWITHSUNDEEP
vbaexcel
Phil
Phil

Reputation: 7597

Correctly saving reference in memory instead of activating

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.

Upvotes: 0

Views: 56

Answers (1)

WGS
WGS

Reputation: 14179

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:

enter image description here

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:

enter image description here

Upvotes: 1

Related Questions