Wednesday, November 26, 2008

Value2 in Excel VBA

I did a lots of VB programs long time ago. Actually, I started to develop Windows applications by using VB3.0 to VB6.0. Then since the .Net, I switched to .Net C# and VB.Net. However, VB6.0 still has its market. During this year my contract job, I have done a lots VBA applications for Excel, Word and some legacy applications which provides VB6.0 code scripts.

Anyway, I encountered one interesting issue today. I tried to use Value2 in Excel VBA codes to compare two date date types. I know that I put the same date from one worksheet to another worksheet. However, when I tried to search for the same date in another worksheet, the cells with dates are not matched:

v1 = sheet1.Cells(row, col).Value2
v2 = sheet2.Cells(col, row).Value2
If v1 = v2 Then
End If

When I changed Value2 to Value, these cells with the same dates are found.

I googled the Value2 and found out that Value2 does not use Currency and Date data types (ref MSDN Office developer center). These values are converted to double type. This conversion caused a very insignificant difference 0.###E-11. This difference makes them different!

By the way, I know that when float values saved in variables, the actual value is very close the "float value" such as 1.2 as 1.199999999, and the value might be dependent on CPUs or OS. That means a float values in one machine may not the exactly same as the value in another machine. For safe gard, when I compare two floating values, I always use absolute the difference to epsilon, same if less then epsilon, not same others.

However, when I looked at C#'s floating data types, I see Equal() operator or == for these data types. Not sure why they are there.