Reading Data from Excel

When reading a cell's value, using its Value property, the data type that Excel provides to VBA is determined by a combination of the cell's value and its formatting. For example, the number 3000 could reach VBA as a Double, a Currency, or a Date (March 18, 1908). The only international issue of concern here is if the cell's value is read directly into a string variable — the conversion will then be done implicitly, and you may not get what you expect (particularly if the cell contains a Boolean value).

As is the case when sending data to Excel, the translation between U.S. and local functions and formats occurs when reading data from Excel. This means that a cell's .Formula or .NumberFormat property is given in English, and with U.S. number and date formatting, regardless of the user's choice of language or regional settings.

Although for most applications it is much simpler to read and write using U.S. formulas and formats, you will sometimes need to read exactly what the user is seeing (in their choice of language and regional settings). This is done by using the xxxLocal versions of many properties, which return (and interpret) strings according to the user's settings. They are typically used when displaying a formula or number format on a UserForm, and are discussed in the following section.

0 0

Post a comment