Get Cell Value in 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: the range method and the cell method. We can use the range method like range(“A2”). The value will give us the value of the A2 cell, or we can use the cell method as cells(2,1). The value will also give us the value of A2 cells.

Be it Excel or VBA, we all need to work with cells because it will store all the data 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. So, if you are a starter regarding VBA cells, this article will guide you on how to get cell values in Excel VBA in detail.

First, we can reference or work with cells in VBA in two ways: using CELLS property and RANGE object. Of course, why CELLS is a property and why RANGE is an object is a different analogy. Later in the article, we will get to that point.

Below are the examples of getting cell values in Excel VBA.

Example #1 - Using RANGE or CELLS Property

In cell A1 we have a value of “India.”

We can reference this cell with a CELLS property or RANGE object. Let us see both of them in detail.

Using Range Property

First, start the macro procedure.

Code:

Sub Get_Cell_Value() End Sub

Now open the RANGE object.

Code:

Sub Get_Cell_Value() Range( End Sub

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

Code:

Sub Get_Cell_Value() Range("A1") End Sub

Since only one cell refers to other parameters is irrelevant, close the bracket and put a dot to see the IntelliSense list.

As you can see above, the moment we put a dot, we can see all the available IntelliSense lists of properties and methods of range objects.

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 does not matter which cell you select when you run the code. It has chosen the mentioned cell, i.e., the A1 cell.

Using Cells Property

Similarly, we use the CELLS property now.

Code:

Sub Get_Cell_Value() Range("A1").Select Cells( End Sub

Unlike the RANGE object, we could directly supply the cell address. However, using this CELLS property, we cannot do that.

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

The next argument is the “Column Index,” i.e., which column we refer to. For example, the 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.

We cannot see any IntelliSense list with CELLS properties, so we must be sure 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 using either the RANGE object or the CELLS property. Since you are a beginner, use the RANGE object only because we can see the IntelliSense list with the RANGE object.

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 the “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 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

Run the code and see the result in a message box.

Since there is a value of “INDIA” in cell A1, the same thing also appeared in the message box. Like this, we can get the value of the cell by the VBA 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 a value into the cell. Let us take the same example only. For cell A1, we need to insert the value of “INDIA,” which we can do from the code below.

Code:

Sub Get_Cell_Value2() Range("A1").Value = "INDIA" End Sub

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

Code:

Sub Get_Cell_Value2() Range("A5").Value = Range("A1").Value End Sub

Let me explain the code to you.

For cell A5, we need the value 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.

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