The Val Function

This is the most commonly used function to convert from strings to numbers. It actually only converts a U.S.-formatted numerical string to a number. All the other string-to-number conversion functions try to convert the entire string to a number and raise an error if they can't. Val, however, works from left to right until it finds a character that it doesn't recognize as part of a number. Many characters typically found in numbers, such as dollar signs ($) and commas, are enough to stop it from recognizing the number. Val does not recognize U.S.-formatted date strings.

Val also has the dubious distinction of being the only one of VBA's conversion functions to take a specific data type for its input. Whereas all the others use Variants, Val accepts only a string. This means that anything you pass to Val is converted to a string (implicitly, therefore according to the WRS and Windows language) before being evaluated according to U.S. formats.

The use of Val can have unwanted side effects (otherwise known as bugs), which are very difficult to detect in code that is running fine on your own machine, but which would fail on another machine with different WRS.

In the following table, myDate is a Date variable containing February 10, 2007 and myDbl is a Double containing 1.234.

Expression

U.S.

UK

Norway

Val(myDate)

2

10

10.02 (or 10.2)

Val(myDbl)

1.234

1.234

1

Val(True)

0 (=False)

0 (=False)

0 (=False)

Val("SomeText")

0

0

0

Val("6 My St.")

6

6

6

For clarity of comparison, the results are all displayed using U.S./UK number formats, though Val(myDate) would appear as 10,02 with Norwegian settings.

0 0

Post a comment