Hi all, would love feedback on unsusual error I'm getting.

Very strange. I have a simple formula that works great if I

only use it in a normal sheet cell and copy it down by columns,

but if I try to do a simple iteration in vba code to perform the same function I get the wrong values.

Description : A number is squared, then divided by another value

between 0.99 to 1.99, next the modulus is taken and then

the number is squared again and the whole formula repeated.

If I copy the formula statement down column wise it calcs fine,

including reasonable decimal accuracy.

There are four inputs ;

base value (inputx)

decx = divisor

mod value

The first formula placed at (E2) looks like ; =MOD(((B2^2)/$B$3),$B$4)

In (E3) this statement is placed ; =MOD(((E2^2)/$B$3),$B$4)

Then this exact same statement is copied down, columnwise to the next 98 cells.

All great, no problem. It seems accurate value wise, right to decimal

precision, with values past the decimal point showing in all column cells.

Some sample input values for testing ;

INPUTX --> 231

DECX 1.010101

MOD 400

LOOPTIMES 100

But when I try to implement this is Excel VBA code (Excel 2007)

I often get the wrong values and absolutely no values past the decimal point ever show.

Have tried using all kinds of different data types ; single, double, variant, etc... but all values returned by the VBA function I made always returns

whole numbers, and is often wrong and certainly does not agree with the

values returned by the simple column based statements.

Have tried to find ways around this or to fix this, came across "CDEC", tried

this and nothing changed. Totally stumped and would love some insight into

if this can be fixed so that the function loop returns the same values with

same kind of decimal precision as the column based statements and

would greatly appreciate feedback on how mthis can be done.

Am including my sample code below ;

Public Function SQRD(inputx As Variant, looptime As Variant, decx As Variant) As Variant Application.Volatile Dim Count As Integer SQRD = CDec(inputx) 'Dim decx As variant Count = 1 For Count = 1 To looptime SQRD = CDec(SQRD ^ 2) '+ looptime SQRD = CDec(SQRD Mod 400 / decx) Next Count End Function

see attached spreadsheet with code