Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable problem with function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable problem with function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable problem with function
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable problem with function
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INEXPLICABLE FORMULA RESULT | Excel Discussion (Misc queries) | |||
File sized in inexplicable way | Excel Programming | |||
File sized in inexplicable way | Excel Programming | |||
Nested IF AND OR function Inexplicable error | Excel Worksheet Functions | |||
Inexplicable crash in VBA | Excel Programming |