The VBA CLngLng function is used to convert a given expression into a long long integer data type. This function is particularly useful when working with large numbers that may exceed the range of a regular long data type.
VBA CLngLng Function – Purpose, Syntax and Arguments
Syntax:
CLngLng(expression)
Arguments:
expression: This is the value or expression that will be converted into a long long integer data type.
Example:
Dim myNum As Double myNum = 1234567890123456789 'Outputs: 1234567890123456789 Debug.Print CLngLng(myNum)
Remarks:
- The CLngLng function is only available in VBA for 64-bit versions of Microsoft Office. For 32-bit versions, the equivalent function is CLngLong.
- The range of values that can be stored in a long long integer is from -9223372036854775808 to 9223372036854775807.
- If the expression cannot be converted into a long long integer, a run-time error will occur.
Important Notes:
- The CLngLng function may not be supported by other programming languages or applications outside of VBA.
- Converting numbers into a long long integer can result in data loss if the original number is larger than the range of the data type.
The CLngLng function in VBA is a valuable function for working with large numbers. It allows for the conversion of values into a data type with greater range and precision, helping to avoid data loss and errors. In this blog post, we explored the description, syntax, arguments, example, remarks, and important notes of the CLngLng function. With this understanding, we can confidently use this function in our VBA projects when dealing with large numbers.
Understanding VBA CLngLng Function with Examples
CLngLng Function in Basic Syntax
Description: The CLngLng function in VBA is used to convert a given expression into a long data type. This function is useful when the expression contains a value that exceeds the range of the integer data type. The basic syntax for using the CLngLng function is as follows:
Dim result As Long result = CLngLng(expression)
Code Explanation: The above code first declares a variable “result” as a long data type. Then, the value of the expression is converted into a long data type using the CLngLng function and assigned to the result variable.
CLngLng Function with Numeric Values
Description: In this example, we will use the CLngLng function to convert numeric values, both positive and negative, into a long data type.
Dim num1 As Long num1 = CLngLng(123.456) 'displays 123 MsgBox num1 Dim num2 As Long num2 = CLngLng(-789.123) 'displays -789 MsgBox num2
Code Explanation: The code first declares two variables “num1” and “num2” as long data types. Then, the CLngLng function is used to convert the given decimal numbers into long data types. Finally, the converted values are displayed using the MsgBox function.
CLngLng Function with String Values
Description: The CLngLng function also works with string values, as long as the string contains only numeric characters. Non-numeric characters will result in an error.
Dim str As String str = "456" Dim result As Long result = CLngLng(str) 'displays 456 MsgBox result
Code Explanation: In this example, a string “str” is declared and assigned the value “456”. Then, the CLngLng function is used to convert the string into a long data type, and the result is displayed using the MsgBox function.
CLngLng Function with Empty String
Description: If the string passed to the CLngLng function is empty, it will return the value 0.
Dim str As String str = "" Dim result As Long result = CLngLng(str) 'displays 0 MsgBox result
Code Explanation: In this example, the string “str” is empty, and the CLngLng function converts it into a long data type. The result is displayed using the MsgBox function, which displays 0 as the result.
CLngLng Function with Non-Numeric String
Description: If the string passed to the CLngLng function contains non-numeric characters, it will result in an error.
Dim str As String str = "abc123" Dim result As Long 'throws an error result = CLngLng(str)
Code Explanation: In this example, the string “str” contains both alphabets and numbers. When the CLngLng function tries to convert it into a long data type, it throws an error since the string contains non-numeric characters.
CLngLng Function with Integer Overflow
Description: The CLngLng function is useful when dealing with large numbers that exceed the range of integer data type. It can handle values up to 9.2 quintillion (9,223,372,036,854,775,807).
Dim num As Long num = 10000000000 'displays -1 MsgBox num 'displays -1 MsgBox CLngLng(100000000000)
Code Explanation: In this example, the value of the “num” variable exceeds the range of the integer data type, and it displays -1 instead of the actual value. However, when the same value is passed to the CLngLng function, it can convert it into a long data type and display the correct value.
CLngLng Function with Expressions
Description: The CLngLng function can also be used with expressions that evaluate to numeric values.
Dim x As Double x = 10.5 Dim y As Double y = 5.5 Dim result As Long result = CLngLng(x * y) 'displays 57 MsgBox result
Code Explanation: In this example, the variables “x” and “y” are declared as double data types and assigned values. Then, the CLngLng function is used to convert the product of x and y into a long data type, and the result is displayed using the MsgBox function.
Conclusion
The CLngLng function in VBA is used to convert an expression into a long data type. It is helpful in handling large numbers that exceed the range of the integer data type and can also be used with string values that contain only numeric characters. It is an essential function to be familiar with for any VBA programmer as it allows for more flexibility in handling data types.
