Setting a sheet and cell as a variable

Question

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

For example, I changed the interface and left the value on Sheet4.Cell (20,1), everywhere in my code that relates to Sheet3.Cell (23, 4), I need to change it to Sheet4.Cell (20, 1). I think there is some best practice for VBA coding for such a situation?

Answer 1

Yes. To do this, make sure you declare a worksheet.

for instance

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

Answer 2

Yes, set the cell as a RANGE object once, and then use this RANGE object in your code:

Sub RangeExample()
Dim MyRNG As Range

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

Debug.Print MyRNG.Value

End Sub

, , . , :

Sub ValueExample()
Dim MyVal As String

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

Debug.Print MyVal

End Sub