VBA Value Property

Value is a property in VBA 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, which does not necessarily mean that one must use the Value property with only the Range method. We can use it with other functions as well.

Early in our learning with VBA, we are curious to learn how to store the data in the cells. If you are that curious, you need to understand the "Value" property. This article will explain the "Value" property, insert or set values, get value from the cell, and many other things.

In one of the earlier articles, we discussed “VBA Range Cells” The Range object can help us refer to single and multiple cells. To use the RANGE object first, we need to decide for which cell we need to insert the value and what value we will insert.

Example #1 - Range Object to Assign Values to Cells

• If you want to insert a value to cell A1, then you should refer the cell A1 like this Range("A1")

Code:

Sub Value()

  Range ("A1")

End Sub

• After referring to 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.

Code:

Sub Value()

  Range ("A1").

End Sub

• From this variety of options, select the property "VALUE."

Code:

Sub Value()

  Range("A1").Value

End Sub

• Once the VALUE property is selected, we need to set the value to cell A1 by putting the value in an equal sign.

Code:

Sub Value()

  Range("A1").Value = "Welcome to VBA"

End Sub

• It will insert the value "Welcome to VBA" to cell A1.

• If you want to insert the same value to multiple cells, refer to the cells like the code below.

Code:

Sub Value()

  Range("A1:A5").Value = "Welcome to VBA"

End Sub

• It will insert the value from cell A1 to A5.

• If you want to insert values to different cells but not for the cell series, then we need to use code and the cell address in separate arguments like the one below.

Code:

Sub Value()

  Range("A1,A5,B4,C2").Value = "Welcome to VBA"

End Sub

• It will insert the text "Welcome to VBA" to cells A1, A5, B4, and C2.

Example #2 - Insert Value using CELLS Property

Not through the RANGE object but also using the VBA CELLS property, we can insert values. But, one of the problems with the CELLS object is we do not get access to the IntelliSense list as we got for the RANGE object.

Here, we need to mention the row and column numbers. Then, we need to insert the value. For example, if you want to insert the value to cell A1, 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 the B5 cell.

We cannot insert values into multiple cells by using the CELLS property. This is unlike our RANGE object.

Example #3 - Get Cell Value

We have seen how to insert values into the cells. Now, we will see how to get values from cells.

Step 1: Define a variable as String.

Code:

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, we have entered the value "Welcome to VBA."

So the code will be k = Range(“A1”).Value

Code:

Sub Value()

  Dim K As String

  K = Range("A1").Value

End Sub

Step 3: Show the variable "k" result in the VBA message box.

Code:

Sub Value()

  Dim K As String

  K = Range("A1").Value

  MsgBox K

End Sub

By running the code, we should see the result of the cell A1 value in the message box.

We can also use the RANGE object to get the data of cell A1. The below code will show you the same.

Code:

Sub Value()

  Dim K As String

  Set CellValue = Range("A1")

  MsgBox CellValue

End Sub

It should also get the value of cell A1 in the message box.

Example 4 - Error Value if More than One Cell Value Requires

Look at the below code.

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.

We get this error when we set the object variable to more than one cell "value." This is because the property does not understand which cell value is given, so it can get a single cell value at a time.

This article 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: -

• VBA Union
• VBA ArrayList
• VBA UpperCase Function
• Match in VBA