Using Double Data Type in VBA


- Written by Puneet

What is the Double Data Type in VBA?

Double is a data type in VBA that stores large and small numbers with up to 15 decimal places. You can store negative numbers from -1.79769313486231E308 to -4.94065645841247E-324, and positive numbers from 4.94065645841247E-324 to 1.79769313486232E308. It’s useful when you need high precision in values (while calculating percentages).

This tutorial will teach us to use the Double data type, its benefits, and its scope. So let’s get started.

How to Declare Double Data Type in VBA

To declare a variable or a constant with a double data type, you need to follow the same steps as to declare a variable with any other data type.

  1. First, use the DIM keywords to start the code to declare a variable.
  2. After that, enter the name of the variable you want to use.
  3. Next, type the keyword “As” to open the list of the data types.
  4. Finally, select the double data type from the list or type it from your keyboard.
Sub double_variable()
Dim myDBL As Double
End Sub

We have declared a variable myDBL with the double data type in the above code. And once you declare a variable as a double, you can store a number with 15 decimal points. Check out the code below:

Sub double_variable()
Dim myDBL As Double
myDBL = 11.45 / 1.56
Debug.Print myDBL
End Sub

In this code, we have divided 11.45 by 1.56. The result is 7.33974358974359, which has fourteen decimals. To see the result, we used Debug.Print to get it in the immediate window.


Benefits of using Double Data Type in VBA

In VBA, using Double Data Type has its benefits:

  • Precision – As we have learned, the Double data type can hold large and small numbers. It is useful when you need precise calculations, as it can take 15 decimals with a number.
  • Compatibility – Double is a widely used data type in other programming languages, making it more compatible with them.

But remember, where double has its advantages, it consumes more memory than other numeric data types, so if you aren’t required to work with large numbers, you can use other data types like Single or Long.

Scope of Double Data Type

You can use Double with a procedure level and a Global (Public) Variable like any other variable.

  • Procedure Level – It’s a variable you can only use within a procedure.
  • Global Level – It’s a variable you can use in multiple procedures and modules.

Must Read – Option Explicit – Variable in a Message Box – Dim Statement

Using Double Data Type with Constants

Like Variables, Double can be used with Constant as well. In the example below, we declared the constant and then used the double data type.

Const myDBL As Double = 2.3456789

As you know, you need to assign a value while declaring a constant. If you want to use a large number throughout your code, use a constant with the Double data type.