# VBA DOUBLE DATA TYPE

## What is Excel VBA Double Data Type?

In Excel VBA (Visual Basic for Applications), the Double data type is a variable type used to store double-precision floating-point numbers. It can represent a wide range of numerical values, both integer and fractional, with a high degree of decimal precision. Double is particularly useful when precise numeric calculations are required, and it is commonly employed for scientific, financial, and engineering applications. The Double data type in VBA occupies 8 bytes of memory.

See the following example:

Here, a subroutine is created to view and print a Double variable. It provides decimal precision and will print the same value without rounding it off, unlike other data types. You can see that the same number has been printed in the “Immediate” tab.

###### Key Takeaways
• The Double data type in Excel VBA is used for handling double-precision floating-point numbers. It allows for a wide range of numerical values with decimal precision.
• Explicitly declare Double variables using the Dim statement before use.
• You can declare and manipulate Double variables in VBA for various mathematical calculations, making it suitable for scenarios where higher precision is required compared to the Single data type.
• Excel VBA provides built-in mathematical functions that work with Double values, such as `sin`, `cos`, `exp`, and `log`.
• If a decimal number is used without a specified data type, VBA interprets it as a Double by default.

### How to Use Excel VBA Double Data Type?

You can see how VBA Double to String conversion works below.

1. Open the Excel Workbook, then go to the Developer tab.

In Developer, select “Visual Basic” to write codes in Excel VBA.

It opens the Excel VBA Editor. But initially, it will be empty. To be able to code, select “Insert.” In the drop-down, select “Module.”

Now, you can code in Excel VBA.

2. Name the subroutine to perform VBA Double to String conversion.

3. Initialize a VBA Double Variable and give it some double value.

4. Give an Integer variable and assign it some value.

5. Create a string variable and perform VBA Double to String conversion using the CStr() function.

The VBA CStr function converts any datatype to the string datatype.

6. Assign the Integer variable to cell “A1.”

7. Assign the double variable to Cell “A2.”

8. Assign the string variable to cell “A3.”

Note: There is no need to use the Value function in this case since it will take the numeric value in the String and be considered an integer.

Code:

Sub DoubleExample()
Dim no As Double
no = 100030.678
Dim n As Integer
n = -500.098
Dim s As String
s = CStr(no)
Range(“A1”) = n
Range(“A2”) = no
Range(“A3”) = s
End Sub

9. In the VBA toolbar, click the “Run” button.

You can also press the “F5” button on your keyboard to run the above subroutine. After running the code, go back to the worksheet to view the output.

As seen above, the string variable has been aligned to the left rather than its right despite being a numerical value. From this, we can infer that the VBA Double to String conversion was performed successfully.

Since you now know how to apply the VBA Double Data Type, you can view some examples using them.

### Double-Click Event

In VBA, you can handle the VBA double-click event for a specific object, such as a worksheet, a cell, or a user form.

Consider this example:

In this example, you use the VBA double-click event in any Worksheet so that this subroutine is triggered. In this case, instead of the default action, that is, editing any cell when you double-click, a VBA double-click event happens.

Therefore, when you go to that specific WorkSheet (Sheet2) in this example, you will get a popup.

### Examples to Use VBA Double Data Type

Let us view the different and practical ways in which VBA Double Data Types are used.

#### Example #1

Accepting an input from the user, you must find the area of a square, irrespective of the decimals or not. In such cases, you will use the VBA Data type Double.

• Step 1: Start with creating a subroutine to find the area of a square.
• Step 2: Define a double variable and get the value from the user. Also if you want, you can name the title of the InputBox as shown below.
• Step 3: Define the area of a square as another Double variable.
• Step 4: It is known that the area of a square is “s*s.” From this, you can define it in VBA, starting with checking whether the given input is numeric or not.

If true, find the square of the given side (use “^” in VBA to depict the power of any number). Print the result in a Message Box in VBA.

• Step 5: Define an Else condition in case the given input is non-numeric. It is to prevent any object mismatch errors. Return a Message Box saying “Invalid Input.”

Code:

Sub AreaOfSquare()
Dim s As Double
s = InputBox(“Enter the side of a square (in decimals)”, “Area of Square”)
Dim ar As Double
If IsNumeric(s) Then
ar = s ^ 2
MsgBox “Area of a Square of side ” & s & ” cm is: ” & ar & ” sq.cm”
Else: MsgBox “Invalid Input!”
End If
End Sub

• Step 6: To run the code, press the “run” button in the Excel toolbar and view the output.

Now, the Input Box accepting values from the user appears.

You can see that the title of the Input Box is the same as defined earlier. Now press “OK” or the Enter button.

We can see the area the square covers given the side as an input.

#### Example #2

