The Visual Basic for Applications (VBA) CLng function is used to convert a given value to a ‘Long’ data type. This conversion ensures that the value falls within the range of a 32-bit signed integer (-2,147,483,648 to 2,147,483,647). This function can be useful when working with larger numbers, as using the ‘Integer’ data type in VBA can result in an overflow error if the number exceeds its maximum limit.
VBA CLng Function – Purpose, Syntax and Arguments
Syntax:
CLng(expression)
Arguments:
‘Long’ data type.
expression: Required. The value or expression to be converted to a
Example:
‘Long’ data type to be used in our VBA code. The following code demonstrates how we can use the CLng function to convert the values in column A into ‘Long’ data type and print them in the immediate window.
For example, let’s say we have a list of numbers stored in an Excel sheet and we want to convert them into the
Sub ConvertToInt()
Dim i As Integer
Dim LastRow As Long
LastRow = Range("A1").End(xlDown).Row
For i = 1 To LastRow
Debug.Print "The value of cell A" & i & " is: " & CLng(Range("A" & i))
Next i
End Sub
Remarks:
- The CLng function can also be used in combination with other functions and mathematical operations.
- It is important to note that the CLng function will round decimal values to the nearest whole number before converting them to ‘Long’ data type.
- If the expression provided to the CLng function cannot be converted to a ‘Long’ data type, it will result in a type mismatch error.
- Similarly, if the expression provided is NULL or empty, it will result in a null value being returned.
Important Notes:
- The CLng function is not limited to working only with numerical values. It can also be used with strings, dates, and other data types, and will attempt to convert them to a ‘Long’ data type if possible.
- If you are unsure about the data type of a variable or value, it is always a good practice to explicitly specify the data type using functions like CLng to avoid any unexpected errors.
In conclusion, the CLng function in VBA can be a useful tool to ensure that our data falls within the appropriate data type range. By converting our values to ‘Long’ data type, we can avoid errors and also perform various mathematical and logical operations on large numbers without any issues. It is always advisable to use this function whenever working with larger numbers in VBA to ensure smooth execution of our code.
Understanding VBA CLng Function with Examples
Basic Use of CLng Function
Description: The CLng function is used to convert a numerical value, either an integer or a decimal, into a 32-bit signed integer in VBA. This function can be helpful when working with large numbers that may exceed the range of a standard integer variable.
Dim lngNum As Long Dim dblNum As Double Dim strNum As String lngNum = 1857 dblNum = 1234.56 strNum = "789" 'output: 1857 Debug.Print CLng(lngNum) 'output: 1235 Debug.Print CLng(dblNum) 'output: 789 Debug.Print CLng(strNum)
Explanation: In this example, we have declared three variables – lngNum as a Long data type, dblNum as a Double data type, and strNum as a String data type. The CLng function is applied to each variable and the result is printed using the Debug.Print statement. In the first case, the CLng function is applied to the lngNum variable which is already of type Long, so the output remains the same. However, in the second case, the CLng function rounds off the decimal value of dblNum to the nearest whole number and returns 1235 instead of 1234.56. In the third case, the CLng function converts the string “789” into a numeric value and returns the result as 789.
Error Handling in CLng Function
Description: When the CLng function is applied to a value that cannot be converted to a Long data type, an error occurs. In order to handle this error, we can use the On Error statement to trap the error and display a custom message.
Dim strNum As String strNum = "ABC" On Error GoTo ErrorHandler 'output: "Type mismatch" Debug.Print CLng(strNum) Exit Sub ErrorHandler: MsgBox "Invalid data type, please enter a numerical value."
Explanation: In this example, we have declared a string variable strNum and assigned a non-numeric value “ABC” to it. When the CLng function is applied to this variable, a type mismatch error occurs as the value cannot be converted to a numerical data type. To handle this error, we have used the On Error statement with the GoTo ErrorHandler command. This redirects the code to the ErrorHandler section where we have displayed a custom error message using the MsgBox function.
Working with Negative Numbers
Description: The CLng function can also be used to convert negative numbers into their respective 32-bit signed integer values.
Dim strNum As String strNum = "-123" 'output: -123 Debug.Print CLng(strNum)
Explanation: In this example, we have assigned the string value “-123” to the strNum variable. When the CLng function is applied to this variable, it recognizes the negative sign and returns the corresponding negative value as a 32-bit signed integer.
Using CLng Function with InputBox
Description: The InputBox function allows the user to input values at run-time. The CLng function can be used with the InputBox function to convert the input into a 32-bit signed integer.
Dim strNum As String
strNum = InputBox("Enter a numerical value.")
'output: user's input
Debug.Print CLng(strNum)
Explanation: In this example, we have used the InputBox function to prompt the user to enter a numerical value. The input is then stored in the string variable strNum. The CLng function is applied to this variable to convert the input into a 32-bit signed integer. This converted value is then printed using the Debug.Print statement.
Using CLng Function with Arrays
Description: An array is a collection of values stored in a single variable. The CLng function can be used to convert all elements of an array into 32-bit signed integers.
Dim arrNum(1 To 3) As Variant arrNum(1) = 1234 arrNum(2) = 5678 arrNum(3) = 9101 'output: 5678 Debug.Print CLng(arrNum(2))
Explanation: In this example, we have declared an array arrNum with three elements and assigned integer values to each element. The CLng function is applied to the second element of the array, arrNum(2), which returns the corresponding 32-bit signed integer value, 5678. This can be useful when working with arrays that contain large numbers and need to be converted to a specific data type.
Using CLng Function with Date Values
Description: Date values in VBA are stored as a numerical value representing the number of days since January 1, 1900. The CLng function can be used to convert the date value into a 32-bit signed integer.
Dim dtmDate As Date dtmDate = #12/31/2021# 'output: 44416 Debug.Print CLng(dtmDate)
Explanation: In this example, we have declared a Date variable dtmDate and assigned a date value of December 31, 2021 to it. When the CLng function is applied to this variable, it returns the corresponding 32-bit signed integer value, 44416, which represents the number of days between January 1, 1900 and December 31, 2021.
Using CLng Function in Mathematical Operations
Description: The CLng function can be used in mathematical operations to convert the result into a 32-bit signed integer. This can be helpful when working with large numbers that may exceed the range of standard integer operations.
Dim lngResult As Long Dim lngNum1 As Long Dim lngNum2 As Long lngNum1 = 32767 lngNum2 = 32768 lngResult = lngNum1 * lngNum2 'output: overflow error Debug.Print CLng(lngResult) 'output: -2147418112 Debug.Print lngResult
Explanation: In this example, we have declared three variables – lngResult as a Long data type and lngNum1 and lngNum2 as Long data types. The product of lngNum1 and lngNum2 exceeds the range of a standard integer, resulting in an overflow error when the CLng function is applied to the result. However, when the result is printed without using the CLng function, it returns a negative value as VBA uses a 32-bit signed integer to store the result.
Conclusion
The CLng function is a useful tool for converting numerical values into 32-bit signed integers in VBA. It can be used in a variety of scenarios, such as handling user inputs, error trapping, and mathematical operations. Understanding the use of this function can help improve the efficiency and accuracy of VBA code.
