The Rules for Working with Excel

□ Pass values to Excel in their natural format if possible (don't convert dates, numbers, or Booleans to strings if you don't have to). If you have strings, convert them yourself before passing them to Excel.

□ When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or Formula strings), always explicitly convert the data to a U.S.-formatted string, using Trim(Str(MyNumber)), or the sNumToUS function shown previously, for all number and date types. Excel will then use it correctly and convert it to the local number and date formats.

□ Avoid using Date literals (such as #1/3/2007#) in your code. It is better to use the VBA DateSerial or Excel DATE functions, which are not ambiguous.

□ If possible, use the date number instead of a string representation of a date. Numbers are much less prone to ambiguity (though not immune).

□ When writing formulas in code to be put into a cell (using the .Formula property), create the string using English functions. Excel will translate them to the local Office language for you.

□ When setting number formats or using the Format function, use U.S. formatting characters — for example, ActiveCell.NumberFormat = "dd mmmyyyy". Excel will translate these to the local number format for you.

□ When reading information from a worksheet, using .Formula, .NumberFormat, and so forth, Excel will supply it using English formulas and U.S. format codes, regardless of the local Excel language.

0 0

Post a comment