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

DIM dSomething as DOUBLE

"Varne" <(E-Mail Removed)> wrote in message
news:621292C4-88A3-43DE-B9EE-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:621292C4-88A3-43DE-B9EE-(E-Mail Removed)...
> 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