• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

On error not working the second time

I have this code for fetching data from autocad.

It is supposed to collect a data set from autocad till it runs into an error condition.
Then it moves to the second data set and continues to collect data till it runs into an error condition.
If it gets an error for the first datum of any set it is supposed to exit.

But when I run it the first error handler works fine but the second time it does not.

This code can be tested as it will handle the error on the first instance instance of pt1=..... and will error out on the second instance of the pt1=....


Please tell me what is bugging the second error handler.


Sub Getdis()

    ActiveCell.ClearContents
    Do
        Do
            On Error GoTo 0
            On Error GoTo nxt
            
            pt1 = dwg.Utility.GetPoint(, "From: ")
            pt2 = dwg.Utility.GetDistance(pt1, "To: ")
            ActiveCell.Value = ActiveCell.Value + Round(pt2 / 12, 2)
        Loop
nxt:
        ActiveCell.Offset(1).Select
        ActiveCell.ClearContents
        On Error GoTo 0
        On Error GoTo ext
        pt1 = dwg.Utility.GetPoint(, "From: ")
        pt2 = dwg.Utility.GetDistance(pt1, "To: ")
        ActiveCell.Value = ActiveCell.Value + Round(pt2 / 12, 2)
        On Error GoTo 0
    Loop
ext:
        AppActivate ("Microsoft Excel")
        ActiveCell.Select
    AppActivate ("Microsoft Excel")
    Application.ActiveWindow.Activate
End Sub

Open in new window

0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 6
  • 5
  • 3
  • +1
1 Solution
 
leonstrykerCommented:
I am not sure that "On Error GoTo 0" clears it all the way. Try Err.Clear instead.
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Does not work, Did it work with you?
0
 
leonstrykerCommented:
How are your variables declared? What is dwg and pt1?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Saqib Husain, SyedEngineerAuthor Commented:
dwg is an Autocad document. pt1 is not declared but is returned a drawing coordinate.

But this really should not matter. I just want it to follow the error handling routines ext and nxt whenever it encounters an error.
0
 
QlemoC++ DeveloperCommented:
IDK if it makes a difference, but you should not nest the loops.
Sub Getdis()

  ActiveCell.ClearContents
  Do
    On Error GoTo nxt
            
    pt1 = dwg.Utility.GetPoint(, "From: ")
    pt2 = dwg.Utility.GetDistance(pt1, "To: ")
    ActiveCell.Value = ActiveCell.Value + Round(pt2 / 12, 2)
  Loop

nxt:
  Do
    On Error GoTo ext

    ActiveCell.Offset(1).Select
    ActiveCell.ClearContents

    pt1 = dwg.Utility.GetPoint(, "From: ")
    pt2 = dwg.Utility.GetDistance(pt1, "To: ")
    ActiveCell.Value = ActiveCell.Value + Round(pt2 / 12, 2)
  Loop

  On Error GoTo 0

ext:
  AppActivate ("Microsoft Excel")
  ActiveCell.Select
  Application.ActiveWindow.Activate
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerAuthor Commented:
This will allow only two data sets to be collected. Am I right?
0
 
leonstrykerCommented:
Have you tried to "Compile VBA Project" on this?
0
 
QlemoC++ DeveloperCommented:
Your code collects two datasets too - no difference. But in your code the LOOP after erroring in the inner loop might be associated with the inner DO. And more, I have absolutely no clue why you should get a second dataset from AutoCAD after the first one caused an error - you are doing nothing but to just perform the same "queries" again ...
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Qlemo,

In Autocad the user picks data points manually by clicking the mouse. Right-clicking returns a null value which gives the error condition, signifying the end of data set. After this the code is supposed to pick up a point from the outer loop and then return to the inner loop and continue getting points. Giving a null value to the outer loop means end of job and return to excel.
0
 
QlemoC++ DeveloperCommented:
Your code does this:
 1. Clear the contents of the currently selected Excel cell.
 2. do
 3.   do
 4.     get a point coordinate
 5.     get the distance to another point
 6.     add the distance in some metric to the active cell value
 7.   until error
 8.   Move one cell down
 9.   get point and distance, and store in active cell
10. until error
This is an endless loop, terminating only when two successice point entry errors are generated. This are not (necessarily) two datasets, as you originally mentioned, and that was confusing me.

Your code should do exactly what I described in this comment. If that is the desired behaviour, all should be fine.

Adding the distance in the outer loop is superfluous - the cell has been cleared just before getting the new point / distance data, so you could just assign the distance. But doesn't harm the way it is.
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Qlemo, The problem at the moment is not the way the loop performs. The problem is why does the error handler not take charge the second time.
0
 
Rory ArchibaldCommented:
On Error Goto 0

Open in new window

does not clear the current exception. You need a Resume statement or use
On Error Goto -1

Open in new window

instead.
0
 
QlemoC++ DeveloperCommented:
I expect it to work if you create an error twice in sequence.
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Just perfect. I wonder why I cannot find it in the help file.
0
 
Rory ArchibaldCommented:
On Error Goto -1 is something of a relic. It's generally to be avoided as its use tends to be a sign of poorly written spaghetti code though, like most things, there is a place for it. My preference would be to use separate procedures with their own error handlers rather than nesting loops within loops directly.
0
 
QlemoC++ DeveloperCommented:
I preference is to use ON ERROR RESUME NEXT and checking for errors if expected.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now