Error Handling Question

[Guest]

How would you handle the errors in this situation (I swear my book on VBA has
been ordered and is on its way!)... I have a text array of dimensions that I
am trying to break up into usable data. Always separated by an 'X', mostly
two, but sometimes three dimensions. e.g. : '24x32x36 or 4x4. I've gotten
the split function to work so that I can pick out and assign the numbers when
there are three dimensions, but how would I handle it if there are only three
dimensions?

 

[Stefan Hoffmann]

hi,

e.g. : '24x32x36 or 4x4. I've gotten
the split function to work so that I can pick out and assign the numbers when
there are three dimensions, but how would I handle it if there are only three
dimensions?

Dim Count As Long
Dim Dimensions() As String

Dimensions = Split("4x4", "x")

For Count = LBound(Dimensions()) To UBound(Dimensions())
'Dimensions(Count) gives each value.
Next Count

or

Select Case UBound(Dimensions()) - LBound(Dimensions())
Case Is = 2
Case Is = 3
Case Else
MsgBox "Wrong number of dimensions."
End Select

mfG
--> stefan <--

 

[Douglas J. Steele]

You seem to have a typo in there, so I'm not really sure which case you're
handling, and which case you aren't...

Dim strDimensions As String
Dim varDimensions As Variant

strDimensions = "24x32x36"
varDimensions = Split(strDimensions, "x")

at this point, UBound(varDimensions) will be 2, indicating that you've got 3
values stored in the array. varDimensions(0) will be 24, varDimensions(1)
will be 32 and varDimensions(2) will be 36.

Similarly,

strDimensions = "4x4"
varDimensions = Split(strDimensions, "x")

will result in UBound(varDimensions) will be 1 (indicating that you've got 2
values stored in the array), and varDimensions(0) and varDimensions(1) will
be both equal 4.

 

[Guest]

And here I was, getting all sad because I thought no one had responded...
Apparently I forgot to click the 'Notify me of responses' button. Anyhow,
yes, you are correct. I have a typo. I meant to say that the code for three
dimensions works fine, but that anything less produces an error. I'll see if
I can follow your instructions and come up with something that will work.
Thanks, everyone!
--
Why are you asking me? I dont know what Im doing!

Jaybird

You seem to have a typo in there, so I'm not really sure which case you're
handling, and which case you aren't...

Dim strDimensions As String
Dim varDimensions As Variant

strDimensions = "24x32x36"
varDimensions = Split(strDimensions, "x")

at this point, UBound(varDimensions) will be 2, indicating that you've got 3
values stored in the array. varDimensions(0) will be 24, varDimensions(1)
will be 32 and varDimensions(2) will be 36.

Similarly,

strDimensions = "4x4"
varDimensions = Split(strDimensions, "x")

will result in UBound(varDimensions) will be 1 (indicating that you've got 2
values stored in the array), and varDimensions(0) and varDimensions(1) will
be both equal 4.

 

[Guest]

Hi Stefan,

Thanks for posting a response! I'll see if I can follow your logic and
produce something that works.

 

[Guest]

Okay, I think I got it... By my (perhaps dubious) logic, you should be able
to determine the largest dimension with:

Me![field1] = max(varDimensions())

However, max() is not defined... What's wrong?


--
Why are you asking me? I dont know what Im doing!

Jaybird

You seem to have a typo in there, so I'm not really sure which case you're
handling, and which case you aren't...

Dim strDimensions As String
Dim varDimensions As Variant

strDimensions = "24x32x36"
varDimensions = Split(strDimensions, "x")

at this point, UBound(varDimensions) will be 2, indicating that you've got 3
values stored in the array. varDimensions(0) will be 24, varDimensions(1)
will be 32 and varDimensions(2) will be 36.

Similarly,

strDimensions = "4x4"
varDimensions = Split(strDimensions, "x")

will result in UBound(varDimensions) will be 1 (indicating that you've got 2
values stored in the array), and varDimensions(0) and varDimensions(1) will
be both equal 4.

 

[Stefan Hoffmann]

hi,

Okay, I think I got it... By my (perhaps dubious) logic, you should be able
to determine the largest dimension with:

Me![field1] = max(varDimensions())

The dimension is

UBound() - LBound() + 1

as the returned array is zero-baseed, this makes it

UBound() + 1

I have to admit, that my Select Case example wasn't clear in this point.


mfG
--> stefan <--

 

[Guest]

I'm having trouble following the logic here... It seems to me that
UBound()+1 would simply give the the total number of arrays. How can I use
this to determine the longest dimension?

 

