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.

How to Use Excel VBA Double Data Type?

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 rad As Double
Dim area As Double
For cell = 2 To lastRow
If IsNumeric(Cells(cell, 1).Value) Then
rad = Cells(cell, 1).Value
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).

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Double Data Type, with its features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Double Data Type. Here we learn how to use Double data type in Excel VBA coding, with step-by-step examples & points to remember. You can learn more from the following articles –

• VBA String to Date
• VBA Long
• VBA Class