WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » Get Cell Value in Excel VBA

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.

Get Cell Value in VBA

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 value 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 code.

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.

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 –

  • VBA Variable Range
  • Split String into Array in VBA
  • Range Cells in VBA
  • Active Cell in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Get Cell Value Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More