Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Decimal
Hi!
Can someone show how to declare the Decimal variable type using CDec function? The following codes do not work. Run time error 6 - Overflow Sub Test() Dim Data(300000) As Long Dim AC As Long For AC = 1 To 300000 Data(AC) = Data(AC - 1) + AC Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Decimal
DIM dSomething as DOUBLE
"Varne" wrote in message ... Hi! Can someone show how to declare the Decimal variable type using CDec function? The following codes do not work. Run time error 6 - Overflow Sub Test() Dim Data(300000) As Long Dim AC As Long For AC = 1 To 300000 Data(AC) = Data(AC - 1) + AC Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Decimal
First off, the Decimal data type is not a "real" VB data type; rather, it is
a sub-type of the Variant data type. So, anything you want the digit range of a Decimal data "type" needs to be a Variant. In this case, that probably should be the AC variable since it will be used as the index to your array; although I think your array should be a variant too as the values being assigned to it look as if they can get quite large. Interestingly, only one reference to a variable in an expression needs to be converted using CDec, then any other references to that variable within the same expression will automatically be elevated to a Decimal type. With that said, it looks like the array, probably because it uses a Decimal typed index, is automatically being converted to a Decimal data type as well. Anyway, try your code this way... Sub Test() Dim Data(3000000) As Variant Dim AC As Variant For AC = 1 To 3000000 Data(AC) = Data(AC - 1) + CDec(AC) Next End Sub Here is some information about Decimal data types that I have posted in the past (it was an answer to a question back posted back then) that you may find helpful... You could cast (you can't Dim) a Variant variable as a Decimal type (96-bit number) and get some 28 or 29 digits of accuracy depending if there is a decimal in the answer or not. Simply Dim a variable as Variant and CDec a number (any number will do) into it to make it the Decimal type. Thereafter, that variable will track 28/29 digits of accuracy. For example the following function will calculate factorials up to 29 digits of display before reverting to exponential display. Function BigFactorial(ByVal N As Integer) As Variant If N < 28 Then BigFactorial = CDec(1) Else BigFactorial = CDbl(1) End If For x = 1 To N BigFactorial = x * BigFactorial Next End Function However, you have to watch out for overflows with Decimal data types -- once over 28/29 characters, they will produce an overflow error. So, if you tried to use the above function like this Debug.Print 10*BigFactorial(27) you would get an overflow error but Debug.Print 10*BigFactorial(28) would work fine (the difference being in the first case BigFactorial has a Decimal subtype and in the second case the subtype is a Double). More generally, if a Variant variable is assigned a value that was cast to Decimal, any calculation involving that variable will be "performed" as a Decimal; and then the result cast back to the variable receiving it. If the result is assigned back to the variable that was originally cast to Decimal, that variable continues to contain a Decimal type value. For example, X = CDec(135.6) X = X - 135 X = X / 7 Print X == 0.0857142857142857142857142857 You have to be careful with this though . . . all VB functions return non-Decimal data.and assigning *that* back to the Variant that was cast as Decimal "collapses" it back to a less robust data type. For example, continuing the code above X = Sqr(X) Print X == 0.29277002188456 -- Rick (MVP - Excel) "Varne" wrote in message ... Hi! Can someone show how to declare the Decimal variable type using CDec function? The following codes do not work. Run time error 6 - Overflow Sub Test() Dim Data(300000) As Long Dim AC As Long For AC = 1 To 300000 Data(AC) = Data(AC - 1) + AC Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert decimal commas to decimal points? | Excel Discussion (Misc queries) | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Batch converting CSV files from comma-decimal to period-decimal | Excel Discussion (Misc queries) | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) |