Module Question

[Guest]

I have a module that was created for me. I have used it with no problem
until now. The whole thing looks like this:

Function GetQty(PackagingString As String) As Long
Dim lngOut As Long
lngOut = 1
Dim intLen As Integer
Dim intChar As Integer 'which character to examine
Dim intNum As Integer 'found number in string
intLen = Len(PackagingString)
For intChar = 1 To intLen
If IsNumeric(Mid(PackagingString, intChar, 1)) Then
'get the value of the found number
intNum = Val(Mid(PackagingString, intChar))
'multiply the values
lngOut = lngOut * intNum
'skip characters to a non-numeric
intChar = intChar + Len(Trim(Str(intNum)))
End If
Next
GetQty = lngOut
End Function

Now I am getting a Debug on this:

intNum = Val(Mid(PackagingString, intChar))

Some of the packaging strings have descriptions in them like this:

CS/25CT/20EA SINGLE SWABSTICK

Most of them look like this:

BX/12EA
CS/2BX/24PK/8EA
CS/30BG/5EA

If I hit cancel on the Debug then I get:

Run-time error '6'

Overflow

Any help would be appreciated. Also, if you could explain why this is
happening that would be great as well.

Thomas

 

[Baz]

Wha is the value of "PackagingString" at the point where the error occurs?

 

[Jason Lepack]

You need to find which PackagingString it is erroring out on. intNum
is reading in a value that is greater than 32767 or less than -32768.
You could solve this by changing intNum to a Long as long as you could
guarantee that the value would not overflow a Long.

Cheers,
Jason Lepack

 

[Guest]

Not sure what you mean?

I am getting a debug message here:

intNum = Val(Mid(PackagingString, intChar))

So I am assuming this is where it is getting the numbers? and deleting the
characters.

 

[Guest]

Jason,

Thanks, is the Long as in Long Integer?

 

[Alex Dybenko]

Hi,
here is a wrong part:

If IsNumeric(Mid(PackagingString, intChar, 1)) Then
'get the value of the found number
intNum = Val(Mid(PackagingString, intChar))

you check for isnumeric for one char, but trying to convert to number the
rest of the sting.

perhaps it should be:

intNum = Val(Mid(PackagingString, intChar,1))


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Jason Lepack]

Thanks, is the Long as in Long Integer?
Yup.

I still stand by this and think that you should determine that your
data is actually supposed to be exceeding these limits, because 33000
units for a specific line seems like a lot.

Cheers,
Jason Lepack

 

[Jason Lepack]

No, he didn't write the code, but the code works the way it is
supposed to according to the description.

 

[Guest]

Thank you sir.
--
Thomas

Hi,
here is a wrong part:

If IsNumeric(Mid(PackagingString, intChar, 1)) Then
'get the value of the found number
intNum = Val(Mid(PackagingString, intChar))

you check for isnumeric for one char, but trying to convert to number the
rest of the sting.

perhaps it should be:

intNum = Val(Mid(PackagingString, intChar,1))


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Baz]

I thought that code was wrong too, but I tried it out and it's OK. If you
use Val on a string that begins with numeric characters, it returns just the
value of the numeric characters up to the first non-numeric character. In
other words, the code, though strange-looking, is actually quite clever.

Basically he's got a bad PackagingString value, and he needs to find out
what it is.

 

[Baz]

Well I don't know what "PackagingString" represents or where it came from,
if you don't know either then you really are in trouble.

Basically, you've got a "PackagingString" which contains a number that's too
big for the integer data type i.e. it's bigger than 32,767. To find out
what that PackagingString value is, when the code breaks, hover your mouse
pointer over the text "PackagingString" in the highlighted statement.

Presumably the offending PackagingString is bad data and you need to fix it,
although knowing nothing about your application I couldn't begin to tell you
how. If, however, the PackagingString is valid and you need to handle
numbers of this size, then you need to change intNum to be a Long variable
instead of an Integer.

 

[Tom Lake]

I have a module that was created for me. I have used it with no problem
until now. The whole thing looks like this:

Is it possible the string has a D in it?

Val("1234D9823ZCW") would overflow since the D is taken as a double
precision
exponent indicator. 12234 D+9823 would be a very big number indeed.

Tom Lake