Solved

On error not working the second time

Posted on 2014-12-31
16
108 Views
Last Modified: 2015-01-02
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
Comment
Question by:Saqib Husain, Syed
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 40525362
I am not sure that "On Error GoTo 0" clears it all the way. Try Err.Clear instead.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 40525368
Does not work, Did it work with you?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40525373
How are your variables declared? What is dwg and pt1?
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 40525378
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40525383
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
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 40525387
This will allow only two data sets to be collected. Am I right?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40525392
Have you tried to "Compile VBA Project" on this?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40525399
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 40525657
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40526871
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
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 40527167
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40527318
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40527320
I expect it to work if you create an error twice in sequence.
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 40527408
Just perfect. I wonder why I cannot find it in the help file.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40527412
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40527670
I preference is to use ON ERROR RESUME NEXT and checking for errors if expected.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now