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.