[Stefan Hoffmann]

hi,

I'm having trouble following the logic here... It seems to me that
UBound()+1 would simply give the the total number of arrays. How can I use
this to determine the longest dimension?

You need to loop through your array:

Dim Count As Long
Dim Dimensions() As String
Dim MaxDimension As Long
Dim MaxDimensionValue As Long

Dimensions = Split("4x4", "x")

MaxDimension = -1
MaxDimensionValue = -1 'any value smaller then possible values
For Count = LBound(Dimensions()) To UBound(Dimensions())
If MaxDimensionValue > Dimensions(Count) Then
MaxDimension = Count
MaxDimensionValue = Dimensions(Count)
End If
Next Count


mfG
--> stefan <--

 

[Guest]

Bless you, sir... and thank you! I hate that I can't figure out what's going
on in your function by looking at it, but hopefully it will come to me. I've
never done a looping procedure before.

 

[Guest]

Well, I still can't figure this out... I understand that I need to loop my
array, and that finding the upper and lower boundaries of the 'count' within
my array is a clever way to ensure that I loop the correct number of times
without causing an error, but I'm still not getting the results that I want.
I hate to be one of these posters that requires hand-holding, but until I
master some of these basic skills, it's my only option. So, here goes...
This is the code that Stefan was kind enough to come up with for me, plus my
notes indicating my understanding of it:

Private Sub Form_Current()
Dim Count As Long 'long could be substituted with integer?
Dim Dimensions() As String 'this is a string because the statement
"36X42X36 is an array
Dim MaxDimension As Long 'long could be substituted with integer?
Dim MaxDimensionValue As Long 'long could be substituted with integer?
Dim Length As Integer
Dim dimension1 As Long 'long could be substituted with integer?

Dimensions = Split([Size 1], "X") 'this simply makes the function easier
to manage

MaxDimension = -1
MaxDimensionValue = -1
For Count = LBound(Dimensions()) To UBound(Dimensions()) 'okay, once
you've determined the lower and upper boundaries, the loop starts by assuming
that the array "36X24X36" is null because there can be no "Dimensions(-1),
right?

If MaxDimensionValue < Dimensions(Count) Then 'and this is because
an actual number is greater than null?
Me.Length = Count 'shouldn't this be "Me.Length=Dimensions(Count)?
End If
Next Count 'go on to next part of the array

End Sub

Well, obviously, there are nuances here that I am missing, but if I
understand the basic concept, please let me know, as that is at least a place
to start. I am attempting to define the field 'Length' as the longest
dimension in the array. The array comes from a text field in a table called
[Size 1]. As a check, I've called the field [Size 1] to be displayed as is.
I've also called the dimensions within that field as [dimension1],
[dimension2], and [dimension3] so I can see what I get. Finally, I've called
the field [Length] to be the longest of the three dimensions. The split
function works fine. When there are all three dimensions in the array, the
code works fine, but when there are only two, the last record with a third
dimension 'sticks' and shows up with the other two. Another problem I'm
having is that sometimes there are spaces between the values and sometimes
there aren't: "36X42X36" or "36 X 42 X 36". I've tried using Val() with
some success, but I'm not sure if I am causing myself problems by doing so.
Sometimes, there is also bad data that needs to be noted. For instance,
someone has input text instead of the standard array format. Basically, I
would like to split the array into the appropriate dimensions and when there
isn't one, use a zero. If the data is bad, note it.

Sorry to whine. Thanks for all your help.

 

[Stefan Hoffmann]

hi,

Well, I still can't figure this out... I understand that I need to loop my
array, and that finding the upper and lower boundaries of the 'count' within
my array is a clever way to ensure that I loop the correct number of times
without causing an error, but I'm still not getting the results that I want.

Can you give us an example of an array, which gives you the wrong result?

Private Sub Form_Current()
Dim Count As Long 'long could be substituted with integer? Yes.

Dim Dimensions() As String 'this is a string because the statement
"36X42X36 is an array

No. It is an array of strings, because the input is a string. This array
could also be an array of Long, as long as all values are numbers.

Dim MaxDimension As Long 'long could be substituted with integer?
Dim MaxDimensionValue As Long 'long could be substituted with integer?
Dim Length As Integer
Dim dimension1 As Long 'long could be substituted with integer?

Yup, yup, yup, but no need to.

Dimensions = Split([Size 1], "X") 'this simply makes the function easier
to manage

Is [Size 1] a field?

