In working with VBA, I often come across scenarios where I need uniform data types for calculations and data processing. That’s where the CLng function becomes a handy tool. It’s pretty straightforward – I use it to convert various expressions into a long integer data type. The range for this data type, I’ve learned, spans quite broadly from negative two billion and change all the way up to positive two billion, giving me plenty to work with and no decimal points to worry about.
I’ve found this particularly useful when I’m dealing with large numbers that exceed the limits of other integer types in VBA. It allows me to prevent overflow errors and ensures that my macros run smoothly. The fact that I can rely on my variables being within a specific range without any decimal parts simplifies my code and lets me focus on more complex parts of the programming logic.
Syntax
CLng(Expression)
- CLng: VBA function I use to convert different types of expressions to the
Longdata type. - Expression: The piece of code that I want to turn into a
Long. Can be any data type that makes sense to convert. - Returns: The numerical result as a
Long.
Arguments
- Expression: I’m the piece you give me, like a string or numeric value, to switch up to a long integer.
| Function | What it Takes (Argument) | What it Spits Out |
|---|---|---|
CLng() | My input, could be numbers or strings | Long data type value within -2,147,483,648 and 2,147,483,647 |
CInt() | My input, any number | Integer data type, more narrow range than long |
CDbl() | My input, anything with decimals | Double, it’s for the big numbers with lots of decimals |
CStr() | My input, could be literally any value | String variable, turns anything into text |
CBool() | My input, usually some numeric value | Boolean, I get true or false from it |
CByte() | My input, integers from 0 to 255 | Byte, the tiny range for small numbers |
CDec() | My input, large or small numbers | Decimal, for when you need precision |
CSng() | My input, like CDbl but smaller scale | Single, a lightweight alternative to double |
CVar() | My input, anything under the sun | Variant, it’s flexible with what it can become |
- Just so you know, I never touch the decimal separator when changing strings to numbers.
- I keep the conversion accurate, making sure not to exaggerate or misrepresent the values you hand me.
- When it’s all about numbers, I stick to what’s precise and literal, like a good calculator.
Example
In one of my macros, I run a piece of VBA code that involves the CLng function. This little gem converts a value to a long integer. The code looks like this:
Sub example_CLNG()
Range("B1").Value = CLng(Range("A1"))
End Sub
I use a value from cell A1—say, 134.567. After the code runs, cell B1 displays 135, showcasing the conversion result as a long data type. Pretty straightforward, right? Here’s a sneak peek at the code in action:
Notes
When I use the CLng function in VBA:
