Menu
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?
399
To use a range or a single cell as a variable, first, you need to declare that variable with the range data type. Once you do that you need to specify a range of a cell to that variable using the range object.
When we declare a variable, we need to assign a data type. We can also assign objects as data types. To assign a value to declared object variables, we need to use the word “SET.” The word “Set” refers to a new object in VBA. For example, the particular range of the particular worksheet.
Type “d” in the Name control of the New Name dialog, then click in the “Refers to” control. Click the cell directly to the right of the cell containing the “d” to specify the cell that the new variable represents.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
