Excel VBA CLng Function
“CLNG” means “Convert to Long” data type. Integer data type limit is between -32768 to 32767, so anything excess you want to store to these variables then we need to use LONG data type in VBA.
Variables are essential in any programming language, and VBA is no different either. Without using variables, it is almost an impossible task to write lengthy codes. If variables are essential, then the datatype variable which we assign to these variables is even more critical because the datatype that we give to them is the result that we get.
Often time we may assign the numerical data as “String,” so this will throw an error while doing calculations, so by using convert functions, we can convert the data type that we want. Commonly used converter functions are CLng, CInt, CDbl, CDate, CDec in VBA. In this article, we will show you about the integer data type conversion function “CLNG.”
Below is the syntax of the CLNG function.
- The expression is nothing but the value or the variable that we want to convert to the LONG data type.
Note: The value that we are trying to convert should be a numerical anything other than numerical value will cause a “Run Time Error 13: Type Mismatch.”
Examples to Use the CLng Function in VBA
Now we will see how do we convert the text stored value to the “Long” data type.
For example, look at the below code.
Sub CLNG_Example1() Dim LongNumber As String Dim LongResult As Long LongNumber = "2564589" LongResult = CLng(LongNumber) MsgBox LongResult End Sub
So, now closely examine the code to understand how this works.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
First, we have declared two variables, String and Long, respectively.
Dim LongNumber As String Dim LongResult As Long
For the string variable, we have assigned the value number but in double-quotes, so what this will do is it will treat the number “2564589” as a string, not as a “Long” variable. Now for the other variable, LongResult, we have used Clng(LongNumber) function to convert the line stored number to a LONG variable.
The next message box will show the result as a LONG number converted from string to long data type.
Now take a look at the below code.
Sub CLNG_Example2() Dim LongNumber As String Dim LongResult As Long LongNumber = "Long Number" LongResult = CLng(LongNumber) MsgBox LongResult End Sub
This should give us the result as “Type Mismatch.”
Understanding why the error is occurring is fundamental to fix the error. When you closely examine the variables for the first variable LongNumber, we have assigned the text value as “Long Number,” then we have used the excel VBA CLNG function to convert this to the LONG data type.
The reason why we got this error is that the LONG data type can accept only numerical data type since we have supplied string value to the variable, it cannot convert the string value or text value to a long data type. Hence, it gives the error as “Type Mismatch.”
One more error we get with the LONG data type is “Overflow Error in VBA,” i.e., the Long variable data type can hold values from -2,147,483,648 2,147,483,647. So any amount above this will cause an overflow error.
For example, look at the below code.
Sub CLNG_Example3() Dim LongNumber As String Dim LongResult As Long LongNumber = "25645890003" LongResult = CLng(LongNumber) MsgBox LongResult End Sub
For the variable “LongNumber,” I have assigned the number “25645890003,” which is over the limit of the “Long” data type. When we run the above code, it will encounter an “Overflow” error.
So, when converting any other data type to Long data type, we need to keep in mind all the above things.
The thing to Remember here
- CLNG stands for convert to long.
- This function converts the other data type to a long data type.
- You need to know the limit of the extended data type to prevent overflow error.
This has been a guide to VBA CLng. Here we discussed how to convert variables to long datatype using examples of VBA CLng function and downloadable excel template. You can learn more about VBA from the following articles –