VBA is a programming language that is used in conjunction with a host application like Microsoft Word or Microsoft Excel. Its coding standards or syntaxes are very similar to VB 6 or VB script.

“VBA not responding” is an irksome and time consuming situation/error that can occur during VBA code runtime.

What causes this issue? And how can you resolve it?

There may be several reasons for this issue. Some of these are:

Low system resources

The computer’s speed or its free space might be maxed out. This could lead to a lack of memory required for execution of the program (your code).

Solution

1. Clear all your temp files.

2. Do a “Disk Cleanup” using the button on the properties window of “C” drive.

3. Try adding additional physical RAM to the system if possible.

4. Close all unused applications — at least until the program is executed successfully.

Infinite loops

Some loops do not have a defined condition to bring the runtime control out of the loop. Such loops are called infinite loops. In other words, the control keeps iterating through such loops endlessly looking to pass a condition which cannot actually pass.

Example

One example is if you are waiting for a button to become enabled without doing something to enable it. Let us assume that the “Submit” button of a form will get enabled only if all mandatory fields are filled. And also assume that one field is left unfilled.

Then a loop waiting for the “Submit” button to be enabled keeps iterating until we use “Ctrl+Break” to debug (or) kill that task using the task manager.

Solution

Loops are equally as dangerous as they are useful. So, we need to build logic wisely. If possible, we can set counters inside the loop and also set conditions to exit the loop of the counter reaches some maximum limit. Or you can use error handling methods to prevent the control from running into an infinite loop.

Usage of status bars

It is pleasing for the end user to see a status bar indicating the current status during the runtime. But you might not realize that it consumes some amount of memory and thereby reduces the speed of execution, (i.e.) increases the execution time.

Solution

You can avoid displaying a status message if it is not really required. In case you need it to impress your client / end user, you may reduce the frequency of display!

Code to use status bar:

Application.StatusBar = “Your message goes here… “

Usage of Progress bar

Progress bars can be built into our VBA macro as a module using a “Userform” and progress indicator.  It is reassuring to see the progress of our execution in percentages during runtime. But this feature as a userform, control, and separate code weaved into our macro can easily use up too many resources during code execution.

Solution

Use this feature only if it is really required. Try your best to retain its original look without changing font or colors of the form or the progress indicator control.

Conclusion

Start with clean code, so you’re not sitting and pondering when you have an infinite loop or a “not responding” error — lest you lose your data or changes.

Also, it’s wise to backup your excel worksheet data frequently to avoid huge loss of data in the event you need to kill the task while Excel or your system hits a “Not Responding” mode.