Inexplicable problem with function

[Pete Jones]

Hi everyone,

I've created a function to calculate the volume of tanks we produce. I've
limited the first imputs to bytes but I don't want to limit the last imput.

However when the first two inputs, height and width, multiplied toghether
are more than a byte I get an error. I don't understand why since
individually the inputs are less then byte size.

For eg the following produces an error Volume( 200, 100,500)


Below is the function
Function Volume(height As Byte, width As Byte, depth)

Volume = height * width * depth

End Function

Any help greatly appreciated

Thanks

Peter

[Charlie]

Why limit yourself to such small values? In Engineering apps we prefer to
use Double Precision:

Function Volume(height As Double, width As Double, depth As Double) As Double

Volume = height * width * depth

End Function

But if you must use Byte inputs for some specific reason, try this

Function Volume(height As Byte, width As Byte, depth As Double) As Double

Volume = CDbl(height) * CDbl(width) * depth

End Function


"Pete Jones" wrote:

> Hi everyone,
>
> I've created a function to calculate the volume of tanks we produce. I've
> limited the first imputs to bytes but I don't want to limit the last imput.
>
> However when the first two inputs, height and width, multiplied toghether
> are more than a byte I get an error. I don't understand why since
> individually the inputs are less then byte size.
>
> For eg the following produces an error Volume( 200, 100,500)
>
>
> Below is the function
> Function Volume(height As Byte, width As Byte, depth)
>
> Volume = height * width * depth
>
> End Function
>
> Any help greatly appreciated
>
> Thanks
>
> Peter

[Pete Jones]

Thanks Charlie

This approach fixes the problem.


"Charlie" wrote:

> Why limit yourself to such small values? In Engineering apps we prefer to
> use Double Precision:
>
> Function Volume(height As Double, width As Double, depth As Double) As Double
>
> Volume = height * width * depth
>
> End Function
>
> But if you must use Byte inputs for some specific reason, try this
>
> Function Volume(height As Byte, width As Byte, depth As Double) As Double
>
> Volume = CDbl(height) * CDbl(width) * depth
>
> End Function
>
>
> "Pete Jones" wrote:
>
> > Hi everyone,
> >
> > I've created a function to calculate the volume of tanks we produce. I've
> > limited the first imputs to bytes but I don't want to limit the last imput.
> >
> > However when the first two inputs, height and width, multiplied toghether
> > are more than a byte I get an error. I don't understand why since
> > individually the inputs are less then byte size.
> >
> > For eg the following produces an error Volume( 200, 100,500)
> >
> >
> > Below is the function
> > Function Volume(height As Byte, width As Byte, depth)
> >
> > Volume = height * width * depth
> >
> > End Function
> >
> > Any help greatly appreciated
> >
> > Thanks
> >
> > Peter

[NickHK]

Pete,
The reason this is occurs is that VBA performs the calculation using the
smallest data type of the inputs.
In order to calulate Volume, the first calculation is (argHeight *
argWidth), both of which are Byte. Hence, VB tries to put the result in a
Byte, which may not be possible, therefore Overflow.
So you need at least one variable that is large enough to hold the largest
expected value. Also specify the data type of "depth" and the return data
type to your function:
Public Function Volume(argHeight As Byte, argWidth As Byte, argDepth As
Byte) As Long
Volume = CLng(argHeight) * argWidth * argDepth
End Function

You will also see that:
Volume = argHeight * argWidth * CLng(argDepth)
can result in an overflow, as multiplication of the first 2 arguments can
result in the intermediate (tempoarary) value exceeding the range of a byte.
Conversely:
Volume = argHeight * CLng(argWidth) * argDepth
does not overflow.

Alternatively:
Public Function Volume(argHeight As Long, argWidth As Byte, argDepth As
Byte) As Long
Volume = argHeight * argWidth * argDepth
End Function

NickHK

As a sideline, I avoid using variable names of "height", "width" etc that
are used by VBA/Excel etc. Although it seems OK in this situation, you may
find times where the interpretation of such variables is not what you
expect. You can call it "argHeight", bytHeight, "TankHeight" etc to keep the
spelling different and its meaning (to VBA) unambiguous.


"Pete Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
>
> I've created a function to calculate the volume of tanks we produce. I've
> limited the first imputs to bytes but I don't want to limit the last
imput.
>
> However when the first two inputs, height and width, multiplied toghether
> are more than a byte I get an error. I don't understand why since
> individually the inputs are less then byte size.
>
> For eg the following produces an error Volume( 200, 100,500)
>
>
> Below is the function
> Function Volume(height As Byte, width As Byte, depth)
>
> Volume = height * width * depth
>
> End Function
>
> Any help greatly appreciated
>
> Thanks
>
> Peter