Weird Math Error in VBA for Excel 4

[astrogirl77]

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 ;

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

 

[strongm]

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

What this

[tt]SQRD Mod 400 / decx[/tt]

is actually doing is:

[tt]SQRD Mod (400 / decx)[/tt]

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

[tt](SQRD Mod 400) / decx[/tt]

(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:

[tt](SQRD Mod 400) / decx[/tt]

actually becomes

[tt](SQRD / decx) MOD 400[/tt]

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:

[blue]Function vbMod(number As Variant, divisor As Variant) As Variant
    vbMod = number - divisor * Int(number / divisor)
End Function[/blue]

And thus your original code actually becomes:

[blue]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) [COLOR=green]' this should look familiar as it mimics the sheet MOD function[/color]
Next Count
  
End Function[/blue]

 

[combo]

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

 

[strongm]

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

 

[combo]

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

 

[astrogirl77]

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

You guys rock!!! Such a day maker!



<3

 

[astrogirl77]

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

 

[strongm]

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):

[blue]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
        [b]SQRD = WorksheetFunction.Power(SQRD, Power)[/b] [COLOR=green]' + looptime[/color][COLOR=green][/color]
        SQRD = vbMod(SQRD / decx, ModVal) [COLOR=green]' this should look familiar as it mimics the sheet MOD function[/color]
    Next Count
  
End Function[/blue]

 

[astrogirl77]

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 ;

SQRD = vbMod(SQRD / decx, ModVal)

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

 

[Andrzejek]

Do you have this:

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.

 

[astrogirl77]

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!

 

[strongm]

Er, my second example shouldn't have any discrepancy

 

[astrogirl77]

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!

 

[strongm]

>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.

 

[astrogirl77]

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

 

[mikrom]

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

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

 

[astrogirl77]

Mikrom, thanks so much! <3

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

 

[mikrom]

@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

 

[astrogirl77]

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

<3