Use of Range Object in VBA
Get and Set Cell value in VBA using Range() Object
There are three elements that are Workbooks, Worksheets and Ranges/Cells in VBA are most important. In Excel everything we are doing is within cells.
In VBA there is two way to get and set value of cell. Two different object that can be use for that.
- Range()
- Cells()
To get or to access value from cell we have to use “Value” property of these Object.
Range() Object in VBA
Range() can use to manipulate cells of Excel using VBA. Range() object takes one argument as string type and argument have cell address or range of cell address.
Syntax of Range() object in VBA
Range(cell_address)
Where…
cell_address: Address of cell that cell from where we want to get value.
Above syntax can just select cell. If we want to access slected cell value then we have to use “Value” property. Its syntax is as under.
Range(cell_address).Value
Where…
Value: it is property of Range() to access value of specified cell
Example:1: Get or read Value from cell using Range() Object
Get or read Value from cell using Range() Object in VBA Video in Hindi
In this example we get value of “b3” cell
write below code in button click event.
Private Sub cmdBttnGetValue_Click() Dim name As String name = Range("B3").Value MsgBox (name) End Sub
Output:
Example:2: Get Cell Value from Other Sheet Using Range() Object
Example-2 Video in Hindi
In this example we put button in “Sheet1”. When user click on this button at that time get value of “b3” cell of “MyDetail” sheet.
write below code in button click event.
Private Sub cmdBttnGetValue_Click() Dim name As String name = Sheets("MyDetail").Range("B3").Value MsgBox (name) End Sub
Output:
Example:3: Set Cell Value of Sheet Using Range() object
Example-3 Video in Hindi
write below code in button click event.
Private Sub cmdBttnGetValue_Click() Range("B3").Value = "Johan" End Sub
Example:4: Get value from InputBox and Set to cell using Range() object
Example-4 Video in Hindi
When user click on button, InputBox display to get value from user and set entered value to “b3” cell.
write below code in button click event.
Private Sub cmdBttnGetValue_Click() Dim name As String name = InputBox("Enter Your Name : ", "Name") Range("B3").Value = name End Sub
Output:
Example:5: Get value from other Sheet’s Cell and Set to other sheet’s cell using Range() object
Example-5 Video in Hindi
There are two sheets “MyDetail” and “Sheet1”. In this program When user click on button, get value from “b3” cell of “MyDetail” sheet and set to “b4” cell of “Sheet1” sheet.
write below code in button click event.
Private Sub cmdBttnGetValue_Click() Dim name As String ' get value from MyDetail sheet cell and store to variable name = Sheets("MyDetail").Range("B3").Value ' set variable value to cell Range("B4").Value = name End Sub