[RESOLVED] Weird Math Error in VBA for Excel

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

'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

---

You mention an error, but I'm assuming you mean unexpected results rather than an error message.

Based on your textual description of the intended order of events, and your example formulas that you report are working properly, the error appears to be an order of operations error in your vba code. In your code, "SQRD Mod 400" takes place before "/ decx".

So, this should do things in the order you describe.

Code:

---

For Count = 1 To looptime
    SQRD = CDec(SQRD ^ 2)  '+ looptime
    SQRD = CDec(SQRD / decx)  Mod 400
Next Count

---

Hi thanks for the feedback - I get what you mean, that is helpful and I appreciate that but it does not fix the problem.

When the function runs with input value of "404.0404"
for 100 iterations, it returns the incorrect value of 1
where as the sheet column formula returns the correct
value of "72.18545607"

The UDF VBA function is not retaining any values past the decimal position
nor is it properly calculating the end result.

I suspect this is related to data type and how excel is handling the calcs in
VBA, but I cant figure out how to fix this as I have tried all known relevant data types.

Its easy to see what I mean by simply entering any value for input thats
a rational number and for any loops higher than 10, the spreadsheet using basic repeat and copy columnwise statements of what I mentioned above ;

"The first formula placed at (E2) looks like ; =MOD(((B2^2)),$B$4)
In (E3) this statement is placed ; =MOD(((E2^2)),$B$4)"

Then comparing the 100th cell to the 100th iteration of the function demonstrates the discrepancy, I just want the function to return the same value and accuracy that the far more cumbersome sheet / column based
approach produces

Quote:

---

Originally Posted by OptionBase1

You mention an error, but I'm assuming you mean unexpected results rather than an error message.

Based on your textual description of the intended order of events, and your example formulas that you report are working properly, the error appears to be an order of operations error in your vba code. In your code, "SQRD Mod 400" takes place before "/ decx".

So, this should do things in the order you describe.

Code:

---

For Count = 1 To looptime
    SQRD = CDec(SQRD ^ 2)  '+ looptime
    SQRD = CDec(SQRD / decx)  Mod 400
Next Count

---

---

Looks like VBA's MOD command always returns an integer value, unlike the Excel MOD worksheet function. You may need to write your own custom Mod function in VBA if you want a result that includes a decimal.

Code:

---

Private Sub Command1_Click()

  MsgBox ModD(3.1, 2.6)
 
End Sub

Private Function ModD(number As Double, divisor As Double) As Double

  ModD = number - (number \ divisor) * divisor

End Function

---

Something like this should work.

Thank you for your patience! This works perfectly now, you pointed out the
most important missing piece, totally helps me out - you rock!

:) <3

Quote:

---

Originally Posted by OptionBase1

Code:

---

Private Sub Command1_Click()

  MsgBox ModD(3.1, 2.6)
 
End Sub

Private Function ModD(number As Double, divisor As Double) As Double

  ModD = number - (number \ divisor) * divisor

End Function

---

Something like this should work.

---

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?

As far as I know there are no issues with using powers that are non-integers. Without seeing your code I would guess it is an order of operations issue. Can you post your current code? And the Excel formula that is working correctly?

OptionBase1, you are a God send, thanks for following up with me

I have included the current code as requested below. In the Excel sheet using only formulas no code
I use the following, this works, I can replace ^2 with any rational number like ; 1.99, 1.999, 1.9999,
2.99, 3.999, etc I copy this down to up to 100 cells in a column, for the divisor value I can enter similiar
rationals and no problem, but this isnt the case with the code - if the iteration is set to say 100 loops
after about the 5th iteration sometimes sooner accuracy is lost badly and there is no correspondence between
what the sheet column cells report vs what the code loop returns. This is the sheet formula below,
except I replace the ^2 with other values mentioned like 1.9999

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)

Here is the code that returns wrong values ;

Code:

---

Public Function func(inputx As Double, loopx As Double, decx As Double) As Double

Application.Volatile

Dim Count As Integer

