Get Cell Value In Excel VBA - WallStreetMojo
Có thể bạn quan tâm
VBA Resources
- E-Books
- Career Guides
- Interview Prep Guides
- Free Practice Tests
- Excel Cheatsheets
- Basic Operations
- Selection and Manipulation
- Advanced Operations
💡 Expert-Led Sessions📊 Build Financial Models⏳ 60+ Hours Learning
ENROLL NOW Get Cell Value in Excel VBAPublication Date :
07 Dec, 2019
Blog Author :
Jeevan A Y
Edited by :
Ashish Kumar Srivastav
Reviewed by :
Dheeraj Vaidya, CFA, FRM
Share
GET: WSM ALL COURSES ACCESSDownload FREE Get Cell Value Excel Template and Follow Along!VBA Get Cell Value Excel Template.xlsmTable Of Contents
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: 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.
Examples of getting Cell Value in Excel VBA
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 SubNow open the RANGE object.
Code:
Sub Get_Cell_Value() Range( End SubThe 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 SubSince 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 SubNow, 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 SubUnlike 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 SubThis 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 SubNow, 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 SubOnce 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 SubNow, 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 SubRun 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 SubIt 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 SubLet 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.
Things to Remember
- 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.
Từ khóa » Visual Basic Excel Get Cell Value
-
VBA Cell Value - Get, Set, Or Change - Automate Excel
-
Excel VBA Get Cell Value - EduCBA
-
Range.Value Property (Excel) - Microsoft Docs
-
VBA Enter Value In A Cell (Set, Get, And Change) - Excel Champs
-
How To Get Cell Value By Row And Column In Excel VBA - ExcelDemy
-
VBA Ranges - Getting And Setting Cell Values
-
How To Get Cell Value With VBA - Excel Tutorials - Officetuts
-
Get Cell Value - VBA / Excel / Access / Word
-
Getting Values From Another Sheet In Excel Using VBA - Chartio
-
How To Set Variable To Cell Value In Excel VBA? - GeeksforGeeks
-
Excel VBA Cell Value - Code Included - YouTube
-
Excel VBA Value And Value2: Step-by-Step Guide And 8 Examples
-
Cell Values And Displayed Text
-
Get Values From Other Sheet Using VBA - Stack Overflow