+ Reply to Thread
Results 1 to 4 of 4

BeforeClose code working in debug but sporadically in actual execution - how can I test it

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    Columbus, OH
    MS-Off Ver
    Office 2007
    Posts
    7

    Question BeforeClose code working in debug but sporadically in actual execution - how can I test it

    I hate asking this question as I fear it will get no replies but I'm completely stumped. I have some code that runs BeforeClose based on a user selection from a user form. It cleans up the entire workbook to delete any unused rows or formulas that are hidden or result in blank values. That works exactly as expected (the clean up code) but it's unpredictable when actually running before close.

    Every time I try to debug it using the ways I'm familiar with (stepping through the code, breaking out and testing pieces of code) it works fine. I get no error message when it doesn't work, I just reopen the file and the rows that should have been deleted weren't. When I try it a second time it works fine.

    My only guess at this point is that I have some other code that's disabling it somehow, but I sure thought I'd notice that when doing a debug...

    My second issue is that my code is complicated and messy because I'm still an apprentice Excel coder at best and I fear an expert will take one look at it and say "I can't work with this - it's too crazy" - so I'm sharing what I THINK you may need to help, but I expect you may need more detail o kindhearted soul who's willing to give this a look!

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    If you made it through all of that without giving up, any guess why it would work sometimes and not others, or any suggestions on how to test it differently to find the problem myself?

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: BeforeClose code working in debug but sporadically in actual execution - how can I tes

    Every time I try to debug it using the ways I'm familiar with (stepping through the code, breaking out and testing pieces of code) it works fine. I get no error message when it doesn't work, I just reopen the file and the rows that should have been deleted weren't. When I try it a second time it works fine.
    When you step through the code to completion, are the changes saved? Upon reopen, you see what you should see?
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: BeforeClose code working in debug but sporadically in actual execution - how can I tes

    Throwing out some ideas to see what sticks.

    Can you determine in your debugging if the cleanup happens but it's not saved, or if the cleanup doesn't happen and it is saved, or both the cleanup and save don't happen?


    With this code...
    Please Login or Register  to view this content.
    If you have more than one workbook open, the ActiveWorkbook may not be the same as ThisWorkbook. Are you saving the right workbook here.



    Where do you declare bDoNotRun? Is it in a standard code module (e.g.; Module1) and declared as Public? Can you monitor bDoNotRun? One possibility is that it's getting set to True on one occasion then later not reset back to False when you want to cleanup.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    09-03-2014
    Location
    Columbus, OH
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: BeforeClose code working in debug but sporadically in actual execution - how can I tes

    GeneralDisarray - yes, if I step through the code completely it does all of the coded deletions and other pieces with no issues. I can stop just about anywhere and step through it (I've tried just about every stop point I can think of) and then let it run past that point (F5) and it will work as designed. It will also work as designed if I F8 through every single step (uber tedious but I had to do it to be sure!)

    AlphaFrog - I feel like I'm responding to a celebrity I see your name here so often in my searches! I thought that since the userform was in the correct workbook that it would force the active workbook to be the correct one but I can certainly change that to make it safer.

    bDoNotRun was my best guess as to the culprit as well but I don't think I've tested it/debugged it thoroughly so it's well worth another look. I declare it as a Public function in my function module (just a regular module - I named a module for functions, one for main code and one for just admin stuff/testing)

    Please Login or Register  to view this content.
    I'll watch bDoNotRun and see if anything funky is happening there and then report back.

    Thank you both VERY much for trudging through that code!
    Last edited by lorikgator; 11-19-2015 at 03:08 PM. Reason: Fixed code wrapper

+ 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] Problems with BeforeClose Event code
    By tmbarnard in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-03-2015, 06:05 PM
  2. [SOLVED] BeforeClose event not running/working when file is closed through VBA in another file
    By jaimelwilson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2014, 03:43 AM
  3. VBA Code Runs in Debug Mode But Returns Type Mismatch Error Outside Debug Mode
    By valerie.k.chiang in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2014, 03:48 PM
  4. [SOLVED] Debug: How to debug this code?
    By reach78 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-25-2013, 07:16 AM
  5. VLOOKUP returning #N/A sporadically
    By Bobbo Jones in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2013, 10:15 PM
  6. Beforeclose sub suddenly stopped working
    By cuewoz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2010, 12:29 PM
  7. Shared Workbook updating sporadically
    By Maddman in forum Excel General
    Replies: 1
    Last Post: 02-28-2005, 10:06 AM

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