1 Replies - 295 Views - Last Post: 08 March 2019 - 02:18 AM Rate Topic: -----

#1 amy.vaulhausen   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 01-April 16

Weird Math Error in VBA for Excel

Posted 07 March 2019 - 03:40 PM

Weird Math Error in VBA for Excel

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

Is This A Good Question/Topic? 0
  • +

Replies To: Weird Math Error in VBA for Excel

#2 andrewsw   User is online

  • never lube your breaks
  • member icon

Reputation: 6813
  • View blog
  • Posts: 28,185
  • Joined: 12-December 12

Re: Weird Math Error in VBA for Excel

Posted 08 March 2019 - 02:18 AM

(I wouldn't describe it as 'weird'.)

The two versions of the formula are not the same. The Excel version has an additional pair of parentheses. cf operator precedence
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1