Strange error on function

[Guest]

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

 

[Tom Ogilvy]

a byte is either 0 or 1. So if you enter anything else, you would have an
invalid argument. Unless a 0 or 1 is all you want, then you should use
something else (integer, long, single, double).

 

[Niek Otten]

Hi Herman,

Probably one of your arguments is text or another invalid data type.
BTW, Optional arguments should not have a type declaration; if you omit the
argument, it will be treated as zero, not as Missing.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Niek Otten]

Hi Herman,

Probably one of your arguments is text or another invalid data type.
BTW, Optional arguments should not have a type declaration; if you omit the
argument, it will be treated as zero, not as Missing.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[JE McGimpsey]

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

 

[Guest]

Function volume(height As Integer, width As Integer, Optional Length As
Variant)

Optional argument is declared as variant.

 

[Niek Otten]

1 or 0 seems to me like a bit. I think any combination which would result in
something >255 will give #VALUE

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Tom Ogilvy]

Oops,
Ignore that, I was thinking of Bit.

anyway, I think you are overflowing your variables, so this worked for me
(up to arguments of magnitude 255)

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

 

[Guest]

Just a technicality, Volume has to have all three - Height,Width and Length.
There is no option whatsoever!!
Alok Joshi