How to Convert String to Double in Excel VBA (5 Methods)

String and Double are two major data types used in VBA. Often you may need to convert the string to double. In this article, I’ll show you 5 methods to convert string to double in Excel VBA with proper explanation.


Download Practice Workbook


What is the CDbl Function?

The VBA CDbl function converts an expression to a double. If you insert the function in the VBA, you’ll see the following scenario.

CDbl function

The syntax of the CDbl function is-

CDbl(Expression)


5 Methods of Converting String to Double in Excel VBA

Let’s be introduced with today’s dataset where the GDP growth of some countries is given in string data type. And we need to convert the string to double.

Dataset


1. Convert String to Double and Display Using the MsgBox


1.1. Convert String to Double Using the CDbl Function

In the beginning method, I’ll see you the application of the VBA CDbl function to convert into double data type from the string data type. Let’s explore the method in a step-by-step approach.

Step 01: Creating a Module

To use VBA, you need to create a module in the following ways.

Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

Step 02: Copying the VBA Code

Now, copy the following code into the newly created module.

Sub StringToDouble()
Dim Str As String
Dim Dbl As Double
Str = "-19.1234567890123456"
Dbl = CDbl(Str)
MsgBox "Converted Value (in Double Data Type) is" & Dbl
End Sub

Convert String to Double Using the CDbl Function

In the above code, I declared Str and Dbl as String and Double data types respectively. Then I assigned the GDP growth of Barbados i.e. -19.1234567890123456 in the place of Str. Later, I used CDbl(Str) for the Dbl value to convert the string into a double data type. Lastly, I utilized the MsgBox to display the output.

Step 03: Running the Code

Finally, run the code (the keyboard shortcut is F5 or Fn + F5) and you’ll get the following output.

Convert String to Double Using the CDbl Function

As you see that there are 13 digits after the decimal point in the case of double data type. But there were 16 digits in this position of the string data type.

Can you guess what is the reason behind this?

Actually, the double data type cannot contain more than 13 digits after the decimal point. So this is a way of verifying whether a string data is converted into double or not.


1.2. Convert String to Integer and String to Double

For understanding the conversion of string to double better, you need to know the following method.

First I have to declare the string as an integer.

For doing this, just copy the code.

Sub StrToInt()
Dim A As Integer
A = "-19.1234567890123456"
MsgBox A
End Sub

Convert String to Integer

Here, I declared A as an Integer data type and assigned a string value in the integer.

If you run the code, you’ll get the output is only -19.

Convert String to Integer

Later, I’ll declare the string as double.

Sub StrToDbl()
Dim A As Double
A = "-19.1234567890123456"
MsgBox A
End Sub

Convert String to Double

After running the code, you’ll get the following output.

Convert String to Double

Read more: How to Convert String to Number in Excel VBA


2. Using the VBA Sum Function

The VBA SUM function aggregates the arguments. Luckily, you can use the function by summing up zero and string.

Before running the code, copy the following code.

Sub StringToDouble()
Dim Str As String
Dim Dbl As Double
Str = "1.9456"
Dbl = WorksheetFunction.Sum(0 & Str)
MsgBox "Converted Double Type Value is " & Dbl
End Sub

Using the VBA Sum Function

Here, I use the WorksheetFunction.Sum (0 & Str) to convert the string into double data types after declaring data types and assigning the value of Str.

Read more: How to Convert String to Long Using VBA in Excel


3. Utilizing the VBA Text Function

Furthermore, the Text function can be used along with the Worksheet.Function object.

Basically, the function convert number to text in a specified format.

To convert the string into a double data type, copy the following code only.

Sub StringToDouble()
Dim Str As String, DoubleValue As Double
  Str = "-19.1234567890123456"
  MsgBox WorksheetFunction.Text(Str, "General")
End Sub

Utilizing the VBA Text Function

There are two arguments in the Text function. Here, I assigned Str as the first argument as I want to convert the string. Then I specified the General format.

If you run the code, you’ll get the following output.


Similar readings


4. Create a Function to Convert String to Double

If you have a larger dataset, you can create a function to convert a string into double quickly. Also, the function will be useful for the repetitive task.

Right away, copy the following code for creating the function.

Function StrToDbl(GDP_Growth As Variant)
'Converting String to Double Data Type
'Creating StrToDbl Function Through Using the VBA CDbl Function
StrToDbl = CDbl(GDP_Growth)
End Function

Create a Function to Convert String to Double

Now, the function i.e. StrToDbl is automatically created. Instead of running the code, go to the main working sheet. Then type StrToDbl in the D5 cell. And you’ll see the function as shown in the following screenshot and click on the function.

excel vba convert string to double Creating a Function

Now, press Enter and use the Fill Handle Tool to copy the formula for the below cells.

Finally, you’ll get the following output.

Create a Function to Convert String to Double


5. Convert a Range of Strings to Double

Lastly, this method will be highly effective if you want to convert a range of strings into double.

Copy the following code into the code module.

Sub Convert_Range_StrToDbl()
Dim k As Integer

Dim Dbl As Double

For k = 5 To 10
Dbl = Cells(k, 3).Value
Cells(k, 4).Value = Dbl

Next k
End Sub

Convert a Range of Strings to Double

In the above code, I used For Loop to get the output in the D5:D10 cell range. For doing this, I declared k as an Integer and assigned the value of k as 5 to 10. And I also specified the input and output cell range using the Cells(row, column).Value function.

Next, run the code and the output will look as follows.

Convert a Range of Strings to Double

Here, I stored the cell’s value into a double type variable Dbl, that’s why each of the values has been converted into double.   


Common Errors

  • Be careful about the Run-time error ‘13’: Type Mismatch. You’ll get such type of error if you input any values except numerical values.

Run-time error '13': Type Mismatch

  • Again you may get #VALUE! error if the Double data type exceeds the digit limits.

Conclusion

This is how you can use Excel VBA to convert the string to double quickly. No, choose your methods based on your requirements. Anyway, if you have any queries or suggestions, please share them in the comments section.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo