## Weird Math Error in VBA for Excel

## Weird Math Error in VBA for Excel

(OP)

Weird Math Error in VBA for Excel

Hi all, would love feedback on unusual 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 this can be done.

Am including my sample code below ;

Hi all, would love feedback on unusual 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 this can be done.

Am including my sample code below ;

#### CODE -->

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

## RE: Weird Math Error in VBA for Excel

What this

SQRD Mod 400 / decx

is actually doing is:

SQRD Mod (400 / decx)

So simply use parenthesis to get the order of operations you require:

(SQRD Mod 400) / decx

(BTW, once you do this, you shouldn't need CDEC)

The second is that, as it turns out, you are doing a different calculation in your VBA than on the spreadsheet.

So, assuming the spreadsheet version is correct:

(SQRD Mod 400) / decx

actually becomes

(SQRD / decx) MOD 400

But then you have the third problem - the VBA MOD function works slightly differently to the sheet MOD function in that the sheet MOD function does floating point, VBA does not ... so you need to write yourself a little supporter function, eg:

## CODE

`Function vbMod(number As Variant, divisor As Variant) As Variant vbMod = number - divisor * Int(number / divisor) End Function`

And thus your original code actually becomes:

## CODE

`Public Function SQRD(inputx As Variant, looptime As Variant, decx As Variant) As Variant Application.Volatile Dim Count As Integer SQRD = inputx Count = 1 For Count = 1 To looptime SQRD = SQRD ^ 2 '+ looptime SQRD = vbMod(SQRD / decx, 400) ' this should look familiar as it mimics the sheet MOD function Next Count End Function`

## RE: Weird Math Error in VBA for Excel

MOD 400 deducts 9600 and you stay with around 3 in inputs difference: 12.54 vs 9.75. After 100 steps I guess that both excel and vba results can be far from exact value

combo

## RE: Weird Math Error in VBA for Excel

## RE: Weird Math Error in VBA for Excel

combo

## RE: Weird Math Error in VBA for Excel

You guys rock!!! Such a day maker!

:)

<3

## RE: Weird Math Error in VBA for Excel

completed? Sorry, bit of a newb

## RE: Weird Math Error in VBA for Excel

… which now exposes the difference between the worksheet power function, and VBA's when using rational numbers. And because of the iterative nature of your SQRD function there only needs to be one teeny discrepancy and ALL subsequent values will be increasingly off. Oh dear, you might think. Not so, as we can use Excel's power function in VBA.

So now the SQRD function (or ast least my version, yours may differ by now if you rae playing with it) becomes (note I've also added a couple of extra optional parameters):

## CODE

`Public Function SQRD(inputx As Variant, looptime As Variant, decx As Variant, Optional Power As Variant = 2, Optional ModVal As Variant = 400) As Variant Application.Volatile Dim Count As Integer SQRD = inputx Count = 1 For Count = 1 To looptime`

SQRD = WorksheetFunction.Power(SQRD, Power)' + looptime SQRD = vbMod(SQRD / decx, ModVal) ' this should look familiar as it mimics the sheet MOD function Next Count End Function## RE: Weird Math Error in VBA for Excel

but it wouldnt run for me it kept failing at the line ;

## CODE -->

It kept saying "sub or function not defined" and highlighted

"vbMod"

## RE: Weird Math Error in VBA for Excel

## CODE

---- Andy

There is a great need for a sarcasm font.## RE: Weird Math Error in VBA for Excel

a number of different approaches with numeric precision addin style tools but nothing worked well.

Have a great day and thanks again!

## RE: Weird Math Error in VBA for Excel

## RE: Weird Math Error in VBA for Excel

after testing the routine against a high math precision python script was that

they did not return the same values, found out the reason for this was that excel

truncates at 15 digits whereas Python lets me set the ceiling much higher with greater

precision, so it turns out altho I can get the VBA code to work, again as you desribed, it loses accuracy after so many iterations due to the built in digit ceiling of Excel / VBA - did not know this before! bummer! :(

## RE: Weird Math Error in VBA for Excel

Because of their adherence to IEEE-754

Floating points can be a pain. You might want to check 0.1 + 0.1 + 0. 1 - 0.3 against your high-precision python library. Remember, with floats, precision is not necessarily the same as accuracy.

## RE: Weird Math Error in VBA for Excel

## RE: Weird Math Error in VBA for Excel

for this purpose it would be better to use decimal arithmetic, for example in python

## CODE

## RE: Weird Math Error in VBA for Excel

what exactly does the second line in your code above do?

## RE: Weird Math Error in VBA for Excel

result = 0.1 + 0.1 + 0.1 - 0.3usingDecimalobjects.Look at the doc about

decimalmodule for python 2.x or 3.x## RE: Weird Math Error in VBA for Excel

<3