[excel] Setting a Sheet and cell as variable

I am new in VBA coding. Lets say I am retrieving value from Sheet3.Cell(23, 4) for a value, is there any way in the VBA code which let me set this as a variable?

For example, I have changed the interface and let the value stay at Sheet4.Cell(20,1), everywhere in my code which refer to Sheet3.Cell(23, 4) need to be changed to Sheet4.Cell(20, 1). I am thinking is there any best practice for coding VBA for situation like this?

This question is related to excel vba

The answer is


Yes. For that ensure that you declare the worksheet

For example

Previous Code

Sub Sample()
    Dim ws As Worksheet

    Set ws = Sheets("Sheet3")

    Debug.Print ws.Cells(23, 4).Value
End Sub

New Code

Sub Sample()
    Dim ws As Worksheet

    Set ws = Sheets("Sheet4")

    Debug.Print ws.Cells(23, 4).Value
End Sub

Yes, set the cell as a RANGE object one time and then use that RANGE object in your code:

Sub RangeExample()
Dim MyRNG As Range

Set MyRNG = Sheets("Sheet1").Cells(23, 4)

Debug.Print MyRNG.Value

End Sub

Alternately you can simply store the value of that cell in memory and reference the actual value, if that's all you really need. That variable can be Long or Double or Single if numeric, or String:

Sub ValueExample()
Dim MyVal As String

MyVal = Sheets("Sheet1").Cells(23, 4).Value

Debug.Print MyVal

End Sub