Return to VBA Code Examples

VBA CLng Function – Convert Expression to Long

This tutorial will demonstrate how to use the CLng VBA function to convert an expression to the long integer data type.

CLng Function

VBA CLng Convert Expression to Long

The VBA CLng function can be used to convert expressions to long data type inside the VBA code.

VBA CLng Rounding

The VBA CLng function will round the decimal part of a number type or a number like expression. However, it does not round correctly in all cases. When the decimal part is 0.5 then VBA CLng function returns the closest even integer.

We can add a decimal number relatively small to our expected decimal value to change the behavior of VBA CLng function to the expected.

VBA CLng Converting Strings to Longs

The VBA CLng function can be used to convert strings to longs if the characters in the string have a meaning as numbers.

VBA CLng Run-Time Error 13 Type Mismatch

Using VBA CLng function with strings that contain non-numerical characters or characters that don’t have meaning in numerical context will result in a Run-Time error ’13’: Type mismatch.

VBA CLng Run-Time Error 6 Overflow

Using VBA CLng function with strings that result in a value smaller or bigger than the expected integer will result in a Run-Time error ’6’: Overflow. Integer data type in excel has an expected value of -2,147,483,648 to 2,147,483,647.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

VBA CLng Regional Settings

VBA CLng function has different behavior converting strings with comma or dot.  It uses the Regional Settings of the operating system for decimal separator and digit separator.

VBA CLng Converting Booleans to Longs

VBA CLng function can convert boolean variables to longs. If the evaluated expression is true the resulting long is -1 and if the evaluated expression is false the resulting long is 0.

VBA CLng Converting Dates to Longs

VBA CLng function can convert a date variable to a long. The returned value is the internal number used by excel for date storage rounded. If that number is outside of the expected long limits for VBA then we get a Run-Time error ’6’: Overflow.