Sending Data to Excel

By far the best way to get numbers, dates, Booleans, and strings into Excel cells is to do so in their native format. Hence, the following code works perfectly, regardless of locale:

Sub SendToExcel()

Dim dtDate

As Date, dNumber As Double, bBool As Boolean, _

stString As String

dtDate = DateSerial(2007, 2, 13)

dNumber =


bBool = True

stString =

"Hello World"


).Value = dtDate


).Value = dNumber


).Value = bBool


).Value = stString

End Sub

There is a boundary layer between VBA and Excel. When VBA passes a variable through the boundary, Excel does its best to interpret it according to its own rules. If the VBA and Excel data types are mutually compatible, the variable passes straight through unhindered.

The problems start when Excel forces you to pass it numbers, dates, or Booleans within strings, or when you choose to do so yourself. The answer to the latter situation is easy—don't do it. Whenever you have a string representation of some other data type, if it is possible, always explicitly convert it to the data type you want Excel to store before passing it to Excel.

Excel requires string input in the following circumstances:

□ Setting the formula for a cell, chart series, conditional format, data validation rule, or pivot table calculated field

□ Specifying the RefersTo formula for a defined name

□ Specifying AutoFilter criteria

□ Passing a formula to ExecuteExcel4Macro

□ Setting the number format of a cell, style, chart axis, or pivot table field

□ Setting the number format in the VBA Format function

In these cases, you have to ensure that the string that VBA sends to Excel is in U.S.-formatted text—you must use English language formulas and U.S. regional settings. If the string is built within the code, you must be very careful to explicitly convert all your variables to U.S.-formatted strings.

Take this simple example:

Sub SetLimit(dLimit As Double)

ActiveCell.Formula = "=IF(A1<" & dLimit & ",1,0)" End Sub

You are setting a cell's formula based on a parameter supplied by another routine. Note that the formula is being constructed in the code and you are using U.S. language and regional settings (that is, using the English IF and a comma for the list separator). When used with different values for dLimit in different locales, you get the results shown in the following table.






Works fine

Works fine

Works fine


Works fine

Works fine

Run-time error 1004

It fails when run in Norway with any non-integer value for dLimit. This is because you are implicitly converting the variable to a string, which you'll recall uses the Windows Regional Settings number formats. The resulting string that you're passing to Excel is:

This fails because the IF function does not have four parameters. If you change the function to read:

Sub SetLimit(dLimit As Double)

ActiveCell.Formula = "=IF(A1<" & Str(dLimit) & ",1,0)" End Sub

The function will work correctly, because Str forces a conversion to a U.S.-formatted string.

If you try the same routine with a Date instead of a Double, you come across another problem. The text that is passed to Excel (for example, for February 13, 2007) is:

While this is a valid formula, Excel interprets the date as a set of divisions, so the formula is equivalent to:

This is unlikely to ever be true. To avoid this, you have to convert the Date data type to a Double, and from that to a string:

Sub SetDateLimit(dtLimit As Date)

ActiveCell.Formula = "=IF(A1<" & Str(CDbl(dtLimit)) & ",1,0)" End Sub

The function is then the correct (but less readable): =IF(A1<36935,1,0)

To maintain readability, you should convert dates to Excel's DATE function, to give:

This is also achieved by the sNumToUS function presented earlier in this chapter, when the bUseDateFunction parameter is set to True:

Sub SetDateLimit(dLimit As Date)

ActiveCell.Formula = "=IF(A1<" & sNumToUS(dLimit, True) & ",1,0)" End Sub

If you call the revised SetLimit procedure with a value of 100.23 and look at the cell that the formula was put into, you'll see that Excel has converted the U.S. string into the local language and regional settings. In Norway, for example, the cell actually shows:

This translation also applies to number formats. Whenever you set a number format within VBA, you can give Excel a format string that uses U.S. characters (d for day, m for month, and y for year). When applied to the cell (or style or chart axis), or used in the Format function, Excel translates these characters to the local versions. For example, the following code results in a number format of dd/mm/aaaa when you check it in the Number Format dialog in Norwegian Windows:

ActiveCell.NumberFormat = "dd/mm/yyyy"

This capability of Excel to translate U.S. strings into the local language and formats makes it easy for developers to create locale-independent applications. All you have to do is code in American and ensure that you explicitly convert your variables to U.S.-formatted strings before passing them to Excel.

0 0

Post a comment