WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA CDBL Function

VBA CDBL Function

Excel VBA CDBL Function

VBA CDBL is an inbuilt data type conversion function and the use of this function is that it converts the data type of any given variable’s value into a Double data type, this function only takes a single argument which is the value of the variable itself.

In VBA, “CDBL” stands for “Convert to Double.” This function converts the given number to Double data type. Take a look at the syntax of the CDBL function.

CDBL Snytax

  • The expression is the value that we are trying to convert to Double data type.

Any floating number which is stored as other than double data type can be converted by applying the CDBL function.

Point to Remember Here: Only numerical values can be converted to Double data type. Anything other than numerical value cannot be converted to double type, so end up showing “Type Mismatch Error in VBA” like the below.

Type mismatch error

Have you ever used a double data type in VBA coding?

If not, it is worth having a look at it now. Double is the data type is used to store the decimal position of the number. We can have up to 13 floating decimal numbers.

For example, look at the below VBA code.

Double Example 1

In the above, I have defined the variable (k) type as Integer. Dim k As Integer

Next, I have assigned the value as k = 25.4561248694615

When I run the code, we will get the result as follows.

Double Example 1-1

We got the result as 25 since we have defined the variable as Integer VBA round to the nearest integer value.

To show the result as it is, we need to change the variable type from Integer to Double.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Double Example 1-2

This should give us the exact number as we assigned to the variable.

Double Example 1-3

Ok, keeping this in mind, we can also convert all the fraction numbers, which are stores as a non-double data type.

Examples to Use VBA CDBL Function

You can download this VBA CDBL Function Template here – VBA CDBL Function Template

Example #1

To start the proceedings, let’s look at the below code.

Code:

Sub Double_Example1()

    Dim k As String

    k = 48.14869569

    MsgBox k

End Sub

VBA CBDL Example 1

Now I will run the code and see what happens.

VBA CBDL Example 1-2

Even though the VBA variable type is “String,” it is still showing the decimal values. This is because String can be of any kind of data type, so decimal or floating numbers are showing as it is.

Now I will change the vba data type from string to Integer.

Code:

Sub Double_Example1()

    Dim k As Integer

    k = 48.14869569

    MsgBox k

End Sub

VBA CBDL Example 1-2

Now I will run the code and see what happens.

VBA CBDL Example 1-3

This is where the CDBL function plays a vital role in converting the integer data type to double. So below code is the same for you.

Code:

Sub Double_Example1()

    Dim IntegerNumber As String
    Dim DoubleNumber As Double

    IntegerNumber = 48.14869569

    DoubleNumber = CDbl(IntegerNumber)

    MsgBox DoubleNumber

End Sub

VBA CBDL Example 1-4

This will convert the string data type value to Double.

excel vba cdbl Example 1-5

Example #2

Now let’s convert the number 854.6947, which is stored as Variant to Double data type.

Code:

Sub Double_Example2()

    Dim VaraintNumber
    Dim DoubleNumber As Double

    VaraintNumber = 854.6947

    DoubleNumber = CDbl(VaraintNumber)

    MsgBox DoubleNumber

End Sub

excel vba cdbl Example 2

The first variable I have declared as “Variant.” Dim VaraintNumber

Note: When the variable type is not declared, it becomes a universal data type Variant.

Next, I have declared one more variable, i.e., Dim DoubleNumber As Double.

For the first variable, VaraintNumber, we have assigned the value as 854.6947.

Now by using the second variable, we have applied the CDBL function to convert the Variant value to Double data type.

DoubleNumber = CDbl(VaraintNumber)

The final part is to show the result in the message box. MsgBox DoubleNumber

Now I will run the code to see the result.

excel vba cdbl Example 2-1

Things to Remember

  • A double data type can accept only numerical numbers.
  • If text value is supplied, it will cause an error of Type Mismatch.
  • A double data type can display only 13 digits of floating numbers.

Recommended Articles

This has been a guide to VBA CDBL. Here we learn how to use VBA CDBL function to convert the value to double data type along with some simple to advanced examples. Below are some useful excel articles related to VBA –

  • How to Declare VBA Global Variables?
  • VBA Data Type
  • Transpose in VBA
  • VBA Switch Function
5 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA CDBL Function Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More