Get Cell Value in Excel VBA

Get Cell Value with Excel VBA

A cell is an individual cell and is also a part of a range, technically there are two methods to interact with a cell in VBA and they are the range method and the cell method, the range method is used like range(“A2”).Value which will give us the value of the A2 cell or we can use the cell method as cells(2,1).value which will also give us the value of A2 cells.

Be it excel working or VBA working we all need work with cell or cells because all the data will be stored in cells so it all boils down to how well we know about cells in VBA. So, if cells are such a crucial part of the VBA then it is important to understand them well and if you are a starter with regards to VBA cells then this article will guide you on how to get Cell Values in Excel VBA in detail.

First thing first we can reference or work with cells in VBA in two ways i.e. by using CELLS property and RANGE object. Why CELLS is a property and why RANGE is an object is a different analogy and later in the article, we will get to that point.

Examples of Get Cell Value in Excel VBA

Below are the examples of Get Cell Value in Excel VBA.

Example #1 – Using RANGE or CELLS Property

For example, in cell A1 we have a value of “India”.

To reference this cell we can use CELLS property or RANGE object, let’s see both of them in detail.

Using Range Property

First, start the macro procedure.

Code:

Sub Get_Cell_Value()

End Sub

Now open RANGE object.

Code:

Sub Get_Cell_Value()

Range(

End Sub

The first argument of this object is “Cell1” i.e. which cell we are referring to. In this case, it is cell A1, so we need to supply the cell address in double-quotes for RANGE object.

Code:

Sub Get_Cell_Value()

Range("A1")

End Sub

Since only one cell is referring to other parameters are irrelevant, so close the bracket and put a dot to see the intellisense list.

As you can see above the moment we put dot we can see all the available intellisense list of properties and methods of range object.

Since we are selecting the cell we need to choose the “SELECT” method from the intellisense list.

Code:

Sub Get_Cell_Value()

Range("A1").Select

End Sub

Now select the cell other than A1 and run the code.

It doesn’t matter which cell you have selected the moment you run the code it has chosen the mentioned cell i.e. A1 cell.

Using Cells Property

Similarly, we use CELLS property now.

Code:

Sub Get_Cell_Value()

Range("A1").Select
Cells(

End Sub

This is unlike RANGE object where we could directly supply the cell address but using this CELLS property we cannot do like that.

The first argument of this property is “Row Index” i.e. which row we are referring to. Since we are selecting the cell A1 we are referring to the first row, so mention 1.

The next argument is the “Column Index” i.e. which column we are referring to. A1 cell column is the first column, so enter 1.

Our code reads CELLS (1, 1) i.e. first row first column = A1.

Now put a dot and see whether you get to see the intellisense list or not.

With CELLS properties we cannot see any IntelliSense list, so we need to absolutely sure of what we are writing. Enter “Select” as the method.

Code:

Sub Get_Cell_Value()

Range("A1").Select
Cells(1, 1).Select

End Sub

This will also select cell A1.

Example #2 – Get Value from Cell in Excel VBA

Selecting is the first thing we have learned, now we will see how to get value from cells. Before we select the cell we need to define the variable to store the value from the cell.

Code:

Sub Get_Cell_Value1()

Dim CellValue As String

End Sub

Now mention the cell address by either using RANGE object or CELLS property. Since you are a beginner use RANGE object only because with RANGE object we get to see the intellisense list.

For the defined variable put an equal sign and mention the cell address.

Code:

Sub Get_Cell_Value1()

Dim CellValue As String

CellValue = Range("A1")

End Sub

Once again put a dot to see the intellisense list.

From the vba intellisense list choose “Value” property to get the value from the mentioned cell.

Code:

Sub Get_Cell_Value1()

Dim CellValue As String

CellValue = Range("A1").Value

End Sub

Now the variable “CellValue” holds the value from the cell A1. Show this variable value in the message box in VBA.

Code:

Sub Get_Cell_Value1()

Dim CellValue As String

CellValue = Range("A1").Value

MsgBox CellValue

End Sub

Ok, run the code and see the result in a message box.

Since there is a value of “INDIA” in cell A1 the same thing appeared in the message box as well. Like this by using the “VALUE” property of the cell we can get the value of the cell.

Example #3 – Get Value from One Cell to Another Cell

We know how to get value from the cell using vba, now the question is how to insert value to the cell. Let’s take the same example only, for cell A1 we need to insert the value of “INDIA” and this can be done from the below code.

Code:

Sub Get_Cell_Value2()

Range("A1").Value = "INDIA"

End Sub

This will insert the value of “INDIA” to cell A1, similarly to get value from one cell to another we can write the code as below.

Code:

Sub Get_Cell_Value2()

Range("A5").Value = Range("A1").Value

End Sub

Let me explain the code to you.

“For the cell A5 we need the value which is from the cell A1 value”, that’s all this code says. So this will get the value from cell A1 to A5 using VBA code.

Things to Remember

• Inserting value to cells and getting value from cell requires the VBA “VALUE” property to be used.
• Using CELLS property we can select only one cell but using RANGE object we can select multiple cells.

Recommended Articles

This has been a guide to Get Cell Value in Excel VBA. Here we discuss the examples on how to get cell value using a range of cells property in excel VBA along with a downloadable excel template. Below you can find some useful excel VBA articles –

• Excel VBA Variable in Range
• Split String into Array in VBA
• Calculating Investment Return in Excel
• VBA Set Range
• Compare Excel Dates
• VBA On Error Resume Next Examples
• Range Cells in VBA
• Active Cell in VBA
• Select Cell in VBA