“Value” Property in VBA
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 VBA coding. In this article, we will explain to you about VBA “Value” property, how to insert or set values, how to get the VBA value from the cell and many other things.
In our 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 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.
VBA Value Examples
Consider the following Examples.
Example #1 – VBA 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 RANGE object now put a dot (.) to see the IntelliSense list of all the properties and method 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 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 below-using code.
Sub Value() Range("A1:A5").Value = "Welcome to VBA" End Sub
This will insert the value “Welcome to VBA” from the cell A1 to A5.
If you want to insert values to different cell 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 VBA Value Using CELLS Property
Not through RANGE object but also using VBA CELLS property we can insert values. But one of the problems with CELLS object is we don’t get the access of IntelliSense list as we got for RANGE object.
Here we need to mention the row & column numbers we need the insert the VBA value. For an 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 to multiple cells by using CELLS property, this is unlike our RANGE object.
Example #3 – VBA Get Cell Value
We have seen how to insert values to the cells, now we will see how to get VBA 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, 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 an 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 “Type Mismatch” error in VBA.
The reason why we get this error because when the object variable set to more than one cell “value” property doesn’t really understand which cell value to be given, so it can get single cell value at a time.
You can download the Excel Template Here – VBA Value Function Excel Template
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 downloads to excel template. Below are some useful excel articles related to VBA –