MaxDimension = -1
MaxDimensionValue = -1
For Count = LBound(Dimensions()) To UBound(Dimensions()) 'okay, once
you've determined the lower and upper boundaries, the loop starts by assuming
that the array "36X24X36" is null because there can be no "Dimensions(-1),
right?

The Split function generates arrays from 0 to UpperBoundary, but an
array in VB(A) can start with any Integer. -3 to +3 is also a valid range.

If MaxDimensionValue < Dimensions(Count) Then 'and this is because
an actual number is greater than null?

Yes. You also found my typo.

Me.Length = Count 'shouldn't this be "Me.Length=Dimensions(Count)?

In my original code, i store the index (MaxDimension) and the value
(MaxDimensionValue).

End If
Next Count 'go on to next part of the array

mfG
--> stefan <--

 

[Guest]

Thank you, Stefan. Yep. My noodling around screwed up the code. I put back
your values for MaxDimension and MaxDimensionValue and it works fine. Thank
you for your help. I would also like to call up values for Dimension1,
Dimension2, and Dimension3 (when it exists) in my form as a double check.
However, looping through the array doesn't allow for simultaneous values of
Dimensions(Count). This is not an essential part of the code, but it is a
"would like to have". Any suggestions?
--
Why are you asking me? I dont know what Im doing!

Jaybird

hi,

Well, I still can't figure this out... I understand that I need to loop my
array, and that finding the upper and lower boundaries of the 'count' within
my array is a clever way to ensure that I loop the correct number of times
without causing an error, but I'm still not getting the results that I want.

Can you give us an example of an array, which gives you the wrong result?

Private Sub Form_Current()
Dim Count As Long 'long could be substituted with integer? Yes.

Dim Dimensions() As String 'this is a string because the statement
"36X42X36 is an array

No. It is an array of strings, because the input is a string. This array
could also be an array of Long, as long as all values are numbers.

Dim MaxDimension As Long 'long could be substituted with integer?
Dim MaxDimensionValue As Long 'long could be substituted with integer?
Dim Length As Integer
Dim dimension1 As Long 'long could be substituted with integer?

Yup, yup, yup, but no need to.

Dimensions = Split([Size 1], "X") 'this simply makes the function easier
to manage

Is [Size 1] a field?

MaxDimension = -1
MaxDimensionValue = -1
For Count = LBound(Dimensions()) To UBound(Dimensions()) 'okay, once
you've determined the lower and upper boundaries, the loop starts by assuming
that the array "36X24X36" is null because there can be no "Dimensions(-1),
right?

The Split function generates arrays from 0 to UpperBoundary, but an
array in VB(A) can start with any Integer. -3 to +3 is also a valid range.

If MaxDimensionValue < Dimensions(Count) Then 'and this is because
an actual number is greater than null?

Yes. You also found my typo.

Me.Length = Count 'shouldn't this be "Me.Length=Dimensions(Count)?

In my original code, i store the index (MaxDimension) and the value
(MaxDimensionValue).

End If
Next Count 'go on to next part of the array

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I would also like to call up values for Dimension1,
Dimension2, and Dimension3 (when it exists) in my form as a double check.
However, looping through the array doesn't allow for simultaneous values of
Dimensions(Count).

This would be Dimension1 = Dimensions(0), Dimension2 = Dimensions(1) and
so on.


mfG
--> stefan <--

 

[Guest]

Hi Stefan,

I guess this brings me back to my original problem... When Count = 2 or
whenever the loop of the array does not return a value, I can't figure out a
way that works for dimension3 to default to zero. In other words, if the
string is "2x4", the field Me.dimension3 = Dimensions(2) causes an 'Subscript
out of range' error because there is no value. Can you suggest a way in
which I can avoid this? I had conceived of a conditional like IIF or If Then
or Select Case to verify that there was valid data in this string, but my
syntax must be poor because I can't get them to work.

 

[Stefan Hoffmann]

hi,

I guess this brings me back to my original problem... When Count = 2 or
whenever the loop of the array does not return a value, I can't figure out a
way that works for dimension3 to default to zero. In other words, if the
string is "2x4", the field Me.dimension3 = Dimensions(2) causes an 'Subscript
out of range' error because there is no value.

I thought this was clear: VBA starts counting the boundaries with 0.

Dimensions()= Split("2x4", "x")

gives you

Dimension(0) = "2"
Dimension(1) = "2"



mfG
--> stefan <--

 

[Guest]

Hi Stefan,

