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

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




---
Regards,
Norman


"Bob" wrote in message
...
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

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

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

--

Dave Peterson

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

"Bob" wrote in message
...
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

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" wrote in message
...
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

"Bob" wrote in message
...
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

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)" wrote in
message ...
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" wrote in message
...
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

"Bob" wrote in message
...
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

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" wrote in message
...
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)" wrote in
message ...
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" wrote in message
...
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

"Bob" wrote in message
...
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

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)" wrote in
message ...
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" wrote in message
...
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)" wrote
in message ...
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" wrote in message
...
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

"Bob" wrote in message
...
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