Code Interruption

  • If your code includes "On Error Resume Next" .... comment out that line and run the macro again.


    If your code does not include "Option Explicit" at the top of all macro code, outside of all macros, include that statement

    and run your macro again.


    Doing both will insure that Excel is giving you error messages with an explanation.

  • So what exactly do I need to do, can you be more specific as to how to enter it in the code. I’m not even sure why I would have any errors at all, tho. Is there a way to bypass the interruption and write the error code to cells on a hidden worksheet when they develop and continue to run. My problem is that I have multiple users that use this at my facility and also in another state, some ppl have do it understand the error codes, but I can probably deal with that for a little while until I get the codes and work on the bugs. I try and think outside of the box so the my users do not have to worry about encountering software related problems. This is a huge workbook (about 50 meg and has about 60 worksheets and over 500k lines of code between VBA and cells of worksheets), I thought by using the on error resume next would cut down on problems. I also struggle with the num lock going off. I appreciate any and all help that you can give me.

  • Quote

    This is a huge workbook (about 50 meg and has about 60 worksheets and over 500k lines of code

    My first "guess" is that you have finally reached the maximum that Excel is going to let you get away with ... even though the workbook has

    functioned well in the past. The size of your project has, more than likely, finally stressed out Excel to the max.


    Suggestion: See if there is a way to reduce the size of the project to something much, much smaller ... or you may need to go to ACCESS.


    Quote


    I thought by using the on error resume next would cut down on problems.

    The "On Error Resume Next" command is not the preferred manner of handling errors (even though many coders, myself included, use it). The combination of the size of your project and use of the "On Error Resume Next" command has 'hidden / bypassed' any errors that may exist in your code. The command does not eliminate the errors ... it just causes EXCEL to ignore them ... temporarily until something else occurs to 'screw things up'. I would say the size of your project is the thing that has finally 'screwed things up'. This is not a criticism ... please don't take it that way. Simply an honest observation.



    If the project is also storing data, as in a database, of information that the employees are referencing while using the workbook ... and if this data storage is large in size (comprising a large percentage of the 50 megs), you could most likely clear things up by storing all of the data in a separate workbook and change your code to access that second workbook when required.

  • There isn’t any data being stored, just different variables being entered to get calculated results from multiple type of “threads”. It has been successfully working for 20+ years, however with new ppl coming on board and using the program, I try and add useful things that will help with features that our customers are looking for. If I take out the “0n error resume next” will that give me the error code that is the problem? It used to flag me with 1004 a lot but I think that is like a generic and might not allow me to find the problem. As noted below I also have a problem with the num lock key going off and I am constantly having to hit it to turn it on, this usually happens when I use a macro to simply go to a specified worksheet print it and then erase some of the cells of the worksheets that data has been entered related to a specified thread. It is a little hard to explain, and the program works correctly in doing what it needs to do. Again, I appreciate any and all of your help.

  • If the workbook is not storing any data ... perhaps the workbook itself is corrupted. 50 mb is an astronomical size !


    Here are some suggestions given to someone else who was experiencing the same issues as you :


    My 2 cents for whatever it is worth. (Probably not much).

    Almost all of these suggestions were derived from various Excel / Microsoft websites - with the exception of the last one which is personal experience.

    If your code runs 99% of the time without issue, it probably isn't the code syntax (my view).

    Here are a number of suggestions :

    Start Excel in SAFE MODE.

    Run the program and see if the issue occurs again.

    If it doesn't, try disabling any add-ins to Excel.

    Disable all Add-Ins to Excel.

    Change the DEFAULT PRINTER.

    This method seems irrelevant but it is not. As, whenever the user opens an Excel

    Spreadsheet then it internally tries to communicate with the connected Printers

    to check for the compatible margins.

    Recompile Macros

    Open the MS Excel and then navigate to the Developer –> Visual Basic.

    Go to the Tools -> Options. After opening the Options window click on the General tab

    and clear the ‘Compile VBA’. Syntax issues will be revealed.

    Repair Excel

    Re-Install Microsoft Office

    Update or Disable Anti-Virus Software

    Turn off Application.Calculation = xlAutomatic at beginning of macros

    Whenever you update a cell, Excel goes through a process to recalculate the workbook.

    When working directly within Excel you want this to happen 99.9% of the time (the exception

    being if you are working with an extremely large workbook). However, this can really slow

    down your VBA code. It’s a good practice to set your calculations to manual at the begining

    of macros and restore calculations at the end of macros. If you need to recalculate the

    workbook you can manually tell Excel to calculate.

    Code:

    Code
    Application.ScreenUpdating = False    
    Application.Calculation = xlManual
    
        'Your macro code
    
    Application.Calculation = xlAutomatic    
    Application.ScreenUpdating = True

    Using DoEvents

    At the beginning of a Loop / End Loop, use DoEvents

    Loop

    DoEvents

    'Your code

    End Loop

    Workbook Corruption

    From personal experience .. sometimes the Excel file becomes corrupt requiring

    a "re-write" of the code to delete the corruption. I have had good success by:

    Copying the macro to Notepad.

    Re-create the sheet involved with the macro.

    Copy/Paste the macro back into the Sheet Module of the re-created sheet.

    Do the same with the Regular Modules. Copy / Paste macro to Notepad.

    Delete the Module. Recreate the module. Paste macro back in.


    Also :


    Sometimes Excel will "write" unseen data to those cells located BELOW all of the cells you can see on each worksheet.

    In other words .... let's say in one of your sheets, you've used from A1 to R67. There are various labels, fields, etc. located

    in that range.


    From A68 and below, Excel may have written non-viewable entries/data is some of the cells. The following macro, paste

    inside your workbook and run like any other macro, will review all the sheets / all the blank areas below the range you

    can see are in use and erase that extraneous data. If the garbage data is there, the macro will delete it and reduce the

    overall size of your workbook.


    MAKE A COPY OF YOUR WORKBOOK AND PERFORM THE 'CLEAN UP' ON THE COPY FIRST !!!!


    If it works and the overall size of the workbook is reduced, you can do the same on the original or rename the copy and use it.



    Another way you can clean up the unseen data is to highlight all of the empty rows BELOW the last used row where you

    can see your original design. Then press the DELETE key or right click and select CLEAR CONTENTS.


    I prefer the manual method of highlighting all the rows and selecting CLEAR CONTENTS. But ... it takes extra effort and time.



    Hope some of this helps.

  • I appreciate all the help that you have given, I will try these things on Monday when I return to work. One of these should reveal the problem. Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!