Couple of things...

First, your IsMissing() will never be true, since IsMissing only
operates on Variants, and length is defined as Single. Therefore any
call without length specified will return zero.

you don't really save anything by using Bytes rather than Longs or
Doubles. XL's Math operations are done using Longs or Doubles, so the
function is less efficient due to implicit coercion. True it will take a
few more bytes, but only a few. And the value returned to XL will be a
double, since that's how XL stores numbers.

As far as your error goes, using Bytes casts the result of your
calculation as a byte, so you'll get an overflow error if your height
times width exceed 256. This is indicated in the UDF return as #VALUE!


I can't reproduce your error, but I'd do it this way:

Public Function Volume2(height As Double, _
width As Double, Optional length As Double = -1) As Double
If length < 0 Then length = width
Volume2 = length * width * height
End Function




In article ,
Herman wrote:

Hi,

I have the following function:

Function volume(height As Byte, width As Byte, Optional length As Single)
If IsMissing(length) Then
volume = height * width
Else
volume = height * width * length
End If
End Function

If I type in a number which is greater than 1 in the width argument, I get
the #value error. I'm not sure why because I set width as byte. Is there
something wrong with my code that I can't see? This is a sample that I'm
playing with but it's driving me crazy. Any help will be greatly appreciated.

Thanks in advance.

Herman