+ Reply to Thread
Results 1 to 10 of 10

Why does macro randomly stop?

  1. #1
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    Why does macro randomly stop?

    I have a macro that imports data from another open workbook. It has been running fine until today. Now somewhere inside my nested loops, after several thousand iterations, it simply stops running. There's no error, it just ends.


    Another forum suggested that it might be a memory problem, so I tried closing and restarting. I also used a different source workbook to import from. That hasn't worked.

    It would help if I knew at what point in the code this occurs. Are there any well-worn tricks or techniques for this?

    (While I'm waiting for your answers, I'll try to narrow down what loop it occurs in.)

    Thanks.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Why does macro randomly stop?

    It could be many reasons:
    The loop is not working, it might not loping through the desired length(rows and columns) or looping on the wrong sheet or empty sheet.
    The easiest approach would be- to run it on F8-step by step on each line while you are watching in local window. As it loops, you should be able to spot if the loops jumps or not doing anything.

  3. #3
    Forum Contributor
    Join Date
    01-20-2012
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Why does macro randomly stop?

    You can also add several lines of debug.print within the loops to log relevant info to your Immediate window.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,886

    Re: Why does macro randomly stop?

    You don't say what your loops are. Possibly looping through each of the sheets in the workbook, each of the rows in each worksheet, each of the columns in each of the rows. How do you determine the upper limit of each loop? Do you use .SpecialCells? Do you use On Error Resume Next to cater for "anticipated" errors? If you do, it could be masking another error, or errors.

    You can add Debug.Print (as has already been suggested) at various stages. Use some sort of marker to indicate which Debug.Print statement it is, and include the loop counters and the cell values or variables you are checking.

    You can set break points so you can process whole sections of code without having to F8 through every line of code. Or you can use the Stop command to achieve the same effect.

    Might be an idea to share your code and, ideally, sample workbook(s).

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Why does macro randomly stop?

    Hi shawnvw

    Please post a Link to this...do the same on the other Forum.
    Another forum suggested that it might be a memory problem
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Why does macro randomly stop?

    Do you have any Exit Sub or End statements?
    Do you have any conditional formatting that uses UDFs?
    Remember what the dormouse said
    Feed your head

  7. #7
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    Re: Why does macro randomly stop?

    I tried the Debug.Print flags, but I must have been putting them in the wrong place because none of them came up. Somehow I tracked it down to this:

    Please Login or Register  to view this content.
    Adding a row to a table just makes the macro end. Is this expected behavior?

    More to the point, how do I add a row to the table without stopping?

  8. #8
    Registered User
    Join Date
    01-23-2013
    Location
    phoenix, arizona
    MS-Off Ver
    Excel 2012
    Posts
    71

    Re: Why does macro randomly stop?

    I had this problem just recently, where mine too would just stop without any explanation. While in Excel, press Ctrl + Break/Pause. I have absolutely no idea what that is or does, but it worked. Found the solution somewhere online.

  9. #9
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Why does macro randomly stop?

    Hi shawnvw

    Please do this

    Please post a Link to this...do the same on the other Forum.
    Another forum suggested that it might be a memory problem
    If you have answers on the "Other Forum" we're wasting our time. Not something most of us wish to do.

    If you choose NOT to do this let us know. I'll not bother you again...though I MAY have a solution...perhaps...maybe.

  10. #10
    Registered User
    Join Date
    09-25-2008
    Location
    Winnetka, CA
    Posts
    83

    Re: Why does macro randomly stop?

    Quote Originally Posted by jaslake View Post
    Hi shawnvw

    Please post a Link to this...do the same on the other Forum.

    The other forum page is here: http://stackoverflow.com/questions/7...messages-occur

    Most of the answer involved changing the code, but that wouldn't apply to me until I could tell where the processing stopped. One answer at the bottom suggested restarting Excel (which I had already tried) and moving the data into a new workbook.

    Since then, I discovered there were two problems: certain cells, when selected or modified, would just shut down operation. I've moved the data to a new workbook, and I think that will solve that problem.

    I still had the other problem: adding a new row to a ListObject stopped execution. I changed the table to an ordinary range, then back to a table again, and that has cleared it up.


    So, if anyone else is having this problem: there's a chance it's not the code that's your problem, but corruption on your sheets. Good luck.
    Last edited by shawnvw; 09-10-2015 at 02:42 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro needed to randomly select from a range
    By AeroTheManiac in forum Excel General
    Replies: 2
    Last Post: 03-04-2014, 01:41 PM
  2. Simple macro to randomly generate 0 or 1
    By mattdaviddesign in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2013, 08:32 PM
  3. Set Macro to Run Randomly
    By Jiptastic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2013, 03:23 PM
  4. Macro randomly misses out on a cell.
    By c2q in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2012, 07:14 PM
  5. Macro to randomly pick data and insert, is there one?
    By ntrcptr in forum Excel General
    Replies: 1
    Last Post: 04-03-2007, 03:50 PM
  6. Replies: 1
    Last Post: 06-27-2006, 02:25 PM
  7. Randomly Generated List / Macro
    By carl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2006, 12:15 AM
  8. macro randomly stops working
    By johnlengacher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2006, 02:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1