What does Val return?

[Gordon Bentley-Mix]

The VBA help says the Val function "[r]eturns the numbers contained in a
string as a numeric value of appropriate type." Does this mean that the data
type of the returned value will automatically converted to the data type of
the variable that's calling the function?

For example, in the following contruction

Dim myValue as Long
Dim myString as String
myString = "123 Main Street"
myValue = Val(myString)

will Val return "123" but will it be returned as a Long or do I need to use
CLng to convert it? I know I can just be sloppy and let VBA force the
conversion, but I would prefer to be precise.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Jay Freedman]

The VBA help says the Val function "[r]eturns the numbers contained
in a string as a numeric value of appropriate type." Does this mean
that the data type of the returned value will automatically converted
to the data type of the variable that's calling the function?

For example, in the following contruction

Dim myValue as Long
Dim myString as String
myString = "123 Main Street"
myValue = Val(myString)

will Val return "123" but will it be returned as a Long or do I need
to use CLng to convert it? I know I can just be sloppy and let VBA
force the conversion, but I would prefer to be precise.

It's hard to tell because VBA does do implicit conversions during
assignments. The closest I can come to an answer is by changing the
declaration of myValue to a Variant and watching the Locals window while
single-stepping through the macro. It shows the data type to be
"Variant/Double", so I suspect that the initial return of the Val function
is a Double.

However, I'm not sure it's worth writing an explicit CLng conversion. The
interpreter might be smart enough to look at the variable being used on the
left of the equal sign, and call an override of the Val function that
already returns a Long. You'd have to peek at the proprietary interpreter
code to find out.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.