EDUCBA

Home VBA VBA Resources VBA Conversion Functions VBA Double

VBA Double

By Ashwani JaiswalAshwani Jaiswal

VBA Double

Excel VBA Double Data Type

In VBA, we have different types of data types that are used as per the need. Like, Integer data type is used for numbers, the String data type is used for alphabets and text and a Long data type is used when we need to use numbers or text without any limit. Similarly, we have a Double data type that is used for decimal numbers. Although, we can use Single for decimals but this will retain only values to 2 points of the decimal. If we want to use decimal values without any limit then we have a Double data type for this.

If we use Integer instead of Double data type for decimal values then it will convert the decimal value into the nearest whole number integers. And also, there is some limit of using Double data type as well. Double can accommodate negative values from -1.79769313486231E308 to -4.94065645841247E324 and for positive values from 4.94065645841247E-324 to 1.79769313486232E308. Beyond this, the value cannot be accepted.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use VBA Double Data Type in Excel?

Below are the different examples to use Double Data Type in Excel VBA.

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

VBA Double – Example #1

In this example we will see, how different data types such as Integers and Single give the output for any decimal value and on the other hand how Double data type gives the output of the same decimal value. For this, follow the below steps:

Step 1: Open a Module from the Insert menu tab.

VBA Double Example 1-1

Step 2: Write the subprocedure for VBA Double. We can choose any other name for defining the name of VBA Double as per our choice.

Code:

Sub VBA_Double()

End Sub

VBA Double Example 1-2

Step 3: Now define a variable, let’s say it be A as Integer as shown below.

Code:

Sub VBA_Double()

Dim A As Integer

End Sub

VBA Double Example 1-3

Step 4: Assign a decimal value to the defined variable. Let’s say that the decimal value is 1.23456789 as shown below.

Code:

Sub VBA_Double()

Dim A As Integer
A = 1.23456789

End Sub

VBA Double Example 1-4

Step 5: Now we need a platform where we can see the output. Here, we can use Msgbox or Debug.Print function. We will use Msgbox which is the most traditional way of doing.

Code:

Sub VBA_Double()

Dim A As Integer
A = 1.23456789
MsgBox A

End Sub

VBA Double Example 1-5

Step 6: Run it by clicking on the Play button or by pressing the F5 key.

VBA Double Example 1-6

We will see, the output message box with value “1”. This means the Integer data type has converted the decimal value to the nearest whole number as 1.

Step 7: Now let’s change the data type from Integer to Single and see what Single data type will return us.

Code:

Sub VBA_Double()

Dim A As Single
A = 1.23456789
MsgBox A

End Sub

VBA Double Example 1-7

Step 8: Again run the code by clicking on the Play button or by pressing the F5 key.

VBA Double Example 1-8

We will see, the Single data type has returned the decimal value which is 1.234568 whereas we fed the input value as 1.23456789.

This means, the data type Single converts the fed decimal value into the nearest possible decimal value by last digit number conversion to nearest value which should be less than 5.

Step 9: Now we will use data type Double in place of Single and see what output we would get.

Code:

Sub VBA_Double()

Dim A As Double
A = 1.23456789
MsgBox A

End Sub

VBA Double Example 1-9

Step 10: Again run the code by clicking on the Play button or by pressing the F5 key.

VBA Double Example 1-10

We will see, the message box has exactly the same value which we fed which means Double didn’t convert the input value if it falls under its limit.

VBA Double – Example #2

In this example, we will use cell references. And we will start working in the same way as we have seen in example-1. For this, we have some data in column A in multiple-digit of decimals.

Multiple-digit of decimals

Follow the below steps:

Step 1: Write the subprocedure of VBA Double as shown below.

Code:

Sub VBA_Double2()

End Sub

VBA Double Example 2-2

Step 2: First, we will start with Integer. So define a variable as Integer as shown below. This would be our input variable.

Code:

Sub VBA_Double2()

Dim A As Integer

End Sub

VBA Double Example 2-3

Step 3: After that, we will again define a variable as an Integer. This is a variable in which we will store the output.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Integer

End Sub

Define variable as Integer

Step 4: Open a For-Next loop as shown below. This is where we will write the condition to get the data from a column to another column.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Integer

For

Next A

End Sub

For-Next loop

Step 5: Select the range of cells in variable A. Here, our data is from cell A1 to A10.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Integer

For A = 1 To 10

Next A

End Sub

VBA Double Example 2-6

Step 6: Now select the cell values which we want to place. Here the cell values are in the first column.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Integer

For A = 1 To 10

Deci = Cells(A, 1).Value

Next A

End Sub

VBA Double Example 2-7

Step 7: Now we will put the selected values into column 2 which is B in defined variable Deci.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Integer

For A = 1 To 10

Deci = Cells(A, 1).Value
Cells(A, 2).Value = Deci

Next A

End Sub

Defined variable Deci

Step 8: Run the code by clicking on the Play button or by pressing the F5 key.

Output of Data Type as Integer

As we had selected the Integer data type for output variable Deci, so it converted the decimal numbers into Integer whole numbers in Column B.

Step 9: Let’s change the data type of Deci which is the output variable from Integer to Single as shown below.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Single

For A = 1 To 10

Deci = Cells(A, 1).Value
Cells(A, 2).Value = Deci

Next A

End Sub

Data Type as Single

Step 10: Run the code by clicking on the Play button or by pressing the F5 key. We will see, decimal numbers from column A got converted into best possible closer decimal numbers.

Output of Data Type as Single

Step 11: Let’s change the output variable data type from Single to Double. And see, what we will get in Column B.

Code:

Sub VBA_Double2()

Dim A As Integer
Dim Deci As Double

For A = 1 To 10

Deci = Cells(A, 1).Value
Cells(A, 2).Value = Deci

Next A

End Sub

VBA Double Example 2-12

Step 12: Again Run the code by clicking on the Play button or by pressing the F5 key. This is how VBA Double works.

VBA Double Example 2-13

Pros & Cons of VBA Double

  • VBA Double converts the same number into decimal numbers in a specific range.
  • It is quite easy to use.
  • We can use Double data type instead of Integer or Single, in a similar way.
  • It doesn’t consider the decimal number beyond the limit which we have seen in the above introduction section.

Things to Remember

  • Limit of using VBA Double for negative values is from -1.79769313486231E308 to -4.94065645841247E324 and for positive values is from 94065645841247E-324 to 1.79769313486232E308.
  • VBA Double can hold up to 14 digit values if it is under the above-given limit.
  • VBA Double uses 8 Byte of system memory each type it is used.

Recommended Articles

This is a guide to Excel VBA Double. Here we discuss how to use Double data types in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Rename Sheet
  2. VBA ReDim
  3. VBA Variable Declaration
  4. VBA Environ