func = inputx

Count = 1

For Count = 1 To loopx
    func = (func ^ 1.9999) 
    func = xlMOD(func, 400)
    func = xlMOD(func, 50) / decx
Next Count
 
End Function

Public Function xlMOD(a As Double, b As Double) As Double
    xlMOD = a - (b * (a \ b))
End Function

---

The code doesn't seem to match the Excel formula. In the code, you are doing two separate mods, in the Excel formula, you are doing only one mod.

Setting that aside, as I guessed, there is an order of operations issue.

With the Excel formula, this is the order things are happening:

1. The value in B2 is squared
2. That result is divided by the value in B3
3. That result is then taken MOD of the value in B4

In the VBA code you posted, the MOD is happening at step 2, and step 3 is the division. That is why you are seeing different results. The power being an integer or not is not the issue based on what you have posted.

Hi Sorry, had multiple copies of code I was testing and pasted the wrong copy, the correct one is shown above.
The thing is the code as is will work IF only integers are used and it will match the spreadsheet based mod formulas,
divergence to error only occurs if I use rationals for the power value and or divisor value, that is the only time it
fails

I created a spreadsheet and am using 1.98 in B2, 1.5 in B3, and 100 in B4, and am using an exponent of 1.99

I am able to reproduce the behavior you are reporting. The Excel formula produces these 20 values:

2.595807420328500
4.449497379974090
13.003119151781300
9.866009309854820
63.423523789175500
72.687121452178400
74.497469982927700
43.809544230660300
32.056214636229500
61.719744189439400
36.984318008911900
79.556604917842500
38.819655490954100
68.548957330533900
2.966165507125420
5.801997638533780
22.050988404413300
14.290013839953500
32.563424077512300
82.718341577604900



The VBA code produces these values:

2.5958074203285
4.44949737997409
13.0031191517813
9.86600930985504
63.4235237891784
72.6871214524167
74.4974700049461
43.8095463149966
32.0563312856718
61.7245359861114
37.360845564674
97.4659235173255
49.5882617565358
76.5680536262732
42.5143966057899
60.6343507675515
52.4425016539521
62.295401903807
82.4251649328703
33.7892939226695

Going to try a few things, back in a bit.

Hi Sorry, had multiple copies of code I was testing and pasted the wrong copy, the correct one is shown above.
The thing is the code as is will work IF only integers are used and it will match the spreadsheet based mod formulas,
divergence to error only occurs if I use rationals for the power value and or divisor value, that is the only time it
fails

Thank you option1!

I took a graphing calculator and wrote a program that goes through the same calculations and used the same starting values, and it also started to stray.

My conclusion is this. There isn't infinite precision in these types of calculations. Taking float values to a float power is going to result in a truncation at some decimal point. Where that truncation takes place must differ between Excel's own internal formula calculation routines and in VBA's math functions, and those differ from where a TI-86 graphing calculator truncates values.

In my testing I changed variable types from Double to Variant and I got the exact same results as when the variables were Doubles, so that was a dead end.

Because of the nature of the calculations you are performing, it seems clear that once a small difference occurs between the different methods of calculation, the size of that difference escalates very quickly until the values are drastically different.

And I would imagine that all of these methods produce very wrong numbers at a certain point compared to a "perfect" calculator with infinite precision.

Hope that helps.

Thanks again OptionBase1 - I hope to understand you correctly, one thing that strikes me as odd and must have some
kind of solution tho, is that as far the loop is concerned it only performs one calc at a time, so does this mean then
that if we raise a rational number to a small rational power and take its mod we cant actually get the correct answer?

So if we have a base value of three digits, with say two to four values after the decimal and we raise it by the power
of another small rational number like 1.9999 we can;t actually get the correct value? Wow, if thats the case
a lot of scientists needing this precision out there must be incredibly frustrated with Excel VBA?

Thanks again OptionBase1 - I hope to understand you correctly, one thing that strikes me as odd and must have some
kind of solution tho, is that as far the loop is concerned it only performs one calc at a time, so does this mean then
that if we raise a rational number to a small rational power and take its mod we cant actually get the correct answer?

