Solved

On error not working the second time

Posted on 2014-12-31
16
97 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 …
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

705 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

19 Experts available now in Live!

Get 1:1 Help Now