Excel VBA Value Property
Value is a property in VBA which is mostly used with the range method to assign a value to a specific range, it is an inbuilt expression in VBA, for example, if we use range(“B3”).value = 3 this will assign cell B3 a value of 3, not necessarily that value property is to be used with only range method we can use it with other functions as well.
Early on in our learning with VBA, we are so curious to learn about how to store the data in the cells. If you are that curious, then you need to understand “Value” property. In this article, we will explain to you about “Value” property, how to insert or set values, how to get value from the cell, and many other things.
In one of the earlier articles, we have discussed “VBA Range Cells.” Range object can help us to refer to a single cell as well as multiple cells. To use the RANGE object first, we need to decide for which cell we need to insert the value and what is the value we are going to insert.
How to use Value Property in VBA?
Example #1 – Range Object to Assign Values to Cells
- For example, if you want to insert a value to the cell A1, then you should refer the cell A1 like this Range(“A1”)
Sub Value() Range ("A1") End Sub
- After referring the cell using the RANGE object, now put a dot (.) to see the IntelliSense list of all the properties and methods associated with this object.
Sub Value() Range ("A1"). End Sub
- Form this variety of options, select the property “VALUE.”
Sub Value() Range("A1").Value End Sub
- Once the “VALUE” property is selected, we need to set the value to the cell A1 by putting the value in equal sign.
Sub Value() Range("A1").Value = "Welcome to VBA" End Sub
- Ok, this will insert the value “Welcome to VBA” to the cell A1.
- If you want to insert the same value to multiple cells, then refer the cells like the below code.
Sub Value() Range("A1:A5").Value = "Welcome to VBA" End Sub
- This will insert the value from the cell A1 to A5.
- If you want to insert values to different cells but not for series of the cell, then we need to use code and the cell address in separate arguments like the below.
Sub Value() Range("A1,A5,B4,C2").Value = "Welcome to VBA" End Sub
- This will insert the text “Welcome to VBA” to the cells A1, A5, B4, and C2 cells.
Example #2 – Insert Value using CELLS Property
Not through the RANGE object but also using VBA CELLS property, we can insert values. But one of the problems with the CELLS object is we don’t get access to the IntelliSense list as we got for the RANGE object.
Here we need to mention the row & column numbers we need the insert the value. For example, if you want to insert the value to the cell A1, then the code is CELLS(1,1). If you want to insert the value to the cell B5, then the code is CELLS(5,2), i.e., equal to B5 cell.
We cannot insert values into multiple cells by using CELLS property. This is unlike our RANGE object.
Example #3 – Get Cell Value
We have seen how to insert values to the cells, now we will see how to get values from cells.
Step 1: Define a variable as String.
Sub Value() Dim K As String End Sub
Step 2: For this variable “k,” we will assign the value of the cell A1. In cell A1 I have entered the value “Welcome to VBA.”
So the code will be k = Range(“A1”).Value
Sub Value() Dim K As String K = Range("A1").Value End Sub
Step 3: Show the result of the variable “k” in the VBA message box.
Sub Value() Dim K As String K = Range("A1").Value MsgBox K End Sub
By running the code, we should the result of cell A1 value in the message box.
We can also use the RANGE object to get the data of the cell A1. The below code will show you the same.
Sub Value() Dim K As String Set CellValue = Range("A1") MsgBox CellValue End Sub
This should also get the value of the cell A1 in the message box.
Example 4 – Error Value if More than One Cell Value Requires
For example, look at the below code.
Sub Value() Dim K As Range Set CellValue = Range("A1:A5") MsgBox CellValue End Sub
If you run the above code, we will get the “Type Mismatch” error.
The reason why we get this error because when the object variable is set to more than one cell “value,” property doesn’t really understand which cell value to be given, so it can get a single cell value at a time.
This has been a guide to VBA Value Property. Here we learn how to get the VBA object cell value and range to assign values to cells along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –