×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Weird Math Error in VBA for Excel
4

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 ;

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

You have three problems. The first is BODMAS (or PEMDAS, if you prefer). It's the order of operations.

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

Another issue is the precision of calculations, you simply need more significant digits. After 5 steps:
source	input	        square	        divided by 2
excel	98.53749683	9709.638282	9612.541996
vba	98.52320768	9706.822453	9609.754324 
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

Er .. something must be wrong combo. The results here match exactly when using my code. There is no discrepancy between the spreadsheet function and the VBA results for all 100 iterations

RE: Weird Math Error in VBA for Excel

It's strange, I was sure I tested original procedure with corrected division and MOD calculation and got the above differences. I repeated rewriting finction and excel and vba match.

combo

RE: Weird Math Error in VBA for Excel

(OP)
Thank you so much! this totally solved the problem, I understand now what was wrong!

You guys rock!!! Such a day maker!

:)

<3

RE: Weird Math Error in VBA for Excel

(OP)
Hi, I have been testing this code string and it was working great, until ... :( I tried to enter either a rational number for either the power to raise to, or for decx(divisor), so when I use (X)^2 and any integer for decx, no problem but if I try (X)^1.9999 and or decx = 1.0101, it returns the wrong value even when the iterations are low - am guessing this means its still only accepting integers somewhere in the formula ... when I do this with out code right in the sheet I can see the correct values returned, comparing this to the code its way off, how can I fix this? Also, how do I tag a thread as answered or
completed? Sorry, bit of a newb

RE: Weird Math Error in VBA for Excel

Well, now you seem to have changed the requirements …


… 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

(OP)
Hi Strongmn! :) I tried the code you suggested and worked on this for quite a while
but it wouldnt run for me it kept failing at the line ;

CODE -->

SQRD = vbMod(SQRD / decx, ModVal) 

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

RE: Weird Math Error in VBA for Excel

Do you have this:

CODE

Function vbMod(number As Variant, divisor As Variant) As Variant
    vbMod = number - divisor * Int(number / divisor)
End Function 
in your code that strongm provided a few posts up?


---- Andy

There is a great need for a sarcasm font.

RE: Weird Math Error in VBA for Excel

(OP)
Thanks to everyone above who gave me a hand! The code you shared with me - I got to work, but as was pointed out accuracy gets lost due to poor float handling. I tried
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

Er, my second example shouldn't have any discrepancy

RE: Weird Math Error in VBA for Excel

(OP)
Hi Strongm, your code example works great as you described, what I found though
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

>the built in digit ceiling of Excel / VBA

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

(OP)
Strongm, what would you recommend to get the best possible accuracy?

RE: Weird Math Error in VBA for Excel

Quote (strongm)


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.

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

CODE

>>> from decimal import *
>>> result = Decimal('0.1') + Decimal('0.1') + Decimal('0.1') - Decimal('0.3')
>>> result
Decimal('0.0')
>>> print result
0.0 

RE: Weird Math Error in VBA for Excel

(OP)
Mikrom, thanks so much! <3

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

RE: Weird Math Error in VBA for Excel

@astrogirl77: The second line computes result = 0.1 + 0.1 + 0.1 - 0.3 using Decimal objects.
Look at the doc about decimal module for python 2.x or 3.x

RE: Weird Math Error in VBA for Excel

(OP)
Got it! Thank you! Will read the docs, thank you also for the link

<3

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close