So if we have a base value of three digits, with say two to four values after the decimal and we raise it by the power
of another small rational number like 1.9999 we can;t actually get the correct value? Wow, if thats the case
a lot of scientists needing this precision out there must be incredibly frustrated with Excel VBA?

I would say it is unlikely that someone who needs near exact precision for iterated calculations involving floats to a float power are using Excel as their calculation tool.

What I'm saying is this: Enter this calculation in Excel:

8.96^1.999

Formatted to 20 decimal places, it will give you this: 80.10575376340050000000

On my TI-86 calculator, it is 80.1057537634, which is less precise than Excel.

Now, enter this same calculation into the Windows calculator and you get 80.105753763400460283817305086727

Windows Calculator seems to be much more precise than Excel. Does the difference really matter? It depends on what you are calculating. But as is demonstrated with Excel Formula's vs. VBA vs. a TI-86 calculator, depending on what you are calculating, a minor difference at one step can turn in to a drastic difference within a few more iterated calculation steps.

The correct answer to these calculations is an irrational number, so nothing will ever give the correct value since the correct value is a non-repeating, infinitely long decimal value.

If you need calculations with better precision, you should check out Wolfram's Mathematica language. As an example, here is 8.96^1.999 to 1000 decimal places as calculated by Wolfram Alpha:

https://www.wolframalpha.com/input/?...99+1000+digits

Hi OptionBase1, I think you are right here. I just wrote a Python script to do this, I sheet out of VB, call the .py script
have it store generated values to an array, write the values to a text file, then have VB open the .txt file, import and place
to a column and the values Python creates do match what the Excel sheet formulas return, but when I use Python Im importing
numpy (math sci precision module) and use floats - then the values match what shows in the sheet. The problem tho is
to do all this takes too long, open writing to file, then importing readind from file. So frustrating! I cant imagine why Microsoft
would provide accuracy in the sheet with simple formulas but then cripple a VBA approach so it cant perform as well...
Unbelievable! If I go with Mathematica, I have to leave VB, VBA - very challenging

Hi OptionBase1, I think you are right here. I just wrote a Python script to do this, I sheet out of VB, call the .py script
have it store generated values to an array, write the values to a text file, then have VB open the .txt file, import and place
to a column and the values Python creates do match what the Excel sheet formulas return, but when I use Python Im importing
numpy (math sci precision module) and use floats - then the values match what shows in the sheet. The problem tho is
to do all this takes too long, open writing to file, then importing readind from file. So frustrating! I cant imagine why Microsoft
would provide accuracy in the sheet with simple formulas but then cripple a VBA approach so it cant perform as well...
Unbelievable! If I go with Mathematica, I have to leave VB, VBA - very challenging

Just found this, what do you think?

https://www.extremeoptimization.com/...t-Numbers.aspx

"Constructing big floating-point numbers
The BigFloat structure has several constructors that construct a floating-point number with the same value as the argument. You can start from 32 and 64 bit integers, single or double-precision numbers, BigInteger values and BigRational values.

Most rational numbers cannot be expressed exactly as a floating-point number. For this reason, a second constructor is provided that takes two additional arguments: a AccuracyGoal value that specifies the desired accuracy of the approximation, and a RoundingMode value that specifies how to round the final approximation."

OptionBase1, you are a God send, thanks for following up with me

I have included the current code as requested below. In the Excel sheet using only formulas no code
I use the following, this works, I can replace ^2 with any rational number like ; 1.99, 1.999, 1.9999,
2.99, 3.999, etc I copy this down to up to 100 cells in a column, for the divisor value I can enter similiar
rationals and no problem, but this isnt the case with the code - if the iteration is set to say 100 loops
after about the 5th iteration sometimes sooner accuracy is lost badly and there is no correspondence between
what the sheet column cells report vs what the code loop returns. This is the sheet formula below,
except I replace the ^2 with other values mentioned like 1.9999

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)

