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 key in any programming language and VBA is no different either. Without using variables it is almost an impossible task to write long codes. If variables are important then the datatype variable which we assign to these variables is even more important because datatype that we assign to them is the end 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.
- Expression is nothing but the value or the variable that we want to convert to the LONG data type.
Note: 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 an 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 “Long” variable. Now for the other variable LongResult, we have used Clng(LongNumber) function to convert the string stored number to a LONG variable.
The next message box will show the result as a LONG number which is 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 very important to fix the error. When you closely look at 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, so it gives the error as “Type Mismatch”.
One more error we get with LONG data type is “Overflow Error in vba” i.e. Long variable data type can hold values from -2,147,483,648 to 2,147,483,647. So any value above this will cause an overflow error.
For an 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 you are converting any other data type to Long data type we need to keep in mind all the above things in mind.
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 long 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 –