JIYIK CN >

This article will discuss how to convert a string to a number in VBA Excel. There is a step-by-step guide and many examples for us to understand.

Convert a string to a number in VBA

In VBA code, numbers saved as text must be converted to real numbers. We have many options for conversion.

We can convert string to number of Byte, Integer, Long, Single, Double, Currency and Decimal data types. We will discuss each conversion in detail with examples.

Converting a string to bytes in VBA

With the help of the following code, we can easily convert a string into multiple bytes data types.

# vba
byte = CByte(str)

We can use CByte()the function to achieve this conversion. The above statement has two items: CByte()and string.

CByte()This function forces a string to a byte type. CByte()It is often used to perform internationalized string to byte data type conversions.

In simple terms, CByte()it is common to distinguish between different decimal/thousand separators and a number of currency options depending on the location of your computer. The range that the byte data type can hold is from 0 to 225. If your string is not within this range, an error will occur.

Here is an example of a macro where we take convertStras an argument to convert a string to bytes.

# vba
Function convertStr(newStr As Variant)

    MsgBox (CByte(newStr))

End Function

Sub newFunc()
convertStr ("12")
End Sub

Output:

Convert a string to an integer in VBA

With the help of the following statement, you can easily convert a string into a number of integer data type. The syntax of the function is as shown below.

# vba
CInt(newStr)

It is recommended to use Clnt()the function to change a string to an integer data type. This function forces a string to be changed to an integer data type.

If the string contains a fraction, this function converts it to an integer. If the fraction is exactly 0.4, this function changes it to the nearest even number.

For example:

# vba
0.4 will become 0.
1.6 and 2.4 both will become 2.
3.7 and 4.3 will become 4

Clnt()Often used to perform internationally accepted conversions of strings to integer data types. In simple terms, Clnt()usually distinguishing between different decimal/thousand separators and many currency options depending on the location of our computer.

The integer data type can hold numbers in the range -32,768 to 32,767. If your string is not in this range, an error will occur.

Let us go through an example and use CInt()the function as shown below. This example newStrsets the as a parameter to change the string into an integer as shown below.

# vba
Function convertToInteger(newStr As Variant)

    MsgBox (CInt(newStr))

End Function

Sub newFunc()
convertToInteger ("12.5")
End Sub

Output:

Convert a string to a long integer in VBA

We can use CLng()the function to achieve this conversion. The syntax of the function is as follows.

# vba
CLng(Str)

When our goal is to change a string into several long data types, we can use CLng()the function. The items in this statement are CLng()and the string.

This function forces a string to be changed into a long data type.

If a string contains a fraction, this function will round it up. If the fraction is exactly 0.4, this function will change it to the nearest even number as shown below.

# vba
0.4 will become 0.
1.6 and 2.4 both will become 2.
3.7 and 4.3 will become 4

CLng()Often used to perform internationalized string to long data type conversions. In simple terms, CLng()often distinguishing between different decimal/thousand separators and a number of currency options depending on the location of your computer.

The integer data type can hold numbers in the range -2,147,483,648 to 2,147,483,647. If your string is not in this range, an error will occur.

Let us see an example and use CLng()the function. In this example, we newStrset as a parameter to change a string into a long string as shown below.

# vba
Function convertToLong(newStr As Variant)

    MsgBox (CLng(newStr))

End Function

Sub newFunc()
convertToLong ("15.7")
End Sub

Output:

The long data type is different from the integer data type in only one thing. Larger numbers are acceptable in the long data type.

But in the past, there was not enough memory and it was not recommended to use the long data type.

In modern times, memory is no longer an issue. We can use long data types instead of integers.

Convert a string to a single string in VBA

With the help of the following syntax, we can easily convert string to multiple single data types as shown below.

# vba
CSng(str)

We can use CSng()the function to achieve this conversion. There are two items in the above statement, CSng()and string.

This function coerces a string to a single data type.

CSng()Often used to perform conversions of internationalized strings to a single data type. CSng()Often distinguishes between different decimal/thousand separators and currency options depending on the computer's locale.

The range of floating point numbers that a single data type can hold is

• When the value is negative, 3.402823E38 to -1.401298E-45
• Positive values ​​are 1.401298E-45 to 3.402823E38

If your string is not within this range, you will get an error. As shown below, let's take a look at an example where we newStrset as a parameter to change the string into a single string.

# vba
Function convertToSingle(newStr As Variant)

    MsgBox (CSng(newStr))

End Function

Sub newFunc()
convertToSingle ("1.3")
End Sub

Output:

Convert a string to a double in VBA

With the help of the following statement, we can easily convert a string into multiple double data types. The syntax of the function is as shown below.

# vba
CDbl(str)

It is recommended to use CDbl()the function to change a string to a double data type. This function forces a string to be changed to a double data type.

CDbl()Typically used to perform internationally-aware conversions of strings to double data types.

In short, CDbl()it is common to distinguish unique decimal/thousands separators and many currency options depending on the location of your computer.

The double data type can hold floating point numbers in the following ranges.

• -1.79769313486231E308 to -4.94065645841247E-324 when the value is negative.
• When the value is positive, 4.94065645841247E-324 to 1.79769313486232E308.

If our string is not within this range, we will get an error. As shown below, let's go through an example in which we newStrset as a parameter to change a string into a double.

# vba
Function convertToDouble(newStr As Variant)

    MsgBox (CDbl(newStr))

End Function

Sub newFunc()
convertToDouble ("1.345")
End Sub

Output:

Convert a string to currency in VBA

With the help of the following statement, you can easily convert a string into multiple currency data types. The syntax of the function is as shown below.

# vba
CCur(newStr)

It is recommended to use CCur()the function to change a string to the currency data type. This function forces a string to be changed to the currency data type.

CCur()Typically used to perform international conversions of strings to currency data types.

This means CCur()typically unique decimal/thousand separators and a number of currency options depending on the location of your computer. The Currency data type can hold integers up to 10,000.

Therefore, a currency can hold 15 digits to the left of the decimal point and 4 digits to the right of the decimal point. Therefore, the range of numbers that a currency can hold is:
-922,337,203,685,477.5808 to 922,337,203,685,477.5807.

If your string is not in this range, an error will occur.

As shown below, let's look at an example where we newStrset as a parameter to change a string into a currency.

# vba
Function convertToCurrency(newStr As Variant)

    msgBox(CCur(newStr))

End Function

Convert a string to decimal in VBA

We can use the following statement to achieve this conversion.

# vba
CDec(newStr)

When our goal is to change a string to a Variant decimal data type, we can use CDec()the function. This function forces the string to be changed to a decimal data subtype of the Variant data type.

In simple terms, CDec()a variant changed to a subtype of Decimal is returned.

CDec()Typically used to convert a string to a decimal data type. This means that CDec()usually different decimal/thousand separators are distinguished and there are many currency options depending on the location of your computer.

The decimal data type can carry integers with variable powers of 10. The power indicates the number of digits that can appear to the right of the decimal point.

The range of values ​​that a decimal can hold is as follows.

• When the scale is 0, that is, there are no decimals, the range is from 79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335.
• With 28 decimal places, the maximum and minimum values ​​are +7.9228162514264337593543950335 and -7.9228162514264337593543950335 respectively.
• The smallest values ​​that do not include zero are -0.0000000000000000000000000000001 and 0.00000000000000000000000000000001.

As shown below, let us go through an example where we newStrset as a parameter to change a string to a decimal.

# vba
Function convertToDecimal(newStr As Variant)

    msgBox(CDec(newStr))

End Function