Macro Enabled Workbook Locks Up and CPU spikes

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
161
Office Version
  1. 365
Platform
  1. Windows
I am having an issue that is hard to reproduce with a workbook I created. Essentially it is an order entry system that is done through the use of userforms. Users can enter orders, and retrieve existing order entries and edit them.

Now sometimes, and I can't say for certain if it's when the file remains open and is used over an extended period of time, it locks up or gets very slow and it takes ages to open userforms or close them. Sometimes it necessitates the task manager to force close excel as it becomes unresponsive. Afterwards when you open the file again it has no issues and works just fine with no lag.

I don't know if it's a memory thing or what, but the CPU Usage shoots up when it starts locking up and goes back down when I force close excel. Is there something I can add to my VBA that would help prevent the slowing/locking up from happening? It's quite frustrating to have to tell the users to close the file every so often to avoid it occurring.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,873
Office Version
  1. 2016
Platform
  1. Windows
I think you should provide the code for helper to see if there is problem with the code itself. Probably there is something looping that eats up the memory.
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
161
Office Version
  1. 365
Platform
  1. Windows
I think you should provide the code for helper to see if there is problem with the code itself. Probably there is something looping that eats up the memory.
There is a lot of code across multiple userforms and modules, is there an easy way to provide all the code?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,873
Office Version
  1. 2016
Platform
  1. Windows
There is a lot of code across multiple userforms and modules, is there an easy way to provide all the code?
That is going to be a problem.

Check if you have any object created and try set it to Nothing at the end of routine. I guess you have to follow execution flow and see anything looping non-stop.

Yes, I have code that span over many other subroutines and it is not easy to pin point.I have to pause stage by stage to debug.

Sorry, I cannot help you much on this.
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
161
Office Version
  1. 365
Platform
  1. Windows
That is going to be a problem.

Check if you have any object created and try set it to Nothing at the end of routine. I guess you have to follow execution flow and see anything looping non-stop.

Yes, I have code that span over many other subroutines and it is not easy to pin point.I have to pause stage by stage to debug.

Sorry, I cannot help you much on this.
no worries thanks for your input. If it was reproducible it would be much easier to diagnose, but it appears to have no set cause and will sometimes randomly happen. Other times it never does.
 

Forum statistics

Threads
1,148,143
Messages
5,745,043
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top