VBA Get Cell Value

Excel VBA Get Cell Value

In VBA there are different ways to apply the same logic to get the cell values and for this, we have a basic function called as CELLS where we can choose the coordinates to get the value stored in those cells or cell range. We all have used the method of selecting the range of using RANGE(“cell position”). VALUE. Similarly, we have other methods to get the value stored in different selected cells. Excel VBA Get Cell Value is one of the simplest and basic functions which all of us should know how to use it.

If we see the syntax, the CELLS function only requires the Row index and Column index coordinates where we just need to put the position of cells.

Examples of Get Cell Value in VBA Excel

We will learn how to use a Get Cell Value in Excel by using the VBA Code with the help of given examples.

Example #1

Let us consider a cell which is B2 with the cell content as “TEST” as shown below. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Write the subprocedure of VBA Get Cell Value.

Code:

Sub VBA_GetCellValue1()

End Sub

Step 3: Now directly use the message box and in the use CELLS with the coordinates as per B2 cell which comes at 2^nd row and 2^nd column.

Code:

Sub VBA_GetCellValue1()

MsgBox Cells(2, 2)

End Sub

Step 4: Run the code by pressing F5 or Play Button is mentioned below the menu bar. We will see the message which will pick up the value from cell B2 as shown below.

Example #2

Let us see another simple code to get the cell value. We will use the same cell B2 as used in example-1. In this example, we will see how to move the cursor to the required cell. For this, follow the below steps:

Step 1: For this again open a new module and write the subprocedure for VBA Get Cell Value.

Code:

Sub VBA_GetCellValue2()

End Sub

Step 2: Now use Range along with Select function to move the cursor.

Code:

Sub VBA_GetCellValue2()

Range("B2").Select

End Sub

Step 3: Now move cursor away from cell B2 and then run the code, we will see the cursor is now moved back to cell B2.

Step 4: This is how we can select the cell with and without cell value. There is one more way to get this one. For this, we will be using CELLS function and putting the same coordinates as used in example-1. The same procedure could be done using the CELLS function.

Code:

Sub VBA_GetCellValue2()

Cells(2, 2).Select

End Sub

Example #3

In this example, we will see how to get the cell value in a message using a different type of method. For this, follow the below steps:

Step 1: For this, again open Module and write the sub procedure.

Code:

Sub VBA_GetCellValue3()

End Sub

Step 2: In the name of VBA Get Cell Value as shown below. And in that, first, define a variable as String using DIM.

Code:

Sub VBA_GetCellValue3()

Dim Value As String

End Sub

Step 3: Using the defined variable VALUE, choose the value from the range cell B2.

Code:

Sub VBA_GetCellValue3()

Dim Value As String
Value = Range("B2").Value

End Sub

Step 4: Use the message box to see the value stored in cell B2.

Code:

Sub VBA_GetCellValue3()

Dim Value As String
Value = Range("B2").Value
MsgBox Value

End Sub

Step 5: Once we Run the code by pressing F5 or Play Button is mention below the menu bar, we will get the message box with value as TEST which is at cell B2.

Step 6: Similar code, can be used if we select the data type as VARIANT instead of STRING. Whereas VARIANT in VBA allows numbers and text both in it.

Code:

Sub VBA_GetCellValue3()

Dim Value As Variant
Value = Range("B2").Value
MsgBox Value

End Sub

Example #4

There is another way to use Get Cell Value in VBA which is also another simplest way. For this, follow the below steps:

Step 1: For this again open a new module and select the cell range from where we want to put. Let say we want to use the same cell range B2 which we have been using before examples.

Code:

Sub VBA_GetCellValue4()

Range("B2").Value

End Sub

Step 2: Now in manner use the cell where we want to put the value. And we are considering the cell A1 here.

Code:

Sub VBA_GetCellValue4()

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

End Sub

Step 3: Now run the code to see the output. The value from cell B2 will be moved to cell A1.

Pros of VBA Get Cell Value

• This is the basic operation where can easily get cell values from anywhere.
• It is very easy to copy or move the cell value or cursor to any place we want.

Things to Remember

• CELLS function is easy to implement by just putting the co-ordinates of ROW and COLUMN respectively.
• VBA Get Cell Value helps us to choose the cell value or put the cursor to the desired position.
• VBA Get Cell Value individually may not be much useful but we can use this operation with different types of code to get valuable results.
• Once done with code, please save the code in Macro Enable excel format to avoid losing the code.
• VBA Get Cell Value can be used with CELLS function and with RANGE function as well.

Recommended Articles

This is a guide to the VBA Get Cell Value. Here we discuss how to use Get Cell Value in excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. How to Use VBA Login?
2. VBA Month | Examples With Excel Template
3. How to Use Create Object Function in VBA Excel?
4. How to Use VBA IsError Function?