Error with a boolean operation

[Bob]

Hi everyone:

In excel VBA, I have the following code:

Public Function MY_tester(z, w)
On Error GoTo ErrHandler

If Not IsNumeric(z) Then
MY_tester = "Error: Z must be a number"
ElseIf Not IsNumeric(w) Then
MY_tester = "Error: w must be a number"
ElseIf ((z + w) < 0) And ((z + w) = Fix(z + w)) Then
MY_tester = "Error: z+w cannot be zero nor negative integers"
Else
MY_tester = "Answer OK"
End If

ErrHandler:
If Err Then
MY_tester = "Error: " & Err.Description
End If
End Function

Public Sub MY_SUB()
MsgBox MY_tester(-4.4, 3.4)
End Sub

When I run the sub MY_SUB, the program returns false for ((z + w) = Fix(z +
w)) , even though it is true (after all, -1 is equal to -1). Does anyone
know why? I think this may be a bug in VBA. I appreciate all your help.

Bob

 

[Norman Jones]

Hi Bob,

From the VBE,

?(-4.4+3.4)=>-1.000000000000001
True


?(-4.4+3.4)=>-1.0000000000000001
False

See Chip Pearson at:

Rounding Errors In Excel
http://www.cpearson.com/excel/rounding.htm

 

[Dave Peterson]

Try adding:

MsgBox (z + w) - Fix(z + w)

And you'll see that this difference is very small, but not 0.
-4.44089209850063E-16

It's the way that a binary system handles decimals.

You could check to see if that difference is really small in your code:

ElseIf ((z + w) < 0) And Abs((z + w) - Fix(z + w)) < 0.0000000001 Then

Make that 0.0000000001 as small as you want.

 

[Bob]

Thanks guys for the insight into the problem. Then I guess, there is no way
of knowing if z+w is exactly an integer or not? The reason I need to know
this is that I am evaluating a function that works for all numbers except
exact integers. Even very close to integers will work, but not the integer
itself.

Bob

 

[Rick Rothstein \(MVP - VB\)]

Will your numbers always have 4 decimal places or less? If so, change your
function declaration to this...

Public Function MY_tester(ByVal z, ByVal w)

The ByVal's make sure the original values passed in will not be changed by
what I propose next. Add these two lines immediately after the function
declaration...

z = CCur(z)
w = CCur(w)

Your code should now work. If your values can have more than 4 decimal
places, then this **might** work (well, it will for for you posted example,
but it might not work depending on the size of your numbers and whether you
use mathematical operations, such as Sqr, Sin, etc., on those values)...

z = CDec(z)
w = CDec(w)

Rick

 

[Bob]

Thanks Rick. No the numbers can be anything. So, currency or decimal may
not work. I will however test the Byval input. Thanks.

Bob

 

[Rick Rothstein \(MVP - VB\)]

If any of your numbers have more than 4 decimal places, then Currency won't
work for you... plain and simple. The Decimal data type is a completely
different animal though, so don't write it off too quickly. It will handle
numbers having 28 digits plus a decimal point; and the decimal point can be
located anywhere within the 28 digits. I seriously doubt that you will have
any numbers that can challenge those limits.

The big problem with the Decimal data type is most math functions (Sqr, Sin,
Ln, etc.) in VB only work up to the limits of a Double... if you feed a
Decimal data type value into such a function, it will be converted to a
Double automatically. There are two concerns with this... one, if there are
more than 15 digits in your number, they will be rounded to 15 digits, so
you may lose some accuracy in the process; and, two, you would have to
remember to use CDec to convert the calculated values back from Doubles into
Decimal data type values (you won't retrieve any extra accuracy doing so,
but it may be needed to preserve future calculations with other Decimal data
type value where math functions aren't being used (such as simple addition,
subtraction, multiplication and division).

As I said, I serious doubt your numbers will contain so many digits to make
this a problem for you, so you should give strong consideration to using
them. As for you for statement that you will "test the ByVal input"... the
only reason using ByVal would be necessary is if you change the value of
inputted arguments, which are nothing more than variables within the
procedure) and store those results back in the argument (such as I did in my
example); the reason being that, without the ByVal, the values passed into
the procedure (subroutine of function) back in the calling program will also
be changed. If you use ByVal, a copy of the passed in values it used in the
procedure and you can change those as much as you want without affecting the
original values.

Rick

 

[Bob]

Thanks Rick for your detail reply. I guess I have to make a huge decision
here. You see, I am solving a partial differential equation, and the
solution involves factorials, the gamma function and math operations along
with sine, exponential and other built in functions. I guess, I can use an
if statement and if the data fits in the range of the decimal to use
decimal, otherwise just use double. As you said the only problem is that
the built in functions all use double.

Someone in this group gave me a link to a site that does calculations up to
200 significant digits, and that works great. The only problem is that it
is very slow. For example when I compare the LOG function in VB for a huge
loop, it takes less than 1 second, where as with this other program it takes
close to 120 seconds. A huge difference. OF course this is understandable
why.

I wish, VB would start offering 128 bit compilers, like JAVA and Fortran.
May be since you are an MVP, you can make this suggestion to Microsoft on
behalf of the VB group. Thanks for all your help.

Bob

 

[Rick Rothstein \(MVP - VB\)]

You will probably find VB somewhat limiting for what you are doing. For
example, the largest Factorial you can calculate using Doubles is 17! before
the number of significant digits becomes large enough to for power of 10
notation... once that happens, your calculations are taking place with
approximated numbers. If it helps any, here is a previous posting of mine
which offers a Factorial function with a larger output, but there is a
proviso in how you can use it...

Here is a previous posting of mine that demonstrates what to watch out
for...

You could cast (you can't Dim) a Variant variable as a Decimal type (96-bit
number) and get some 28 or 29 digits of accuracy depending if there is a
decimal in the answer or not. Simply Dim a variable as Variant and CDec a
number (any number will do) into it to make it the Decimal type. Thereafter,
that variable will track 28/29 digits of accuracy. For example the following
function will calculate factorials up to 29 digits of display before
reverting to exponential display.

Function BigFactorial(ByVal N As Integer) As Variant
If N < 28 Then
BigFactorial = CDec(1)
Else
BigFactorial = CDbl(1)
End If
For x = 1 To N
BigFactorial = x * BigFactorial
Next
End Function

However, you have to watch out for overflows with Decimal data types -- once
over 28/29 characters, they will produce an overflow error. So, if you tried
to use the above function like this

Debug.Print 10*BigFactorial(27)

you would get an overflow error but

Debug.Print 10*BigFactorial(28)

would work fine (the difference being in the first case BigFactorial has a
Decimal subtype and in the second case the subtype is a Double).

More generally, if a Variant variable is assigned a value that was cast to
Decimal, any calculation involving that variable will be "performed" as a
Decimal; and then the result cast back to the variable receiving it. If the
result is assigned back to the variable that was originally cast to Decimal,
that variable continues to contain a Decimal type value. For example,

X = CDec(135.6)
X = X - 135
X = X / 7
Print X ==> 0.0857142857142857142857142857

You have to be careful with this though . . . all VB functions return
non-Decimal data.and assigning *that* back to the Variant that was cast as
Decimal "collapses" it back to a less robust data type. For example,
continuing the code above

X = Sqr(X)
Print X ==> 0.29277002188456


Rick