Returns or sets a Variant value that represents the value of the specified range.
Syntax
expression.Value (RangeValueDataType)
expression A variable that represents a Range object.
Parameters
Name | Required/Optional | Data type | Description |
---|---|---|---|
RangeValueDataType | Optional | Variant | The range value data type. Can be an XlRangeValueDataType constant. |
Remarks
When setting a range of cells with the contents of an XML spreadsheet file, only values of the first sheet in the workbook are used. You cannot set or get a discontiguous range of cells in the XML spreadsheet format.
The default member of Range forwards calls without parameters to Value. Thus, someRange = someOtherRange
is equivalent to someRange.Value = someOtherRange.Value
.
For ranges whose first area contains more than one cell, Value returns a Variant containing a 2-dimensional array of the values in the individual cells of the first range.
Assigning a 2-dim array to the the Value property will copy the values to the range in one operation. If the target range is larger than the array, the remaining cells will receive an error value.
Assigning an array to a multi-area range is not properly supported and should be avoided.
Example
This example sets the value of cell A1 on Sheet1 of the active workbook to 3.14159.
Worksheets("Sheet1").Range("A1").Value = 3.14159
This example loops on cells A1:D10 on Sheet1 of the active workbook. If one of the cells has a value of less than 0.001, the code replaces the value with 0 (zero).
For Each cell In Worksheets("Sheet1").Range("A1:D10")
If cell.Value < 0.001 Then
cell.Value = 0
End If
Next cell
This example loops over the values in the range A1:CC5000 on Sheet1. If one of the values is less than 0.001, the code replaces the value with 0 (zero). Finally it copies the values to the original range.
Public Sub TruncateSmallValuesInDataArea()
Dim dataArea As Excel.Range
Set dataArea = ThisWorkbook.Worksheets("Sheet1").Range("A1:CC5000")
Dim valuesArray() As Variant
valuesArray = dataArea.Value
Dim rowIndex As Long
Dim columnIndex As Long
For rowIndex = LBound(valuesArray, 1) To UBound(valuesArray, 1)
For columnIndex = LBound(valuesArray, 2) To UBound(valuesArray, 2)
If valuesArray(rowIndex, columnIndex) < 0.001 Then
valuesArray(rowIndex, columnIndex) = 0
End If
Next
Next
dataArea.Value = valuesArray
End Sub