VBA Double

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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 IntegerVBA IntegerIn VBA, an integer is a data type that may be assigned to any variable and used to hold integer values. In VBA, the bracket for the maximum number of integer variables that can be kept is similar to that in other languages. Using the DIM statement, any variable can be defined as an integer variable.read more

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.

vba-double

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Double (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples to use VBA Double Data Type

You can download this VBA Double Excel Template here – VBA Double Excel Template

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more below.

Code:

Sub Integer_Ex()

  Dim k As Integer

  k = 2.569999947164

  MsgBox k

End Sub
VBA Double Example 1

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 keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more F5 to see the final value in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

VBA Double Example 1-1

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
VBA Double Example 1-2

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

VBA Double Example 1-3

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
VBA Double Example 1-4

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

VBA Double Example 1-5

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.

VBA Double Example 1-6

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

VBA Double Example 1-7

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 referenceHow To Work With Cell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more in a worksheet. Below are the numbers we have entered in the worksheet.

Data Example 2

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
Integer Example 2-1

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

Integer Example 2-2

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

Now, we will change the VBA data typeVBA Data TypeData type is the core character of any variable, it represents what is the type of value we can store in the variable and what is the limit or the range of values which can be stored in the variable, data types are built-in VBA and user or developer needs to be aware which type of value can be stored in which data type. Data types assign to variables tells the compiler storage size of the variable.read more 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
Single Example 2-3

This code will give the below result.

Single Example 2-4

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
VBA Double Example 2-5

It will return the below result.

VBA Double Example 2-6

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.

Recommended Articles

This article has been a guide to VBA Double. Here, we discuss using and declaring the VBA Double data type to store longer decimal places, along with practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –