Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: getting out of "Not Responding"

  1. #1
    New Member
    Join Date
    Apr 2005
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default getting out of "Not Responding"

    Is there any way to stop a macro from running when it gets hung up and my excel is "Not Responding"? Now I have to blow it up which is not a desirable method especially when I have made changes that I wanted to save.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,938
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    9 Thread(s)

    Default Re: getting out of "Not Responding"

    Usually CTRL-Break works, but if you Excel is not responding, I don't know if you have method to do that.

    The bigger question is "why is your macro hanging up?".
    Do you have some code in there that needs re-writing?
    You may be able to add some error trapping to avoid this situation.

    If you can post your code and explain what is happening (and where it might be hanging up), we may be able to help you fix it.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Mar 2011
    Location
    Ireland
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Re: getting out of "Not Responding"

    I have a related "not responding" problem with a couple of multi-worksheet workbooks. Each worksheet has many formulas, many cross-references to other worksheets, and several calls to macro subs and functions. There are too many of these items for me to easily use VBA debugging methods, although I did incorporate quite a few while the workbooks were still in the development phase.

    These workbooks are in frequent use for adding data rows; the formulas etc. in previous rows do not need recalculation. But even if I open them in safe mode - which should avoid recalculation - the problem still recurs, especially when saving and closing each workbook.

    When "not responding" occurs (very frequently now), I can find no method of breaking into the process to see what is happening to cause it. The only things I can do are:
    1. Wait in the hope that Excel will start responding again
    2. Use the Task manager to crash Excel and hope that I have not lost too much new data.


    Is there any way of monitoring and/or logging exactly what happens, when, and where, so that I may have a chance of tracing what is going wrong?

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    48,938
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    9 Thread(s)

    Default Re: getting out of "Not Responding"

    I have a related "not responding" problem with a couple of multi-worksheet workbooks. Each worksheet has many formulas, many cross-references to other worksheets, and several calls to macro subs and functions. There are too many of these items for me to easily use VBA debugging methods, although I did incorporate quite a few while the workbooks were still in the development phase.
    Excel might not be the right tool for the job. When I hear things like "multiple worksheets/workbooks" and many "cross-references", it sounds to me like what you are really trying to develop is a relational database, and Excel is not a very good tool for something like that. Database programs like Access, or bigger database options like SQL, MySQL, or Oracle are better tools to use for those sort of things.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Mar 2011
    Location
    Ireland
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting out of "Not Responding"

    Quote Originally Posted by Joe4 View Post
    Excel might not be the right tool for the job. When I hear things like "multiple worksheets/workbooks" and many "cross-references", it sounds to me like what you are really trying to develop is a relational database, and Excel is not a very good tool for something like that. Database programs like Access, or bigger database options like SQL, MySQL, or Oracle are better tools to use for those sort of things.
    Joe4, you may be right in principle, but I do not have the option.

    Also these Excel applications have been running without this problem for several years up until a few weeks ago. Microsoft Office Technical Support have checked things for me and reported that there is nothing they can find. So it is obvious to me that some VBA "enhancements" I have made recently cannot be working efficiently. The only way I think I can find the culprits is to trace the details of what is happening in the VBA SUBs and somehow pinpoint the processes that are taking the time.

    If there are no tools for doing the trace, then I will have to insert tracing code everywhere that I think it might be relevant. I just hoped (and still do) that someone may have some bright ideas about how to achieve the same end painlessly.

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,604
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: getting out of "Not Responding"

    so what changed a few weeks ago
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    New Member
    Join Date
    Mar 2011
    Location
    Ireland
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting out of "Not Responding"

    Quote Originally Posted by mole999 View Post
    so what changed a few weeks ago
    In the most troublesome workbook, I replaced a complicated formula with a VBA sub. That sub should not be volatile - which is what I have not yet tried out!

    Thanks for asking a most significant question.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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