Excel Performance is too slow and most of the time not responding

[SBBmaster09]

Hi Friends,

I would like to ask if how can I speed up the performance of my excel. Everytime I create or simple copy a sheet with VBA codes it becomes Not Responding and hang.

I have Lenovo 15.6 Laptop, Windows 10, 8GB, 64bit but why is this happening? What do I need to adjust? I want to create more VBA tools, that's why I bought a high-end laptop.

Please help.


Thank you.

screenshotPC.PNG

[mikerickson]

How big are these files?

[SBBmaster09]

The file is a simple vba excel file that has a size of 2.80 MB (2,945,154 bytes)

[MrShorty]

IMO, 3MB is a fairly large file, mine tend to be less than 0.5 MB. I obviously cannot comment on how "simple" the spreadsheet it, but it seems large enough to have some problems.

You have provided almost no information about the file, so we cannot provide any specific suggestions. A few generic suggestions:

1) Pivot tables are usually very fast -- usually much faster than built in formulas or VBA routines. If the formulas/procedures you are trying to perform in this file could be replaced by one or more pivot table operations, performance would likely improve dramatically.
2) In many formulas, we sometimes refer to full columns. Some formulas (especially array formulas using SUMPRODUCT()) cannot ignore unused cells. Improvements can often be found by limiting references to the necessary range and not to full columns/rows.
3) I see a lot of array formulas that duplicate effort. I have seen some significant improvements on this forum where one will take part of a large formula, put it in a helper cell (so that that part of the formula only calculates once), then refer to this helper cell.
4) Lookups are a common source of slow performance -- especially linear lookups (lookup type argument is set to FALSE or 0). Examine any lookups to see if they can be improved.
5) Where you are also using VBA, there can be a significant performance penalty everytime VBA reads from or writes to the spreadsheet. You want to minimize those operations as much as possible.
6) Use of volatile functions (OFFSET(), INDIRECT(), TODAY(), etc.) Can significantly impact performance, as they can cause unnecessary recalculation.

When all is said and done, the basic process of speeding up a spreadsheet involves identifying the bottlenecks and improving them. Those are the first things I would probably look for if I had access to your file.

[SBBmaster09]

Hi, Please see attached file i am referring to test my excel VBA file. I am trying to copy the worksheets in a new workbook (manually) since I cannot remember the password I used for the codes though i have a documentation of its codes. Si even transferring it takes time and hangs in the end and never succeeded. i already reformatted my pc just to check if its about the performance but still no change. i dont think its about vba codes because my computer before can pull out large vba files (office workstation, it has only 4 gb ram 64bit).

https://drive.google.com/file/d/0B45...ew?usp=sharing

[MrShorty]

Can you describe the problem in more detail? I am not sure I can recreate the problem. I filled in a handful of fields with random text, clicked on save form and it saved just fine (after several seconds). I can see the sheet flicker with each selection change, so it appears there is a selection change event running. Without the password to unlock the project, it will probably be next to impossible to debug or search for the bottlenecks. At this point, I would say that it seems to work just fine (and this computer is pretty slow), and I get only brief not responding messages.

Does it do this on all computers, or only the current computer you are working on?

[SBBmaster09]

only in my computer, my own laptop that i bought just for my personal use. that is why i am asking what could be the possible reason.. since i bought one of the hi end laptops at that time. if you could advise something related to technical related that coud help my excel works fine.. if you could determine i can schedule a screen sharing to show the actual happening..

[MrShorty]

I have no idea what would cause something like this to be slow/unresponsive on only one computer like that.

[Aardigspook]

Do you have anything in your personal macro workbook which might be running on this computer and not on others?

[snb]

Did you check the availability of references in the VBEditor ?