totn Excel Functions

MS Excel: How to use the CLNG Function (VBA)

This Excel tutorial explains how to use the Excel CLNG function with syntax and examples.

Description

The Microsoft Excel CLNG function converts a value to a long integer.

The CLNG function is a built-in function in Excel that is categorized as a Data Type Conversion Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

Syntax

The syntax for the CLNG function in Microsoft Excel is:

CLng(expression)

Parameters or Arguments

expression
The value that you wish to convert to a long integer.

Returns

The CLNG function returns a long integer value.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • VBA function (VBA)

Example (as VBA Function)

The CLNG function can only be used in VBA code in Microsoft Excel.

Let's look at some Excel CLNG function examples and explore how to use the CLNG function in Excel VBA code:

Dim LValue As Long

LValue = CLng(35150.45)

In this example, the variable called LValue would now contain the value of 35150.

Be careful using CLNG. If you were to use the following code:

Dim LValue As Long

LValue = CLng(35150.5)

The variable LValue would still contain the value of 35150. Until the fraction is greater than .5, the CLNG function will not round the number up.

TIP:

  • If the fraction is less than or equal to .5, the result will round down.
  • If the fraction is greater than .5, the result will round up.

We've found it useful in the past to add 0.0001 to the value before applying the CLNG function to simulate the regular rounding process.

For example, CLng(35150.50001) will result in a value of 35151.

Share on: