Cell Values And Displayed Text
| When you are working in Excel, either with formulas or with Visual Basic For Applications (VBA) code, it is important to understand the difference between the actual value of a cell and what is displayed on the screen. The actual value of the cell is what Excel stores internally and what it uses in formulas and calculations. This is not necessarily the same as what you see displayed on the screen or printed on your reports. It is important that you understand the difference between the two -- otherwise, your formulas may not work as expected. For example, as you know, dates are stored as the number of days since 0-Jan-1900. (Click here for more information about working with dates and times.) Excel will store the date as a simple number. The date 7-April-2001 is stored internally by Excel as the number 36,988. However, it is quite unlikely that you will display a date in this "serial" format. More likely, you'll display it as 4/7/2001 or perhaps a day name (e.g., "Sat"). On this page, we'll refer to actual value of the cell as "value" and the displayed text as "text". Cell Values In Formulas Let's look at this example further. In cell
A1, enter 7-April-2001. This should display in the default format for your
language. Now, in A2, enter the formula =IF(A1="4/7/2001","Yes","No").
This will display No, because the value of A1 is not the
character string "4/7/2001". Now, in A3, enter the
formula It is unlikely that you'll hard code dates like
this. However, you may often format a cell to display just the day
of week. Change the number format of A1 to ddd. This
will display Sat. In cell A4, enter the formula You can use the TEXT
function to format the value of a cell in a formula, and then compare that
to another value. For example, you can use the following
formula to determine if A1 is a Saturday The distinction between value and text
is important not just for dates, but for numbers as well. For
example, suppose B1 contains the number 0.4999999, but is is formatted to
display only one decimal place. In this case, B1 will display
0.5. But the formula =IF(B1>=0.5,"Yes","No")
will return No, because the value of
B1 is still less than 0.5, even though it appears to be equal to
0.5. In a case like this, you may want to use the ROUND
function to round the value properly before the comparison: If you fail to take into account the differences between a cell's actual value and the text that is displayed on the screen, your formulas may not work as expected, and it may appear the Excel isn't working properly when in fact it really is.
Cell Values In VBA When you are writing code in VBA, you can use the Text property of a Range object to get the text which is displayed on the screen. The Value property returns the actual value of the cell. For example, Range("C1").Value = 0.49999999 You'll need to have the Immediate window of the VBA
Editor visible in order to see these results. Press CTRL+G or choose
Immediate Window from the View menu.
|