Setting a Sheet and cell as variable

Question

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

User · Answer

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

User · Answer

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

[excel] Setting a Sheet and cell as variable

Examples related to excel

Examples related to vba