Here’s What Double Is in VBA and How it Works

Like any other programming language, Visual Basic for Applications also has its own datatypes. “Double” (double precision floating point) is one among them. A double datatype has double the storage size or capacity when compared to the “Single” (integer) datatype. The size of the Double datatype is about eight bytes, which is the size of two integers. Variables of a double datatype can hold both fractions and integers.

Contents

Declaring a Variable of Double Datatype

Syntax:

Dim <variable name> as Double

Example:

This is an example wherein a double variable is declared but not assigned a value. So, until a value is assigned to the said double variable, its value remains 0 which is the default value.

Sub double_demo()
Dim amount As Double
MsgBox amount
End Sub

Output:

A Microsoft Excel prompt box that reads "0" with an OK button.

Now let us assign value to it and see its result.

Sub double_demo()

' declare a variable
Dim amount As Double

'assign a value
amount = 2.34567890124556

' display the value in a message box
MsgBox amount

End Sub

Output:

A decimal value is assigned to the amount variable this time. The value is displayed in the message box.

A Microsoft Excel prompt box that reads "2.34567890124556" with an OK button.

Now, let us try to assign an integer value to the variable:


Sub double_demo()

' declare a variable
Dim amount As Double

'assign a value
amount = 234567890124557#

' display the value in a message box
MsgBox amount

End Sub

Output:

A Microsoft Excel prompt box that reads "234567890124557" with an OK button.

Integer vs Single vs Double Datatypes

Integer datatype: Converts the decimal values to the nearest integer values.

Single datatype: Displays up to two digits of decimal places.

Double datatype: Stores negative values in the range -1.79769313486231E308 to -4.94065645841247E324 and positive values in the range 4.94065645841247E-324 to 1.79769313486232E308.

Example 1

In this code, we are trying to display a decimal value as an integer. So, the decimal value automatically converts the number to the nearest integer.

Sub integer_demo()

' declare 3 variables
Dim a As Integer
Dim b As Integer
Dim c As Integer
'assign  values to all the 3 integer variables
a = 2.34567890124557 '  decimal value less than .50
b = 2.5              '  decimal value equal to .50
c = 2.5676856653     '  decimal value more than .50

' display the 3 values in a message box
MsgBox &amp;amp;quot;a = &amp;amp;quot; &amp;amp;amp; a &amp;amp;amp; vbCrLf &amp;amp;amp; &amp;amp;quot;b = &amp;amp;quot; &amp;amp;amp; b &amp;amp;amp; vbCrLf &amp;amp;amp; &amp;amp;quot;c = &amp;amp;quot; &amp;amp;amp; c

End Sub

Output:

A Microsoft Excel prompt box that reads a=2 b=2 c=3

A decimal value less than or equal to .50 rounds down the integer to the same number as it is. But the inter value is rounded up to the next integer number if the decimal value is greater than .50.

So, here:

  1. 2.34567890124557 is rounded down to 2
  2. 2.5 is rounded down to 2
  3. 2.56 is rounded up to 3

Example 2

In this example, we will declare and assign 2 variables of a single datatype. Let us see how the output is displayed when we run the program.

Sub single_demo()

' declare 3 variables
Dim a As Single
Dim b As Single
Dim c As Single

'assign  values to all the 3 Single variables
a = 23.4567890124557 '  decimal value less than .50
b = 25.555           '  integer and decimal value with a total of less than 7 digits
c = 2.5676856653     '  decimal value more than .50

' display the 3 values in a message box
MsgBox &amp;amp;quot;a = &amp;amp;quot; &amp;amp;amp; a &amp;amp;amp; vbCrLf &amp;amp;amp; &amp;amp;quot;b = &amp;amp;quot; &amp;amp;amp; b &amp;amp;amp; vbCrLf &amp;amp;amp; &amp;amp;quot;c = &amp;amp;quot; &amp;amp;amp; c

End Sub
A Microsoft Excel prompt box that reads a=23.45679 b=25.555 c=2.567686

The values are reduced to a total of 7 digits. The last or 7th digit (the integer and decimal numbers put together) is rounded up or down based on whether the 8th digit of the original number is more than or less than 5.

So, while displaying

23.4567890124557 has become 23.45679

25.555 remains the same as it is within 7 digits for display

2.5676856653 has become 2.567686

Example 3

Now, in this example, we will declare three variables of double data type and assign values to them. Let us see what gets displayed in the message box when we run the code.

Sub double_demo()

' declare 3 variables
Dim a As Double
Dim b As Double
Dim c As Double

'assign  values to all the 3 Double variables
a = 43.4567890124557 '  decimal value less than .50
b = 85.5555666689734 '  decimal value equal to .50
c = 72.5676856653435 '  decimal value more than .50

' display the 3 values in a message box
MsgBox &amp;amp;quot;a = &amp;amp;quot; &amp;amp;amp;amp; a &amp;amp;amp;amp; vbCrLf &amp;amp;amp;amp; &amp;amp;quot;b = &amp;amp;quot; &amp;amp;amp;amp; b &amp;amp;amp;amp; vbCrLf &amp;amp;amp;amp; &amp;amp;quot;c = &amp;amp;quot; &amp;amp;amp;amp; c

End Sub

Output:

A Microsoft Excel prompt box that reads a=43.4567890124557 b=85.5555666689734 c=72.5676856653435

Here the same principle has been applied with a difference in the size. Double data type can hold and display a total of 15 digits per variable. This is inclusive of the integer and decimal parts. Based on the value of the 15th digit, (whether it is greater than or less than 5), the 14th digit remains the same or rounds off to the next digit. This does happen when we press the end key at the end of the line and so, the output remains the same as in the code.

To explain in short,

The value 43.456789012455745 will turn out to be 43.4567890124557 when we press the enter key at the end of the line after typing the code.

Similarly, 43.456789012455767 will turn out to be 43.4567890124558 when we press the enter key at the end of the line after typing the code.

Example 4

This time we will use cell reference and convert values in an Excel sheet.

The Excel sheet has values in column A as in the image below.

Excel sheet with 6 rows under Column A. Row 1 reads 34.565766, Row 2 is 2.768668657, Row 3 is 7.34543, Row 4 is 3.45, Row 5 is 23.568, and Row 6 is 2343.6868

Now, we will use a small piece of code to populate the values that are converted into “Double” datatype into column B.

Sub double_demo_2()

' declare variables
Dim r As Integer ' no of rows
Dim i As Integer ' counter for the loop
Dim d As Double ' a double variable
' assigning values
r = 6 ' 6 rows to be iterated

' loop
For i = 1 To r

' assign the col A value to double datatype
d = Cells(i, 1).Value

' assign the converted value to 2nd col ( col B)
Cells(i, 2).Value = d

Next

End Sub

Output:

Excel sheet with 6 rows under Columns A and B. Row 1 reads 34.565766, Row 2 is 2.768668657, Row 3 is 7.34543, Row 4 is 3.45, Row 5 is 23.568, and Row 6 is 2343.6868. The numbers are all the same in column B.

Since the data in column A is within the double datatype’s numeric range, column B is also populated with the same values post the conversion through the code.

Conclusion

In most of the cases, the integer and the single datatypes will suffice. There are rare situations where a double datatype will be required to store and use long numbers and will add more decimal places. So, double datatype comes to our rescue in such situations. We can keep this in mind and use it wherever required in our code.

Leave a Reply

Your email address will not be published. Required fields are marked *