Excel Performance Issues and Errors when PC Usage is high

[heylookitsme]

I have been using Excel for over 25+ years and consider myself an expert. Excel cannot handle complex formulas and big data. Even without volatile formulas Excel can't handle much of anything you throw at it. As I type I sit and wait for now 12 mins because Excel is frozen. And why... because I deleted a column from a table. Holy crap. Make sure you don't ever paste from a FILTERED table into another sheet by accident either or you'll be waiting while Excel re-calculates each and every cell it pastes. Uggghhh FOREVER. Today went into a file to process a macro via VBA and I see the code in the project folder but when I clicked on my code would not show up in the window. Just a blank page where my career will one day be thanks to Excel. I had to open Excel as Safe Mode save the file and re-open it. Tables always seem to cause more headaches then they are worth. Excel has way too many flaws. We need better programmers working at Microsoft. Please someone make Excel better. I deal with this stuff DAILY.

[#2]

only +rep

[#3]

@heylookitsme 

You mention big data and filtering; that sounds database oriented in which case are you using Power Query and Power Pivot which were designed for that purpose?  Could you actually move the data to a database?  As you suggest, going beyond the point at which the workbook collapses under the weight of data is not going to be a rewarding experience.

[#4]

@heylookitsme 

There is a whole website:

https://www.excel-nervt.de/

[#5]

@heylookitsme 

run another instance and work on something else:

[#6]

This data in particular is 7,400 rows and 300 Columns and in a Table.

[#7]

I hated to post this the other day but the frustration builds over time. I also meant to bring up the fact that workbooks I have created that contain Macros have started popping up this error Can't Find Project or Library. Well, then you can't save it even if you try and repair it. It is due to the Visual Basic Application library Reference. The only way I have found to fix the issue albeit temporarily is to close it out, then open it again in Excel's Safe Mode. I just simply save it, close it out and re-open it as I normally would and it works again for a few weeks to a couple of days before doing it again. Believe me for my peers it is a great confidence booster in my abilities when a program I created fails like this. Ring, Ring, "Hey man that file has another error again something about Missing Project or Library and I just did 2 hours worth of work that has to be re-done". Not my fault, Microsoft's but I'll take a look at it. Before anyone says go into reference and look for the missing one and click it. No that won't work in this situation. For one, VBA will no longer show any code or let you select the reference button. This is a known issue with MS for sometime that has never been fixed. I have Microsoft Enterprise 365 the latest version. I make sure it is updated every Monday Morning. Anyone know a permanent fix for this?

[#8]

Funny you should say that because that is exactly what I did.

[#9]

Clarification? only +rep

[#10]

Any way to view it in English?

[#11]

@heylookitsme 

Only with translation tools.

[#12]

@heylookitsme 

is your code modular?  See example below and maybe create error handling routine/subroutine/sub procedure

Main Procedure - Visual Basic | Microsoft Docs

Module mainModule
Function Main() As Integer
MsgBox("The Main procedure is starting the application.")
Dim returnValue As Integer = 0
' Insert call to appropriate starting place in your code.
' On return, assign appropriate value to returnValue.
' 0 usually means successful completion.
MsgBox("The application is terminating with error level " &
CStr(returnValue) & ".")
Return returnValue
End Function
End Module

OtherFunction(

 Other Function Statements or Expressions...

)

Other Sub(

  Other Sub Lines...

  Exit Sub

)

[#13]

The Main Procedure is only required for Console Applications that run on their own. While this workbook contains a Workbook_Open Event it wouldn't qualify based on that. Great idea though.

[#14]

So, I finally think I have Excel's issues all figured out. I have been noticing when Excel starts using about 2-2.5gb of ram is where issues start occurring where Excel does all kinds of crazy stuff. I have a 16gb ram installed and one would think MS Office/Excel could easily run off of that. I have seen several posts in many other forums where users are finding the same issue at the same ram level. I am using Office 365 Apps for Enterprise 64-Bit. So, I should be able to utilize more than what it is limiting me too. I am on the most current version. So, after I completed an Online Repair I still get the Can't find project or library error when I open any file, even a blank brand new spreadsheet. My Reference Library is showing 2 Visual Basic For Applications checked one is pointed to Excel.exe and the other to the appropriate MSO.dll file. Since the one is connected to Excel.exe I can't remove it from the library. I do not want to do a full new install either. There has to be a way to access the reference library and remove this through the registry or something similar.