Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help - Error Trapping Doesn't Work

  Asked By: Harry    Date: Nov 04    Category: MS Office    Views: 769
  

I am new to VBA programing and trying to trap errors. I don't
understand why my first trap (for asphalt) works perfectly and my
second trap (for coal) does not. very frustrating! The second trap
will trap a 0 value but won't trap a value above the max value of 4.
I don't get an error statement, the program just locks and I have to
do a ctrl-alt-delete. Any help will be appreciated.

Here is the code:

TryAgain:
Matl = Range("B12")
Range("B13").ClearContents
Title = Matl & " To Be Conveyed"

If Matl = "Asphalt" Then MatlCode = InputBox("1 = Binder 2 =
Crushed 3 = Other Asphalt - Enter Code: ", Title)
If Matl = "Asphalt" And MatlCode < 1 Or MatlCode > 3 Then
GoTo TryAgain
If Matl = "Asphalt" And MatlCode = 1 Then SubMatl = "Asphalt
Binder"
If Matl = "Asphalt" And MatlCode = 2 Then SubMatl = "Asphalt,
Crushed"
If Matl = "Asphalt" And MatlCode = 3 Then SubMatl = InputBox
("Enter Type of Other Asphalt: ", Title)

If Matl = "Coal" Then MatlCode = InputBox("1 = Lump 2 = ROM 3
= Fine-Graded 4 = Other Coal - Enter Code:", Title)
If Matl = "Coal" And MatlCode < 1 Or MatlCode > 4 Then GoTo
TryAgain

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jay Richards     Answered On: Nov 04

Some comments...
"Errors" and "trapping errors", usually refers to trapping system error  like
missing files or data base errors  and such like where the code *can't* do
what you want it to. What you are doing here rather, is testing for a set of
conditions.
I suspect you're coming from something like GWBasic. Use of GoTo nowadays is
normally not such a good idea.
Lots of If statements one after the other like that can be a bit
confusing... And that's what I suspect is happening here. Your test is
actually working but you may not be actually testing for what you want!
It's generally accepted better prctice to seperate If Then Else over
different lines rather than put the whole thing on one line.

You may want to step through your code using the debugger as well while
adding Matl, MatlCode and SubMatl as watches.

Rather than...
If Matl = "Coal" Then MatlCode = InputBox("1 = Lump 2 = ROM 3 = Fine-Graded
4 = Other Coal - Enter Code:", Title)

If Matl = "Coal" Then
MatlCode = InputBox("1 = Lump 2 = ROM 3 = Fine-Graded 4 = Other Coal
- Enter Code:", Title)
Endif

For the GoTo try...
Do
If....... then
....
ElseIf .....
....
Else
Exit do
EndIf
Loop

You have probably declared your variables somewhere... Can you post the
whole code maybe or if it's too big or whatever the variable declerations.
If you haven't declared the variables that's also good information for use.

It's normally good practice to be specific about what you are setting
variables to so...
Matl = Range("B12").text
Or
Matl = Range("B12").value
Is better than Matl = Range("B12"),

I think that the "And"s and "Or"s are confusing things.

Personaly unless I was *totally* positive about my code, I'd try and break
the If... code down to single choices so that I would be sure of what
This *And* That *Or* TheOther
was meant to be testing.

For...
If Matl = "Coal" And MatlCode < 1 Or MatlCode > 4 Then GoTo TryAgain
...What do you actually want?? Break it down...

' The following is just off the top of my head but maybe it'll give you a
start.
If ucase(Matl) = "ASPHALT" then
Select Case MatlCode
Case 1
SubMatl = "Asphalt Binder"
Case 2
SubMatl = "Asphalt, Crushed"
Case 3
InputBox.....
Case Else
' Do Nothing.
End Select
ElseIf Ucase(Matl) = "COAL" then
MatlCode = InputBox....
Select case MtlCode
Case 1, 2, 3
' Do something.
Case Else
' Do Nothing.
End Select
Else
' Not Coal or Asphalt.
EndIf

If you need any more then please do get back to us.

 
Didn't find what you were looking for? Find more on Help - Error Trapping Doesn't Work Or get search suggestion and latest updates.




Tagged: