Read and Write Cell Value using VBA

In this blog, we will learn about getting data from our worksheet, writing data into a worksheet with the help of Cell Value. So let us start learning to read and write Cell Value using VBA.

Working with Cells using VBA

A cell is the basic building block of data in a worksheet and is also part of a range. There are two methods to extract data. Cells and Range method. We have divided the article into two parts with a number of examples to learn from each part.

You can also download the practice workbook to follow along with us.

Read Cell Value

We need to get value from a cell in the worksheet into our VBA code for many purposes. There are some examples to learn from.

Example 1- Get the Value of Active Cell

Active Cell is the selected cell in which data would be entered if you started to type. We can get the value in Active Cell as follows.

Sub Active_Cell()
'checking the condition if active cell is non empty cell
If ActiveCell.Value <> "" Then
'display the value in active cell
MsgBox ("Value in Active Cell:= " & ActiveCell.Value)
End If
End Sub

Use ActiveCell.Value to get the value in the active cell. We have used a condition that checks if the active cell is not empty and then prints the result containing the value in Active Cell using the MsgBox function.

Nothing would happen if the Active Cell is an empty cell because the condition of a nonempty active cell would not be specified.

Press Alt and F8 keys to open the list of available macros.

Example 2 – Assign to a Variable

We can assign the value of a worksheet cell to a variable. We need to make sure that the value in the worksheet cell is compatible with the data type of the container variable using an IF condition.

For instance, let us suppose we want to pick a numerical value from cell C5 and assign it to the RollNumber variable in the VBA code. RollNumber is of Double Data Type. Cell C5 is accessed using the Cells method. The cells method returns a reference to a cell as an intersection of a particular row and column.

Cells(<row_number>,<column_number>).Value

Now use the following Sub Procedure to extract the value from cell C5 and assign it to the RollNumber variable only if it is numerical.

Sub Roll_Number()
'get value from C3 and assign it to variable
Dim RollNumber As Double
'get value of cell C3 (5th row and 3rd column) and assign it using = (assignment operator)
RollNumber = Cells(5, 3).Value
'print the value of variable
MsgBox ("Roll number is: = " & RollNumber)
End Sub

This is how we read cell value, now we would write a Cell Value

Write Value in Cell

We can write a value in an excel cell or a range of cells with the help of VBA. We can also input the value to write in the worksheet using the InputBox Function.

Example 1 – Enter Value in Cell

We can use the Cells method to add value to a cell. Let us say we need to print “ExcelUnlocked” in cell A1 using VBA. A1 is the intersection of 1st row and 1st column.

Cells(1,1).Value="ExcelUnlocked"

We can do the same, with the help of the Range method also.

Range("A1").Value="Learning with ExcelUnlocked"

When you run the following Sub Procedure, you see that the text strings are written into the current worksheet cell A1 and A2.

Sub Write_inCell()
'write into a cell
Cells(1, 1).Value = "ExcelUnlocked"
Range("A2").Value = "Learning With ExcelUnlocked"
End Sub

You can write in Active Cell using this statement.

ActiveCell.Value="This would be written in Active Cell"

Example 2 – Using InputBox to Write in Active Cell

We can use an InputBox to enter a value in the active cell.

Sub Input_Write()
'take input and write to active cell
ActiveCell.Value = InputBox("Enter the value for cell " & ActiveCell.Address)
End Sub

Here Active.Cell would return a cell reference of the cell which is currently selected. The value entered in the InputBox would be written in active cell.

Note that you can also assign the Input_Write to a Button Shape.

So this is how we read and write cell value using VBA.