Excel VBA CLng Function

Variables are essential in any programming language, and VBA is no different. Without using variables, it is almost an impossible task to write lengthy codes. Moreover, if variables are essential, then the data type variable we assign to them is even more critical because the data type we give them is the result we get.

Often we may assign the numerical data as “String,” so this will throw an error while doing calculations. We can convert the data type we want by using convert functions. Commonly used converter functions are CLng, CInt, CDbl, CDate, and CDec in VBACDec In VBACDEC is an inbuilt data type conversion function available as a VBA function. While dealing with multiple values of other data types, the VBA CDEC function converts those data types to a decimal data type. It stands for “Convert to Decimal”.read more. This article will show you the Integer data type conversion function, “CLNG.”

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA CLng (wallstreetmojo.com)

Syntax

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 we are trying to convert should be a numerical anything other than a numerical value that will cause a “Run Time Error 13: Type MismatchRun Time Error 13: Type MismatchWhen we assign a value to a variable that is not of its data type, we get Type mismatch Error or Error code 13. For example, if we assign a decimal or long value to an integer data type variable, we will get this error (Error Code 13) when we run the code.read more.”

Examples to Use the CLng Function in VBA

Example #1

Now, we will see how we convert the text stored value to the Long data type.

For example, look at the below code.

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.

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. For the other variable, LongResult, we have used the 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.

Output:

Example #2

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

It should give us the result of “Type Mismatch.”

Understanding why the error is occurring is fundamental to fixing the error. For example, when you closely examine the variables for the first variable, LongNumber, we have assigned the text value as “Long Number.” We have used the Excel VBA CLNG function to convert this to the LONG data type.

We got this error because the LONG data type can accept only numerical data types since we have supplied string value to the variable. Therefore, it cannot convert the string or text values to a Long data type. Hence, it gives the error as “Type Mismatch.””

Example #3

One more error we get with the LONG data type is “Overflow Error in VBAOverflow Error In VBAVBA Overflow Error or “Run Time Error 6: Overflow” occurs when the user inserts a value that exceeds the capacity of a specific variable’s data type. Thus, it is an error that results from the overloading of data beyond the desired data type limit of the variable.read more,” i.e., the Long variable data type can hold values from -2,147,483,648 to 2,147,483,647. So any amount above this will cause an overflow error.

Sub CLNG_Example3()

Dim LongNumber As String

Dim LongResult As Long

LongNumber = “25645890003”

LongResult = CLng(LongNumber)

MsgBox LongResult

End Sub

For the variable “LongNumber,” we have assigned the number “25645890003,” which is over the limit of the Long data type. Therefore, when we run the above code, it will encounter an “Overflow” error.

So, when converting any other data type to a long one, we must remember 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.We must know the limit of the extended data type to prevent overflow errors.

This article has been a guide to VBA CLng. Here, we discussed converting variables to long data type using examples of the VBA CLng function and downloadable Excel template. You can learn more about VBA from the following articles: –

  • VBA Application.MatchReplace String in VBAVBA VARTYPE FunctionVBA Double