How To Declare Double Data Type In VBA? - WallStreetMojo

All Courses 🛒Login

VBA Resources

• E-Books
• Career Guides
• Interview Prep Guides
• Free Practice Tests
• Excel Cheatsheets

All BlogsVBA ResourcesVBA DoubleData Types in VBA

• Basic Data Types
  • VBA Data Type
  • VBA Boolean
  • VBA Integer
  • VBA Long
  • VBA Double
  • VBA String to Integer
  • VBA String to Date
  • VBA Variant
  • VBA Value Property

• Variables and Constants
  • VBA Const
  • VBA Global Variables
  • VBA Public Variables
  • VBA Variable Declaration
  • VBA Variable Range
  • VBA Variable Types
  • VBA Type

• Type Conversions
  • VBA CDBL Function
  • VBA CDEC
  • VBA CLng
  • VBA CSTR
  • VBA Val Function
  • VBA VARTYPE Function
  • VBA INT
  • VBA ENUM
  • VBA MOD

Data Types in VBAFinancial Modeling Immersive Program (2 Months)

💡 Expert-Led Sessions📊 Build Financial Models⏳ 60+ Hours Learning

ENROLL NOW VBA Double

Publication Date :

05 Sep, 2019

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Share

Download FREE VBA Double Template and Follow Along!VBA Double Excel Template.xlsm

Table Of Contents

Excel VBA Double Data Type

VBA Double is a data type we assign to declare variables, an improved or longer version of the “Single” data type variable. One can usually use it to store longer decimal places.

VBA Integer data type always converts decimal values to the nearest integer value. The single data type can show up to two digits of decimal places. On the other hand “Double” data type can store values from -1.79769313486231E308 to -4.94065645841247E324 for negative values and for positive numbers it can store values from 4.94065645841247E-324 to 1.79769313486232E308.

More importantly, it consumes 8 bytes of memory.

Examples to use VBA Double Data Type

Example #1

Before we see the example of the Double data type, let us look at the example codes of “Integer” and “Single” data types in VBA. First, look at the VBA code below.

Code:

Sub Integer_Ex() Dim k As Integer k = 2.569999947164 MsgBox k End Sub

We have declared the variable “k” as Integer. For this variable, we have assigned the value as 2.569999947164.

Let us run this code manually or use excel shortcut key F5 to see the final value in the message box in VBA.

The result is showing as 3 instead of the supplied number of 2.569999947164. The reason is VBA has converted the number to the nearest integer value, i.e., 3.

When the decimal value is more than 0.5, it will convert to the next integer value, and when the decimal value is less than 0.51, it will convert to the below Integer value.

Now, we will change the data type from Integer to Single.

Code:

Sub Integer_Ex() Dim k As Single k = 2.569999947164 MsgBox k End Sub

Run the code through shortcut key F5, and see what number we get this time.

This time we got the result of 2.57, so we got two decimal places. The original value we have assigned was 2.569999947164, so in this case, third, the placed decimal value is 9, so since this is more than 5, it has converted the second place decimal value 6 to 7.

Now, change the data type from Single to Double.

Code:

Sub Integer_Ex() Dim k As Double k = 2.569999947164 MsgBox k End Sub

Now, run the code manually and see how many digits we get in the message box result.

This time we got all the decimal values. So, we can supply up to 14 digits of decimal places under the Double data type.

Suppose you supply any value greater than 14 decimal positions that it will convert to the nearest value. For example, look at the below image.

We have typed 15 decimal places instead of 14. If we press the "Enter" key, it will be back to 14 digits only.

Instead of 59 (last two digits), we got 6, i.e., since the last digit is 9, which is greater than the last number 5, 5 is converted to the next integer value, i.e., 6.

Example #2

Now we will show how to work with cell reference in a worksheet. Below are the numbers we have entered in the worksheet.

Let us capture the same values next by using INTEGER data type, SINGLE data type, and DOUBLE type.

Below is the code to retain values from columns A to B using the INTEGER data type.

Code:

Sub Double_Ex() Dim k As Integer Dim CellValue As Integer For k = 1 To 6 CellValue = Cells(k, 1).Value Cells(k, 2).Value = CellValue Next k End Sub

Let us run the code through shortcut key F5 to see what values we get in column B.

When we used Integer as data type, we got all the whole numbers, i.e., without decimals.

Now, we will change the VBA data type of a variable from Integer to Single.

Code:

Sub Double_Ex() Dim k As Integer Dim CellValue As Single For k = 1 To 6 CellValue = Cells(k, 1).Value Cells(k, 2).Value = CellValue Next k End Sub

This code will give the below result.

This time we got only two decimal places.

Now, change the data type from Single to Double.

Code:

Sub Double_Ex() Dim k As Integer Dim CellValue As Double For k = 1 To 6 CellValue = Cells(k, 1).Value Cells(k, 2).Value = CellValue Next k End Sub

It will return the below result.

We have got exact values from column A.

Things to Remember

• Double is an improved data type of Single data type.
• It can hold up to 14 decimal places.
• It consumes 8 bytes of system memory.

INVESTMENT BANKING RESOURCESLearn the foundation of Investment banking, financial modeling, valuations and more.Join Wallstreetmojo YoutubeFREE EXCEL COURSESLearn MS Excel right from scratch. Master excel formulas, graphs, shortcuts with 3+hrs of Video.Join Wallstreetmojo InstagramFREE FINANCE MODELING IN EXCELLearn Financial Modeling in Excel with this Step by Step Guide (Colgate Case Study)Join Wallstreetmojo LinkedinINVESTMENT BANKING RESOURCESLearn the foundation of Investment banking, financial modeling, valuations and more.Join Wallstreetmojo YoutubeJoin Wallstreetmojo Instagram