VBA Value Property

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.

VBA Value

How to use Value Property in VBA?

You can download this VBA Value Function Excel Template here – VBA Value Function Excel Template

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”)

Code:

Sub Value()

  Range ("A1")

End Sub

VBA Value Example 1

  • 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.

Code:

Sub Value()

  Range ("A1").

End Sub

VBA Value Example 1-1

  • Form this variety of options, select the property “VALUE.”

Code:

Sub Value()

  Range("A1").Value

End Sub

VBA Value Example 1-2

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

Code:

Sub Value()

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

End Sub

Example 1-3

  • Ok, this will insert the value “Welcome to VBA” to the cell A1.

Example 1-4

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

Code:

Sub Value()

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

End Sub
  • This will insert the value from the cell A1 to A5.

Example 1-5

  • 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.

Code:

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 1-6

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.

Using CELLS Property

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.

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 I have entered the value “Welcome to VBA.”

Get Cell Value 1

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 result of the variable “k” 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 the result of cell A1 value in the message box.

Get Cell Value 1-2

We can also use the RANGE object to get the data of the 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

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.

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.

Recommended Articles

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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>