VBA CLng

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

Excel VBA CLng Function

“CLNG” means “Convert to Long” data type because the Integer data type limit is between -32768 to 32767. So, we need to use the LONG data type in VBA for anything excess you want to store to these variables.

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 VBA. This article will show you the Integer data type conversion function, “CLNG.”

Excel VBA CLng Function

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 Mismatch.”

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.

Code:

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.

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 VBA,” 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.

For example, look at the below code.

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,” 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.