VBA provides some data type conversion functions. These functions can make your VBA code more readable. For instance,
If lLongVariable Then
If CBool(lLongVariable) Then
The second is more readable because the reader can easily see that you are converting a long data type to Boolean.
I’ve heard that explictly converting data types speeds up code execution. I haven’t tested it myself, nor seen the results of any tests, so take it for what it’s worth. Here’s the conversion functions that I commonly use:
CBool – converts to Boolean (True/False). I generally am converting a numeric to a boolean when I use this. In VBA, True is equivalent to -1 and False to 0 – that’s different than Excel where True is 1. In practice, any non-zero numeric will return true when converted with this function.
CLng – converts to Long. When I use this one, I’m almost always coverting a Boolean to a Long. A common situation is passing a Boolean argument to a sub and I use that argument to do one more loop, capture one more character of text, or something like that. It usually takes the form of Abs(CLng(BoolVariable)) to convert the -1 of True to 1.
I rarely, if ever, use CLng to convert a numeric (like Double) to Long. According to help, CLng rounds decimals that are exactly .5 to the nearest even number. There’s probably some lucid explanation for that, but it just seems crazy to me.
CStr – coverts to String. I definitely use this when filling collections, because the key has to be a string. I should use it any time I’m concatenating a numeric into a string, but alas I’m too lazy. Mayble I’ll make that my new year’s resolution next year.
I don’t think I’ve ever used any of the other functions, but my memory isn’t what it used to be. Here’s some that I never use. The others I just haven’t had occasion to use.
CInt – converts to Integer. Someone once told me that VBA doesn’t use the Integer data type internally. If you Dim a variable as Integer, VBA converts it to a Long for its own use. I’ve just assumed that’s true, and thus I never use the Integer data type nor the CInt function. CInt has the same rounding wierdness as CLng.
CVar – converts to Variant. I avoid Variant data types whenever possible. Sometimes it’s not possible, but what’s a guy to do. If I have to use a Variant, I generally know that up front and I can’t think of a situation where I would have to convert a variable to Variant.
CDate – converts to Date. I’ll be some people use this, it’s probably a valuable function. If I have a String, I use the DateValue function and if I have a numeric, I use DateSerial. Both return a Variant data type with a Date sub type. Maybe I should start using CDate in combination with those functions.
You Write:
” According to help, CLng rounds decimals that are exactly .5 to the nearest even number. There’s probably some lucid explanation for that, but it just seems crazy to me.”
Its not crazy. Consider that values ending in .5 are exactly between the nearest integers. The convention of always rounding them up is arbitrary, and can infact introduce a positive skew into a very (very) large data set. In a really big data set, rounding x.5 to the nearest even will round down as often as it rounds up, and thus eliminate the potential skew.
vkd: That makes sense. My problem is that we have a convention and this doesn’t follow it. Even if it’s arbitrary, it’s what I would expect (which I guess is why it’s a convention – to meet expectations.) If CLng and CInt break convention, then why doesn’t Round?
One good thing about this explicit conversions (altough I never use them either ;) ), is that it makes the transition to .Net easier…
For example, this
Dim i As Integer
i = 1
If i Then
‘Something
End If
would work in VBA, but would cause a compile error in .Net.
How about CDec when filling a variant array with mixed data vectors? Keeps XL VBA from auto-assigning as literal datetime when reading first cell(datetime) into a vector and preserves for calculations in VBA.
Good point, Juan. Harald convinced me that I should learn .net, now I just have to do it.
jwolf: I’ve read your comment three times and it’s still over my head. Can you give me a short example of where this fails? I’d like to learn what you’re talking about.
Just to add to the 1st 2 comments up here – the rounding “convention” of rounding up at 0.5 is used by the majority of people but when you work in certain industries this is not accurate enough and therefore rounding to the nearest even number is much better – this includes banking (I think this method of rounding has a name something to do with accounting) and forecasting in virtually any industry. Rounding to the nearest even number is actually a “convention” it’s just that it’s only one that’s used where huge amounts of data are found.
The reason that some of the conversions use one convention and some another is so that you can use excel to do any of them.