Well, I found a solution that works, but I don't know how elegant it is...
Now the only thing I need to add is a criteria check for bad data. For
example, instead of the size of a part (24x36x18) the data entry person will
get the fields mixed up and input "C" for the type of part. The criterion
for the input has now been fixed, but I needed (and still need) a way to
split the relevant data into a useful format for existing records. I imagine
that I can add a simple IF THEN statement at the beginning of the code to
check for numerical data, but I don't know which function to use. I'll look
into it. I will gratefully accept any clues or suggestions that will cut
down on my search. Thanks again for all of your help.



Private Sub Form_Current()
Dim Count As Long
Dim dimensions() As String
Dim MaxDimension As Long
Dim MaxDimensionValue As Long
Dim Length As Integer
Dim dimension1 As Long

dimensions = Split([Size 1], "X")

MaxDimension = -1
MaxDimensionValue = -1
For Count = LBound(dimensions()) To UBound(dimensions())

If MaxDimensionValue < Val(dimensions(Count)) Then
MaxDimension = Count
MaxDimensionValue = Val(dimensions(Count))
Me.Length = Val(dimensions(Count))
Me.dimension1 = Val(dimensions(0))
Me.dimension2 = Val(dimensions(1))
If UBound(dimensions()) < 2 Then
Me.dimension3 = 0
ElseIf UBound(dimensions()) = 2 Then
Me.dimension3 = Val(dimensions(2))
End If
End If
Next Count
End Sub

 

[Stefan Hoffmann]

hi,

I imagine
that I can add a simple IF THEN statement at the beginning of the code to
check for numerical data, but I don't know which function to use. I'll look
into it. I will gratefully accept any clues or suggestions that will cut
down on my search. Thanks again for all of your help.

Private Sub Form_Current()
Dim Count As Long
Dim dimensions() As String
Dim MaxDimension As Long
Dim MaxDimensionValue As Long
Dim Length As Integer
Dim dimension1 As Long

dimensions = Split([Size 1], "X")

MaxDimension = -1
MaxDimensionValue = -1
For Count = LBound(dimensions()) To UBound(dimensions())

If Not IsNumeric(Dimensions(Count)) Then
MsgBox Dimensions(Count) & " is not a valid numeric value."
End If

If MaxDimensionValue < Val(dimensions(Count)) Then
MaxDimension = Count
MaxDimensionValue = Val(dimensions(Count))
Me.Length = Val(dimensions(Count))
End If
Next Count

Select Case UBound(Dimensions())
Case Is = 2
Me.dimension1 = Val(dimensions(0))
Me.dimension2 = Val(dimensions(1))
Case Is = 3
Me.dimension1 = Val(dimensions(0))
Me.dimension2 = Val(dimensions(1))
Me.dimension3 = Val(dimensions(2))
Case Else
MsgBox "Wrong number of dimensions."
End Select

End Sub

mfG
--> stefan <--

 

[Guest]

Stefan,

Sorry, I didn't see your response yesterday... Apparently I'm still
learning the way my browser treats responses to this forum. Looks like your
solution will work just fine, thank you.
--
Why are you asking me? I dont know what Im doing!

Jaybird

hi,

I imagine
that I can add a simple IF THEN statement at the beginning of the code to
check for numerical data, but I don't know which function to use. I'll look
into it. I will gratefully accept any clues or suggestions that will cut
down on my search. Thanks again for all of your help.

Private Sub Form_Current()
Dim Count As Long
Dim dimensions() As String
Dim MaxDimension As Long
Dim MaxDimensionValue As Long
Dim Length As Integer
Dim dimension1 As Long

dimensions = Split([Size 1], "X")

MaxDimension = -1
MaxDimensionValue = -1
For Count = LBound(dimensions()) To UBound(dimensions())

If Not IsNumeric(Dimensions(Count)) Then
MsgBox Dimensions(Count) & " is not a valid numeric value."
End If

If MaxDimensionValue < Val(dimensions(Count)) Then
MaxDimension = Count
MaxDimensionValue = Val(dimensions(Count))
Me.Length = Val(dimensions(Count))
End If
Next Count

Select Case UBound(Dimensions())
Case Is = 2
Me.dimension1 = Val(dimensions(0))
Me.dimension2 = Val(dimensions(1))
Case Is = 3
Me.dimension1 = Val(dimensions(0))
Me.dimension2 = Val(dimensions(1))
Me.dimension3 = Val(dimensions(2))
Case Else
MsgBox "Wrong number of dimensions."
End Select

End Sub

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Sorry, I didn't see your response yesterday... Apparently I'm still
learning the way my browser treats responses to this forum. Looks like your
solution will work just fine, thank you.

So better use a newsreader:

OE, Thunderbird, 40tude...


mfG
--> stefan <--