VBA CLng

What Is Excel VBA CLng Function?

The “VBA CLng function” converts expressions/values of one data type into a Long integer. The function is in-built into Excel and can also be used as a VBA function. The Long data type can store values ranging from -2,147,483,648 and 2,147,483,647.

To understand the VBA CLng function in Excel, please refer to the below example. First, let us convert a String data type into a Long one. Both the data types support numeric values. Open the VBA Editor and write the below code for converting the String data type to Long using the CLng function in VBA. Run the code, and you get the result as 1050 (the “,” is ignored and converted into a Long data type).

Code:

Sub CLng_Example()
Dim X As String
Dim LongX As Long
X = “1,050”
LongX = CLng(X)
MsgBox LongX
End Sub

What is Excel VBA CLng Function?

Excel VBA CLng function comes under the VBA Functions category. The acronym CLng suggests “Convert to Long” and converts a variable (usually a String) into a Long data type.
The argument for the CLng function is an expression (which is to be converted into a Long data type).
Ensure the string contains a numeric value (e.g., “1004”); if we assign a non-numeric text value to a string, then try to convert it to long, and an error “Type Mismatch” will be displayed.
Similarly, if we assign a value out of the range of the CLng function, the CLng will not be able to convert it to the long data type and will show an “Overflow error.”

VBA CLng Function Syntax

The syntax of the CLng function is as follows:

The ‘Expression’ can be a value or variable to convert any VBA data types into a Long data type.

How To Use The CLng Function In VBA?

To use the VBA CLng function, we will follow these simple steps using an example.

Let us consider a decimal number stored as a string value. The decimal part is rounded off via the VBA CLng function if the value of the string is a decimal, for instance, 0.5. Then the result will show the closest even integer 1. Let us see one such example.

Step 1: Open VBA in Excel and click the “Insert” tab. Choose “Module” to open a new module, as shown in the image below.

Step 2: Write the code starting with the sub-procedure and declare two variables as “String” and “Long.” The string variable will be assigned a numeric value and it must be converted to the long data type.

Step 3: Now, assign a value to the string variable, as shown below.

Step 4: Use the VBA CLng Function to convert the String into Long and “MsgBox” to display the result in the message box.

Code:

Sub CLng_Example()
Dim Num As String
Dim LongNum As Long
Num = “999.99”
LongNum = CLng(Num)
MsgBox LongNum
End Sub

Step 5: Run the above code using F5 or the Run button shown in the VBA Ribbon, and we can see the result in the message box as shown below:

Here, the VBA CLng function has converted the string variable value to a rounded figure Long number.

Examples

As we have now seen the use of the VBA CLng function in Excel, let us understand the functioning of VBA CLng function in Excel using some examples.

Example #1

Let us look at an example where we want two string variables to be converted into Long variables using the VBA CLng function.

Step 1: Start with a sub-procedure and define two variables of type String and two variables of Long datatype.

Step 2: Now assign string values with numbers to X and Y.

Step 3: Now, we will use the CLng function to convert the two String data types (X and Y) to Long data type.

Step 4: Use the MsgBox function to see the result in a message box. Here, to prove that the number has been converted to Long, we are using the Vartype function. It prints an integer indicating the variable’s data type. We should get a 3 indicating it is a Long data type.

Step 5: Run the code and see the result for both the string values in the Message box as shown below:

Example #2

In this example, we will look at the error of the VBA CLng function, “VBA Run-time error-6, Overflow.” This error occurs when we assign a numeric value outside the VBA CLng function’s number range.

Step 1: Start with a sub-procedure and define two variables, String and Long.

Step 2: Assign the numeric value (2,147,483,649) to the Excel VBA CLng string. The value is out of the CLng function range.

Step 3: Now, we will use the CLng function to convert it to Long. However, we should get an error as the value we assigned is out of the range of the VBA CLng function.

Step 4: Assign MsgBox to see the result in the message box.

Vba clng function - Example 2 - Step 4.jpg

Step 5: Run the code and observe the “Run-time error-6, Overflow” in the message box.

Example #3

Let us see another example of one of one error that occurs using CLng. Here we will look at the “Run-time-6 Type Mismatch” error. This error occurs when we assign a non-numeric value to a VBA string data type. Thus, the CLng function cannot convert a string into a Long data type and displays an error in the result.

Step 1: Begin with the sub-procedure and define the variables of data types, String and Long.

Step 2: Now assign the non-numeric value (“Number”) to the string variable.

Step 3: Now we will use the CLng function, which will convert the String to the Long data type

Step 4: Assign MsgBox to see the result in it.

Step 5: Run the code, and we get the “Run-time error-13, Type Mismatch” error in the dialog box because we assigned a text value to the String data type.

Sub CLng_Example()
Dim Num As String
Dim LongNum As Long
Num = “Number”
LongNum = CLng(Num)
MsgBox LongNum
End Sub

Let us see what happens when we assign a special character like “$” in the variable value. See the below code.

Sub CLng_Example()
Dim Num As String
Dim LongNum As Long
Num = “$150”
LongNum = CLng(Num)
MsgBox LongNum
End Sub

Step 6: Run the above code. Surprisingly, we get the result of 150. It clearly shows that CLng has ignored the $ sign and converted the rest of the String to Long.

Note: The currency symbol is ignored only if it is in compliance to the location settings of your system. For instance, using the Yen symbol with US as your locations settings will throw an error.

Important Things to Note

CLng (Convert to Long) function converts a given data type into a Long one.
While using the VBA CLng function, we should stick to the range it can store; if we assign a value out of the 2,147,483,648 and 2,147,483,647 range, we will encounter a VBA CLng “Run-time error,6, Overflow error”.
Similarly, if we assign a non-numeric value to a numeric variable, the CLng cannot convert the VBA variable type into a Long data type and will show a “Run-time error.-13, Type Mismatch.” The CLng function uses the operating system’s regional settings for decimal and digit separators.
VBA CLng date – The CLng function can convert a Date data type to a long one.

For Example:

Code:

Sub CLngEx()
Dim DateEx As Date
DateEx = #3/3/2023#
MsgBox CLng(DateEx)
End Sub

The result is the coded internal number used by Excel for date storage.

Frequently Asked Questions (FAQs)

1. When to use Excel VBA CLng function?

Excel VBA CLng function is used to convert an expression or a variable value into a Long data type (value can be anything between -2,147,483,648 and 2,147,483,647)

2. What is the range of CLng in VBA?

The range of CLng VBA function is between -2,147,483,648 to 2,147,483,647. The values are without a decimal. Any value out of this range will cause an error when we run the code.

3. Why is Excel VBA CLng not working?

Excel VBA CLng function not working means there is some error. These are the two usual errors we encounter while using the CLng Function. 1) VBA CLng Overflow error: When we assign an out-of-range value to the string and try to convert it to long, and, 2) VBA CLng Mismatch error: When we assign a non-numeric value to a string variable for conversion into a Long data type.