Excel VBA Value Property

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 CellsVBA Range CellsRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more.” 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Value Property (wallstreetmojo.com)

How to use Value Property in VBA?

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.

Sub Value()

Range (“A1”).

End Sub

  • From 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 cell A1 by putting the value in an equal sign.

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.

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.

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 propertyVBA CELLS PropertyCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more, 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.

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

Sub Value()

Dim K As String

K = Range(“A1”).Value

End Sub

Step 3: Show the variable “k” result in the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Sub Value()

Dim K As String

K = Range(“A1”).Value

MsgBox K

End Sub

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

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.

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 UnionVBA ArrayListVBA UpperCase FunctionMatch in VBA