I get this when I have multiple excel workbook open, I’m not sure if it generates because of low resources or if there is some type of VBA Error? There is no code # associated with this. See attached, and thanks in advance for any and all help.
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.
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.
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.
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.
At the beginning of a Loop / End Loop, use DoEvents
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.
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.Code
Sub LipoSuction() 'JBeaucaire (8/3/2009) Dim LR As Long, LC As Long Dim ws As Worksheet For Each ws In Worksheets LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1 LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1 'Clear everything below column A last cell and row 1 last cell ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Clear ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Clear Next ws End Sub
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.
Let me know how it goes.