[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

On error not working the second time

Posted on 2014-12-31
16
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 71

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 71

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
 
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 71

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 2000 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 71

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 71

Expert Comment

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

Featured Post

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.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

649 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