Implicit Conversion

This is the most common form of type conversion used in VBA code and forces the VBA interpreter to convert the data using whichever format it thinks is most appropriate. A typical example of this code is:

Dim dtMyDate As Date dtMyDate = DateValue("Jan 1, 2007")

MsgBox "This first day of this year is " & dtMyDate

When converting a number to a string in Office 2007, VBA uses the WRS to supply either a date string in the user's ShortDate format, the number formatted according to the WRS, or the text for True or False in the WRS language. This is fine, if you want the output as a locally formatted string. If, however, your code assumes you've got a U.S.-formatted string, it will fail. Of course, if you develop using U.S. formats, you won't notice the difference (though your client will).

There is a much bigger problem with using implicit conversion if you are writing code for multiple versions of Excel. In previous versions, the number formats used in the conversion were those appropriate for the Excel language being used at run time (buried within the Excel object library), which might be different from both U.S. and local formats, and were not affected by changing the WRS.

Be very careful with the data types returned from, and used by, Excel and VBA functions. For example, Application.GetOpenFilename returns a Variant containing the Boolean value False if the user cancels, or a string containing the text of the selected file. If you store this result in a String variable, the Boolean False will be converted to a string in the user's WRS language, and it may not equal the string "False" that you may be comparing it to.

To avoid these problems, use the Object Browser to check the function's return type and parameter types, and then make sure to match them, or explicitly convert them to your variable's data type. Applying this recommendation gives you (at least) three solutions to using Application.GetOpenFilename.

Typical code running in Norway:

Dim stFile As String stFile = Application.GetOpenFilename() If stFile = "False" Then

If the user cancels, GetOpenFilename returns a variable containing the Boolean value False. Excel converts it to a string to put in your variable, using the Windows language. In Norway, the string will contain "Usann". If this is compared to the string "False", it doesn't match, so the program thinks it is a valid filename and subsequently crashes.

Solution 1:

Dim vaFile As Variant vaFile = Application.GetOpenFileName()

If vaFile = False Then 'Compare using the same data types

Solution 2:

Dim vaFile As Variant vaFile = Application.GetOpenFileName()

If CStr(vaFile) = "False" Then 'Explicit conversion with CStr() always

'gives a US Boolean string

Solution 3:

Dim vaFile As Variant vaFile = Application.GetOpenFileName()

If TypeName(vaFile) = "Boolean" Then 'Got a Boolean, so must have

'cancelled

Note that in all three cases, the key point is that you are matching the data type returned by GetOpenFilename (a Variant) with your variable. If you use the MultiSelect:=True parameter within the GetOpenFileName function, the last of the preceding solutions should be used. This is because the vaFile variable will contain an array of filenames, or the Boolean False. Attempting to compare an array with False, or trying to convert it to a string, will result in a run-time error.

Was this article helpful?

0 0

Post a comment