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 propertyUsing CELLS PropertyCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more 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.

Get-Cell-Value-in-VBA

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Get Cell Value in Excel VBA (wallstreetmojo.com)

Examples of getting Cell Value in Excel VBA

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

You can download this VBA Get Cell Value Excel Template here – VBA Get Cell Value Excel Template

Example #1 – Using RANGE or CELLS Property

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

VBA Get cell value Example 1

To reference this cell, we can use a 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
VBA Get cell value Example 1-1

Now open the RANGE object.

Code:

Sub Get_Cell_Value()

Range(

End Sub
VBA Get cell value Example 1-2

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 the RANGE object.

Code:

Sub Get_Cell_Value()

Range("A1")

End Sub
VBA Get cell value Example 1-3

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

VBA Get cell value Example 1-4

As you can see above, the moment we put a 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
VBA Get cell value Example 1-5

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

vba get cell value example 1

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
Example 1-6

This is unlike the RANGE object where we could directly supply the cell address but 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 the cell A1 we are referring to the first row, so mention 1.

Example 1-7

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.

Example 1-8

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.

VBA Get cell value Example 1-9

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
VBA Get cell value Example 1-10

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
Example 2

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
Example 2-1

Once again, put a dot to see the IntelliSense list.

Intellisense list Example 2-2

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
Example 2-3

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
Example 2-4

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

vba get cell value example 2

Since there is a value of “INDIA” in cell A1, the same thing appeared in the message box as well. Like this, by the VBA valueVBA ValueIn VBA, the value property is usually used alongside the range method to assign a value to a range. It's a VBA built-in expression that we can use with other functions.read more 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
VBA Get cell value Example 3

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
VBA Get cell value Example 3-1

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 codeUsing VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

VBA Get cell value Example 3-2

Things to Remember

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

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>