Description of the Value2 property for the Range object in Excel

[N I C K]

Hi folks,
I am trying to do some coding in Excel using Visual Basic Applications. I have done some basic coding in VBA, so you can consider me as rookie in VBA. I always get confused while studying about the Value2 property. This property is used for the range object in an Excel. So thought that I would get some help from you guys.!! So please provide the description of the Value2 property for the Range object in Excel Any other information related to the topic would be grateful.!!

[chroma]

I would like to explain you the concepts, so that later on you will find easy to understand this topic. Microsoft Excel includes a property Visual Basic for Applications called Value2. The Value2 property, which you can use for the Range object, is almost identical to the Value property except that the Value2 property does not use the Currency data types and date. Depending on how a cell is formatted, the two properties may return different values for the same cell. Hope that you got the basic point.

[Rob Dizzle]

You should know that Value2 property is not available in versions prior to Excel 97. Therefore, if a cell is formatted with a number format to currency or number format date, the Value property can not return the expected value of the underlying cell. This applies to the Microsoft Office Excel 2003, and the lower versions of it. Since you have not described the version that you are using, I thought that this information would be useful for you.!!

[NIcaBoy]

In the following example, the Value and Value2 properties return different results for the same object cell. To view the results, proceed as follows :

1. Close and save open workbooks and then create a new workbook.
2. Enter the following in Sheet1 :
   

    A1: 1.23456789
       A2: Feb 3, 97

3. Click on cell A1 and then click cell menu format. Under the number tab, click Currency in the Category list, then click OK.
4. Click cell A2, then click on cell in the format menu.
5. Click the number tab, click Date in the Category list and click OK.
6. Start the Visual Basic Editor (press ALT + F11).
7. On the Insert menu, click Module.
8. Then you will have to enter the proper coding in module.

[Zenon]

You can enter the following code in module, in the above step described by 'NIcaBoy'. The code would be :

Sub Value_vs_Value2()

         'Creates a cell value formatted as Currency
         Range("A1").Formula = "$1.23456789"
 
         'Creates a cell value formatted as a Date
         Range("A2").Formula = "2/3/1997"

         MsgBox "Currency returned by Value property = " & _
            Sheet1.Range("A1").Value

         MsgBox "Currency returned by Value2 property = " & _
            Sheet1.Range("A1").Value2

         MsgBox "Date returned by Value property = " & _
            Sheet1.Range("A2").Value

         MsgBox "Date returned by Value2 property = " & _
            Sheet1.Range("A2").Value2

         Range("A3").Value = Range("A1").Value

         MsgBox "Currency set by Value property = " & _
             Range("A3").Value

         Range("A4").Value = Range("A1").Value2

         MsgBox "Currency set by Value2 property = " & _
            Range("A4").Value2

      End Sub

[Kirmiac]

The value of underlying cell in cell A1 is 1.23456789. However, the Value property returns 1.2346. This is because Excel stores numbers in currency format integer that is multiplied by 10 000 to produce a fixed-point numbers with fifteen digits to the left of the decimal point and four digits to the right of the decimal. For cells formatted as currency, the Value2 property returns the cell value underlying real. The value of underlying cell in cell A2 is 35,464, which is the serial number, 3 Feb 97. The Value property returns a date in short date format. For cell formatted as a date, the Value2 property returns the serial number of the underlying.