Given a sales table that a company manages, you have to find the total sum of all the values in the table without losing the decimals. It is an excellent use case where you’d use the VBA Double data type.

To a given set of values, press “Ctrl + T” to convert it into an Excel Table. You can see how to find the sum of this table below.

• Step 1: Name the subroutine which will find the sum of the table given above.
• Step 2: Define a ListObject datatype and assign the table to it. It will hold all the values present in “Table1” (the table above).

In this, you specify which WorkSheet the table is in and add the table name (“Table1” in this case).

• Step 3: Define a variable that will hold the sum of all the values of the table as a double data type and an iterative variable.
• Step 4: Define a nested FOR-loop. The first loop is to get the individual rows in the table. The second loop runs through each row of the table with the iterative variable mentioned in the previous step.
• Step 5: Add each table value with the sum variable declared earlier. Then, continue the FOR-loop.
• Step 6: Print the total sum in cell “C2.”

Code:

Sub TotalSumOfTable()
Dim tb1 As ListObject
Set tb1 = Worksheets(“Example_2”).ListObjects(“Table1”)
Dim res As Double
Dim i As Range
For Each R In tb1.ListRows
For Each i In R.Range
res = res + i.Value
Next i
Next R
Range(“C2”).Value = res
End Sub

• Step 7: Run the above subroutine by pressing the “green arrow” button on the VBA toolbar.

Go back to the WorkSheet to view the output.

#### Example #3

Consider an example where, given a set of radii of a circle, you will need to find the area of that specific circle. You will also need to highlight the areas having a value greater than 100. It can be done by declaring the areas and the radii as double variables and starting to equate accordingly.

• Step 1: Name the sub-procedure you will use to print the area of a circle.
• Step 2: Find the size of the table using the xlUp function. Declare a Long variable to store the size of the table.

You take column “A” and count the total number of rows, then end the counting at the last non-empty cell with the xlUp function, used to simulate the “up” arrow key on your keyboard.

• Step 3: Define the radius variable and the area variable as Double datatypes.
• Step 4: Start a FOR-loop running through the table and check whether all the values are numeric.
• Step 5: If the value is numeric, assign that value to the radius variable.
• Step 6: Calculate the area of the circle with the formula  and assign it to the second column of the Excel worksheet.
• Step 7: Check whether the area is greater than 100. If so, change the font color using VBA ColorIndex.

Here, the color index 4 means that the font color in VBA will change to green.

• Step 8: Define an Else condition in the case that the value in the table is non-numeric (to prevent any object type mismatch errors).

In case of a non-numeric value, there will be a Message Box popping saying that the given input is invalid.

Code:

Sub AreaOfCircle()
Dim lastRow As Long
lastRow = Range(“A” & Rows.Count).End(xlUp).Row
Dim area As Double
For cell = 2 To lastRow
If IsNumeric(Cells(cell, 1).Value) Then
area = 3.14159 * rad ^ 2
Cells(cell, 2).Value = area
If Cells(cell, 2).Value >= 100 Then Cells(cell, 2).Font.ColorIndex = 4
Else
MsgBox “Invalid Input!”
End If
Next cell
End Sub

• Step 9: Run the above sub-procedure by clicking the green arrow button on the VBA toolbar.

After pressing the button, go to the worksheet to view the results.

In this case, you can see that the values greater than 100 are highlighted in green color.

### Important Things to Note

• Use Double when you need higher decimal precision in numerical calculations.
• Explicitly declare Double variables using the Dim statement before use.
• Double can handle a wide range of values, making it suitable for both large and small numbers.
• Utilize scientific notation for representing very large or very small numbers.
• Avoid using Double for exact comparisons due to potential precision issues with floating-point arithmetic.
• Do not solely rely on a specific number of decimal places; consider the nature of floating-point representation.
• Be mindful that Double variables consume more memory (8 bytes) compared to Single variables (4 bytes).

1. How do I declare a variable as a Double in VBA?

There are two main ways you can declare a variable as double, as shown below:

If you don’t add any specifications, VBA will automatically assume that the given variable is of VBA double data type.

2. What is the range of values that a Double variable can hold in VBA?

A Double variable in VBA can hold values ranging from approximately `-1.79769313486232E308` to `-4.94065645841247E-324` for negative values and from `4.94065645841247E-324` to `1.79769313486232E308` for positive values.

3. What is the difference between Single and Double in VBA?

In VBA, Single is a data type for single-precision floating-point numbers, while Double is a data type for double-precision floating-point numbers. Double provides greater precision and a larger range of values compared to Single.

See the example below:

Seeing the output:

4. Can I use scientific notation for Double values in VBA?

Yes, you can use scientific notation for Double values in VBA. For example, you can represent 1.23 x 10^5 as 1.23E+05 in VBA.