Here is the code that returns wrong values ;

Code:

---

Public Function Phaser(phase As Double, radius As Double, decx As Double) As Double

'SYNTAX ; Phaser(phase,radius,divisor)

'Public Function Phaser(phase As Integer, radius As Integer, decx As Double) As Double

Application.Volatile

Dim Count As Integer

Phaser = phase

'Dim decx As Double

Count = 1

For Count = 1 To radius
    Phaser = (Phaser ^ 3)  '+ radius
    Phaser = xlMOD(Phaser, 1449)
    Phaser = xlMOD(Phaser, 1156) / decx
Next Count
 
End Function

Public Function xlMOD(a As Double, b As Double) As Double
    xlMOD = a - (b * (a \ b))
End Function

---

I would say you are overstating the issue. VBA's calculation isn't "crippled". It is slightly less precise, perhaps by 1 or 2 decimal places. In my values I posted earlier, the first three calculations were identical between the Excel formula and VBA. Unfortunately, the iterated algorithm you are using exacerbates the issue quickly with certain values once a difference starts to materialize. Given a different algorithm, the difference between Excel formula and VBA calculations might be essentially unnoticeable.

Here are some tests I just did with Excel and VBA:

10.5265464 ^ 1.45644

Excel Formula: 30.8246454160737000
Excel VBA: 30.8246454160737

Same answer. But then:

100 * 10.5265464 ^ 1.45644 - 3082

Excel Formula: 0.4645416073658450
Excel VBA: 0.464541607366115
Wolfram Alpha: 0.464541607365982162827330058630099816106459645614600902978

VBA differs from Excel's formula, but both differ from Wolfram Alpha. Excel is "closer" to Wolfram Alpha by a tiny margin, but both are inaccurate at some point.

Good luck with your project.

Edit: Holy cow, I should have noted earlier, I'm doing all this testing using Excel 2003 using Variant variables. It is possible that newer versions of Excel/VBA have variable types that allow for greater precision.

Edit2: And at some point, I did test using CDec to convert values at various steps to the Decimal data type, and those answers still differed from the Excel formula results.

Regarding the link you sent, I haven't done anything with custom/3rd party numeric data types, but in general, anything you can use that will give you additional decimal places of precision in the calculations will be better.

Just found this, what do you think?

https://www.extremeoptimization.com/...t-Numbers.aspx

"Constructing big floating-point numbers
The BigFloat structure has several constructors that construct a floating-point number with the same value as the argument. You can start from 32 and 64 bit integers, single or double-precision numbers, BigInteger values and BigRational values.

Most rational numbers cannot be expressed exactly as a floating-point number. For this reason, a second constructor is provided that takes two additional arguments: a AccuracyGoal value that specifies the desired accuracy of the approximation, and a RoundingMode value that specifies how to round the final approximation."

This xlMOD implementation is totally flawed. The way a \ b works in VB6/VBA is by *first* casting a and b to integers, then doing an integer division. What you need is Int(a / b) i.e. floating point division with result *floored* to an integer.

Yet, even with this fix you get strange results out of rounding errors e.g. xlMOD(3.9, 1.3) gives 1.3 while original excel MOD returns a value just above 0.0

cheers,
</wqw>

Yes, now that you say that about the \ operator I should have realized that, but in all honesty, I've never tried using it for non-integers before until this thread. Passing 3.7 and 1.4 to my function returns -1.9, LOL. Shows how much testing I did.

The 3.9, 1.3 results are interesting. I know there are known float values that can produce unexpected results in certain calculations.

Thanks for setting me straight.

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!

Quote:

---

Originally Posted by OptionBase1

Yes, now that you say that about the \ operator I should have realized that, but in all honesty, I've never tried using it for non-integers before until this thread. Passing 3.7 and 1.4 to my function returns -1.9, LOL. Shows how much testing I did.

The 3.9, 1.3 results are interesting. I know there are known float values that can produce unexpected results in certain calculations.

Thanks for setting me straight.

---