Home  ›  Fatal dim avg n Could Not Coerce Input Data to Double Can t Continue

Fatal dim avg n Could Not Coerce Input Data to Double Can t Continue

Written By quarta-feira, 12 de outubro de 2022 Add Comment Edit

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.

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 is1.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 howVBA 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

hollandditind.blogspot.com

Source: https://www.educba.com/vba-double/

0 Response to "Fatal dim avg n Could Not Coerce Input Data to Double Can t Continue"

Postar um comentário

Assinar: Postar comentários (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel