Get Cell Value in Excel VBA

What is Get Cell Value in Excel VBA?

Get Cell Value in Excel VBA refers to the process of extracting a cell’s value in Excel using VBA code. It is a fundamental operation that is used in many Excel VBA projects. You can use the Get Cell Value method to retrieve the contents of a single cell or a range of cells.

This article will explore what Get Cell Value in Excel VBA means and how you can use it in different scenarios. First, let’s look at an example demonstrating how to retrieve a specific cell’s value in Excel VBA and take action based on the value. In this case, we retrieve the value of cell A1 in the active worksheet and check if it equals the string “Hello.”

If the value is “Hello,” we display a message box with the exact text using the MsgBox function. It is a simple example of how you can use VBA to automate certain tasks based on the contents of specific cells in your workbook.

How to Get Cell Values in Excel VBA?

Let’s explore some examples on how to get cell value in Excel VBA.

Example #1 – Using RANGE or CELLS Property

One of the most straightforward ways to get cell value in Excel VBA is by using the RANGE or CELLS property. The RANGE property is used to select a specific range of cells, while the CELLS property is used to select a single cell.

1. Using RANGE property

In this example, we’ll use the Range property to select a specific range of cells and then act on those cells, such as changing their value.

• Step 1: Open a new Excel workbook and press Alt + F11 to open the Visual Basic Editor.

• Step 2: Insert a new module by clicking “Insert” from the top menu and selecting “Module.”

• Step 3: Type the following code in the module:

Sub ChangeRange()
‘Select the range A1:A10 and change the values to 0
Range(“A1:A10”).Value = 0
End Sub

The first line of the procedure is a comment that begins with an apostrophe (‘). Comments are used to explain what the code is doing in human-readable language.

Explanation:

• The second line is the actual code that performs the action.
• It uses the “Range” function to specify the range of cells to be changed (A1 to A10) and then sets their values to zero using the equals sign (=) and the number 0. Thus, the RANGE function is used for Excel VBA get value of cell.

• Step 4: Save the module by clicking “File” from the top menu, then selecting “Save” and giving it a name. Make sure to save the file as a “Macro-Enabled Workbook.”

• Step 5: Close the VBA Editor by clicking on the “X” button at the top right corner of the editor window. Enter some values in the range A1:A10.

• Step 6: Press Alt + F8 to open the “Macro” dialog box.

• Step 7: Select the “ChangeRange” macro and click on “Run.”

• Step 8: Check the range A1:A10, you will see that all the values have been changed to 0.

The purpose of this program was to select a group of cells in an Excel spreadsheet and set their values to zero.

In other words, it will replace whatever values were previously in those cells with the number zero.

2. Using CELLS property

In this example, we’ll use the VBA Cells property to select a specific cell and then perform an action on that cell, such as changing its value.

• Step 1: Open an Excel workbook and press Alt + F11 to open the Visual Basic Editor, as shown in the above example.

• Step 2: Insert a new module by clicking “Insert” from the top menu and selecting “Module.”

• Step 3: Type the following code in the module:

Sub ChangeCell()
‘Select cell B1 and change the value to 0
Cells(1, 2).Value = 0
End Sub

Explanation:

• The second line uses the “Cells” function to specify the location of the cell to be changed (in this case, row 1 and column 2, which corresponds to cell B1).
• It then sets its value to zero using the equals sign (=) and the number 0.

• Step 4: Save the module “”“”“”and enter a value in cell B1 of your Excel worksheet.

• Step 5: Press Alt + F8 to open the “Macro” dialog box. Select the “ChangeCell” macro and click on “Run.”

• Step 6: Check cell A1, you will see that its value has been changed to 0.

This program selects a single cell in an Excel spreadsheet (specifically, cell B1) and set its value to zero.

In other words, it will replace whatever value was previously in that cell with the number zero.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2 – Get Value from Cell in Excel VBA

Let’s say you have a worksheet with the following data. And you want to get the value of cell B2 using VBA. Here’s how you can do it:

• Step 1: Open a new Excel workbook and insert a new module. Type the following code in the module:

Sub GetValueFromCell()
Dim cellValue As Integer
cellValue = Range(“B2”).Value
MsgBox “The value of cell B2 is: ” & cellValue
End Sub

Explanation:

• The first line of the procedure declares a new variable called “cellValue” as an integer data type. This variable will be used to store the value of cell B2.
• The second line of the procedure assigns the value of cell B2 to the “cellValue” variable using the “Range” function. The Range function is used to specify the location of the cell (in this case, cell B2), and the “.Value” property is used to retrieve its value.
• The third line of the procedure uses the “MsgBox” function to display a message box with the value of the “cellValue” variable.
• The “&” symbol is used to concatenate the string “The value of cell B2 is: “ with the actual value of the cell stored in the “cellValue” variable.

• Step 2: Save the module and press Alt + F8 to open the “Macro” dialog box and select GetValueFromCell and then click on Run.

• Step 3: You will see a message box with the value of cell B2, which is 20.

The purpose of this program is to get the value of a single cell in an Excel spreadsheet (specifically, cell B2) and display it in a pop-up message box.

Example #3 – Get Value from One Cell to Another Cell

Say you have a worksheet with the following data and want to get the value of cell B2 and then copy it to cell A4. Here’s how you do it:

• Step 1: Open a new module in the VBA Editor. Now type the following code.

Sub GetValueAndCopy()
Dim cellValue As Integer
cellValue = Range(“B2”).Value
Range(“A4”).Value = cellValue
MsgBox “The value of cell B2 has been copied to cell A4.”
End Sub

Explanation:

The procedure declares a new variable called “cellValue” as an integer data type. This variable will be used to store the value of cell B2.

The following line of the procedure assigns the value of cell B2 to the “cellValue” variable using the “Range” function. The Range function is used to specify the location of the cell (in this case, cell B2), and the “.Value” property is used to retrieve its value.

We use the “MsgBox” function to display a message box with the value of the “cellValue” variable. The “&” symbol is used to concatenate the string “The value of cell B2 is: “ with the actual value of the cell, which is stored in the “cellValue” variable.

• Step 2: Save the module and press Alt + F8 to open the “Macro” dialog box and select GetValueAndCopy and then click on Run.

• Step 3: You will see a message box indicating that the value of cell B2 has been copied to cell A4.

In this example, we used the VBA Range property to get the value of cell B2 and assigned it to a variable called cellValue. Then we used the Range property again to set the value of cell A4 to cellValue.

Finally, we displayed a message box to confirm the value was copied.

Important Things to Note

Here are some important things to keep in mind when using Get Cell Value in Excel VBA:

1. You should specify where the cell value resides in the workbook and worksheet. Excel will assume you are referring to the active workbook and worksheet.
2. When retrieving a cell’s value, ensure that the cell is not empty. You may encounter run-time errors in your VBA code if the cell is empty.
3. If you need to retrieve the value of multiple cells, it is recommended to use the Range property with a range of cells instead of the Cells property. It will improve the performance of your VBA code.
4. The value of any variable in VBA can be found by hovering the mouse over the variable   while debugging the code or by using the Debug.Print statement.
5. You can also use the RANGE function in Excel VBA get cell value from range.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Get Cell Value in Excel VBA, with its methods and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Get Cell Value in Excel VBA. Here we discuss how to get value from cell in excel VBA, with examples & downloadable excel template. You can learn more from the following articles –

• VBA Value Property
• VBA Data Type
• VBA InStr