Vba runtime error 5991

Just like any other programming language, VBA has no luck when it comes to errors and you have to deal with them, no matter what. They may come from different sources, like bad coding, impossible operations (like zero division), or unexpected errors.

The best way to deal with it is to have a proper understanding of all the possible outcomes of the result that you could get with code. Look at the below example where we have a VBA code that calculates the square root of the number using the value which you have in the selected cell.

Sub Square_Root()
ActiveCell.Value = ActiveCell.Value ^ (1 / 2)
End Sub

But if the active cell has a value other than a number, you’ll get a run-time error, just like below.

Error Settings in VBA (Error Trapping)

In the VBA option, you can configure the setting to deal with errors before you start writing codes. To open the VBA settings, go to Tools ➤ Options ➤ Generals ➤ Error Trapping. Here you have three options which you can use.

• Break on All Errors: If you have this option activated, VBA will stop the code for all types of errors even if you have used all kinds of error handling techniques.
• Break-in Class Module: With this option, VBA will stop all your codes that are not handled by any technique. And if you’re using objects such as Userforms, it will also break within those objects and highlight the exact line where the error is.
• Break on Unhandled Errors: This is the default setting that helps you to know about all the errors where you are not using any error handling technique and stop the code for all the unhandled errors. (But, if you’re using objects such as Userforms, this will not highlight the line causing the error in the object but will only highlight the line that’s referring to that object).

Types of VBA Errors

To understand VBA errors, you can split them into four categories, and below is the explanation of these types of errors.

1. Syntax Errors

While writing VBA code you need to follow a particular Syntax and when you skip it or don’t write it in the way it should be you can face SYNTAX error (also called Language error). It’s like typos that you do while writing your codes.

Well, VBA helps you by pointing out these errors by showing an error message. You just need to make sure you have “Auto Syntax Check” activated in your VB editor.

Go to the Tool ➤ Options and make sure to tick the “Auto Syntax Check”. With this, whenever you make a SYNTAX error, VBA will show an error message.

But if “Auto Syntax Check” is disabled VBA still highlights the line of code with the error but won’t show the error message.

2. Compile Errors

It comes when you write code to perform an activity, but that activity is not valid or can’t be performed by VBA. The best example is where you have a code using the IF statement but missed to add END IF at the end of the statement and now when you run this VBA will show you a compilation error message.

Apart from this, there are some other examples of compile errors:

• Using For without Next (For Next).
• Select without End Select (Select Case).
• Not Declaring a Variable when you have “Option Explicit” enabled.
• Calling a Sub/Function that does not exist.

3. Runtime Errors

A runtime error occurs at the time of executing the code. Remember the example, I shared with you above when the code calculated the square root of a number.

When a runtime error occurs while running code, it stops the code and shows you the error dialog box and that error box talks about the nature of the error you have. Let’s say you have written a code that opens a workbook from the location which you have specified but now that workbook is relocated or deleted by someone.

So, when you run the code, VBA will show you a runtime error as it can’t find that file on that location. The message you get in a run time error describes the reason which helps you to understand the reason for the error.

And when a runtime error occurs it stops the execution of the code. If you click on the “Debug” button it shows you the line of code that has that error by highlighting it yellow. Or you can click on the “End” button to stop the code to execute and close the error message.

4. Logical Error

It’s not an error but a mistake while writing code. These types of errors sometimes can give you nuts while finding them and correcting them.

Let’s say you write code and while declaring a variable you used the wrong data type, or you have used the wrong calculation steps. In this case, your code will work fine, and you won’t find this error easily. The best way to deal with this kind of problem is to run each line of code one by one.

Using Debug Tools in VBA

VBA provides you a set of tools to debug your code and remove bugs from your codes.

1. Compile VBA Project

In Visual Basic Editor, there’s an option that you can use instantly after completing your code. These compile options scan each line of your code and show a message box if there is an error in your code.

Note: Compile VBA option only traces Syntax and Compile errors, not runtime errors as these errors only rise when a code is running. To use Compile VBA Project, go to ➤ Debug ➤ Compile VBA Project.

Once you run “Compile VBA Project” and you have no error in your code, the options will be greyed out.

2. Run Each Line of Code One by One

This is how I do it. When I complete a code, I simply run it line by line to check if there’s an error occurring. It may take time, but it helps you to get to about all the errors (Syntax, Compile, and Run-Time).

On the “Debug Toolbar”, there’s a button “Step In” which you can use to execute a code line by line or you can simply press F8 to execute a single line and then press it again to execute the next line in the code.

Using “On ERROR” Statement to Handle VBA Errors

It’s important to check your codes and find possible errors in all the debugging ways you have. But, the best and most effective way is to create error-handling statements that can deal with an error and make your code flawless while executing. Let’s learn about these statements. When an error occurs in a VBA code the best possible ways to handle that error can be:

• Let the VBA ignore the error and execute the code
• Let a special set of statements to run when an error occurs.

In both solutions, you can use “On Error” statements. Below four “On Error” statements that you can use. And now, let’s look at each statement one by one.

1. On Error Resume Next

This simple line of code lets VBA continue executing the code despite the occurrence of an error. The IDEA is simple: Move to the next line of the code if there’s an error found somewhere while executing.

In the below code, you have two lines of code:

• The first line says the value  of cell A1 is 25 divided by 0
• And the second line says the value cell A2 is 10 divided by 5

Now there’s a problem with the code which you have inline one. As you know if you divide anything with 0 the result will be an error.  So, when you run this code VBA will show an error message “Run-time error ‘11’ Division by Zero” and stop the execution.

But when you add the “On Error Resume Next” at the very beginning of the code and run the code, VBA simply skips that line of code where the error occurs and continues with the second line and add that value in cell A2.

Sub myDivide()
On Error Resume Next
    Range("A1").Value = 25 / 0
    Range("A2").Value = 10 / 5
End Sub

So, whenever you want your code to get executed despite an error occurring anywhere simply use the “On Error Resume Next” statement in your code.

But here’s one more thing you need to note down: It will only skip errors that occur after it.

Let’s say if an error occurs at line 5 and you have added “On Error Resume Next” on line 8 then it would not skip that error.  So, the best way is to add it as the first line of the code in the procedure.

2. On Error GoTo 0

It’s the default behavior of VBA that when an error occurred it stops the execution of the code.

Well, using “On Error GoTo 0” make no difference in your code. VBA will simply stop the code and show a message with a description of the error. Then why would I bother to use it? Smart Question. Let’s use the example you have used above in “On Error Resume Next”.

In this code whenever an error will occur VBA will resume to the next line of code and run it and you won’t see any error message. But let’s say you have more lines in your code and you don’t want to surpass those lines if there’s an error in the code.

So, if you enter “On Error GoTo 0” after the second line of code it will restore the VBA’s default error handler which shows error messages each time an error occurs.

3. On Error GoTo [Label]

Think about a place in a building where you can head up in an emergency. In the same way, using “On Error GoTo [Label]”, you can simply create a separate block of code in your main code to deal with an error.

Actually, “On Error GoTo [Label]” is a far better and more convenient way to deal with errors. In the below code, you have “On Error GoTo Oh!Error” now in this line statement the word “Oh!Error” is the label.

If you look at the end of the code where you have a specific starting with the label name and then a code for a message box with a message about the code.

Now, what happens if an error occurs the VBA will jump to the label “Oh!Error” and run the block of code which you have after that label.

But there’s one thing you need to take care of: If an error doesn’t occur even then the label you have in your code will get executed. There are two things you need to do:

• First, make sure to add your Error label at the end of the code.
• Second, add an “Exit Sub” before the error label.

With this, you’ll benefit in both situations. Let’s say if an error occurs and VBA jumps to the label you specified there would only be code from the label itself to code. And if an error doesn’t occur “Exit Sub” statement which you have before the label will exit the procedure without executing the error label.

4. On Error GoTo -1

Before we get into this, let me share something with you.  When an error occurs in a code VBA stores that error log in its memory and only clears it when the routine ends.

O VBA! Live in Present

To deal with the second error in a VBA code you need to clear the first error from VBA’s memory. In the below code, you have two “On Error GoTo [Label]” statements that deal with errors from two different blocks of code.

But if you run this code, when the second error VBA won’t jump to the label which you have defined and instead show the error message “Type Mismatch”.

Sub Square_Root()
On Error GoTo myError1
Range("A1").Value = Range("A1").Value ^ (1 / 2)
myError1:
MsgBox "There's some problem with the value you have in the cell A1."
On Error GoTo myError2
Range("A2").Value = Range("A2").Value ^ (1 / 2)
myError2:
MsgBox "There's some problem with the value you have in the cell A2."
End Sub

To fix this problem you can use “On Error GoTo -1” which makes VBA remove the current error from its storage memory.

Sub Square_Root()
 On Error GoTo myError1
 Range("A1").Value = Range("A1").Value ^ (1 / 2)
 myError1:
 MsgBox "There's some problem with the value you have in the cell A1."
 On Error GoTo -1
 On Error GoTo myError2
 Range("A2").Value = Range("A2").Value ^ (1 / 2)
 myError2:
 MsgBox "There's some problem with the value you have in the cell A2."
 End Sub

Now when you run this code, “On Error GoTo -1” removes the error from the memory and VBA deals with the error in the second statement as you want.

What Else Do I Need to Know to Handle Errors in VBA?

Apart from using error handling techniques, there are a few other things that you can use to deal with errors in a better way.

Err Object

When an error occurred while executing of code, you can use the Err object to get details about that error. There are a few properties and methods which you can use with Err object. Let’s learn them one by one.

Properties

Below are the properties which you can use with the Err object:

• Err.Number: With an error occurred there’s a number stored in the Err Object. In the below code, when occurred the message box will show the error number.  
• Err.Description: This property shows the description of the error which can help you to understand the reason for the error.
• Err.Source: This property shows you in which project the error has occurred.
• Err.HelpContext: This property returns the help context id for the error in the help file.
• Err.HelpContext: This is a string value for the location of the help file.

Normally when you are dealing with errors using error handling techniques you won’t be using the Err Object that much in your codes. But below is a simple example to use it.

Sub Square_Root()
On Error GoTo myError1
    Range("A1").Value = Sqr(Range("A1").Value)
Exit Sub
myError1:
    MsgBox "There's some problem with the value you have in the cell A1." & vbCrLf & _
                "Error Number: " & Err.Number  & vbCrLf & _
                "Error Description: " & Err.Description
End Sub

When you run the above code, and if an error occurred, it will show a message box with the error number and description of the error.

Methods

With Err Object there are two methods that you can also use.

• Err.Clear: This method clears the error number and error description from VBA’s memory (It’s different from “On Error GoTo -1” as it doesn’t completely reset the error).
• Err.Raise: With this method, you can generate a run time error in your code intentionally, and below is the syntax which needs to follow:

Err.Raise [number], [source], [description], [helpfile], [helpcontext]

Quick Tips on Error Handling

Here are a few quick tips which you can use to deal with VBA errors in a better way.

• Use “On Error Resume Next” only when you know for sure about an error to occur and it’s OK to skip the line of code with an error and it’s safe to skip to the next line.
• The best way to deal with run-time errors is by using “Error Handler” with “On Error GoTo [Label]”. This ensures that whenever the error occurs you will know about it, but it won’t show that nasty error message.
• Whenever you use the error handler make sure to use “Exit Sub” before it.

There’s More

Below are some of the links which could be useful for you to learn more about error handling in VBA, make sure to check out all of these:

Type Mismatch (Error 13) | Runtime (Error 1004) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400 | VBA Subscript Out of Range Runtime Error 9

Если вы ищете как runtime error исправить — вы попали по адресу.

Причин появления таковой может быть огромное множество.

Чаще всего причина состоит в том, что выходит новая версия того или иного приложения/игры и она устанавливается прямо поверх старой.

Хотя это далеко не единственная ситуация, которая может вызвать ее появление.

После того, как вы нажимаете на единственную кнопку в окне ошибки, оно просто закрывается и программа также перестает работать.

Бывает, что она все же продолжает работать, но в ней невозможно ничего сохранить. В любом случае, есть несколько способов исправить ее.

Как выглядит ошибка

Способ №1. CCleaner

Самое простое решение заключается в том, чтобы воспользоваться программой для чистки реестра. Как это работает?

Все просто – чистильщик реестра, собственно, и предназначен для того, чтобы исправлять всевозможные проблемы, вроде той, которую рассматриваем мы, систематизировать файлы и добавлять то, чего недостает ему для нормальной работы.

Лучшей по праву считается CCleaner, хотя вы можете воспользоваться и другой.

Чтобы использовать ее, сделайте вот что:

1. Скачайте (вот ссылка на бесплатную) и установите программу.
2. Запустите. Перейдите на вкладку «Реестр» на панели слева.
3. В разделе «Целостность» поставьте галочки на всех возможных пунктах – никто не знает, в чем именно проблема.
4. Нажмите кнопку «Поиск проблем». Когда этот процесс закончится, нажмите кнопку «Исправить…».

Использование

1. Подождите, пока приложение исправит все проблемы. Перезагрузите компьютер.

После этого runtime error должен перестать появляться. Если нет, переходим к следующему решению.

Способ №2. DirectX

Вполне вероятно, что ее появление вызывают какие-то проблемы с DirectX. Возможно, какие-то файлы исполняемых библиотек были повреждены или же существующие библиотеки банально устарели.

В любом случае, лучше всего просто установить наиболее актуальные варианты и, опять же, перезагрузить компьютер.

Для этого зайдите на сайт Microsoft, на страницу с возможностью скачать DirectX. Запустите скачанный файл и выполните все действия, подразумевающие установку исполняемых библиотек.

Здесь нет никаких особых рекомендаций – обычная.

Страница загрузки DirectX

Если после установки и перезагрузки компьютера ошибка не исчезла, идем дальше.

Способ №3. Visual C++

О распространяемом пакете Visual C++ в контексте рассматриваемой проблемы можно скачать то же самое, что и о библиотеках DirectX.

Возможно, какие-то файлы были повреждены или версия устарела.

Бывают в данном случае и другие ситуации, когда установленная C++ попросту не подходит для вашей операционной системы.

Ниже приведена таблица с вариантами, которые подходят для разных версий ОС.

Так вот, в зависимости от того, какая у вас ОС, вам следует скачать и инсталировать на свой компьютер C++2008 (64-бит, 32-бит), C++2010 (64-бит, 32-бит) или же C++2015 обновление 3.

Это наиболее актуальная версия по состоянию на июнь 2017 года. В будущем выйдут новые, поэтому следите за новостями на официальном сайте Microsoft.

Скачивание происходит одинаково – выбираем язык распространяемого компонента и жмем кнопку «Скачать». После этого останется запустить загруженный файл.

После этого перезагрузите компьютер.

Страница загрузки Visual C++

Способ №4. Microsoft .NET Framework

Здесь все то же самое – Microsoft .NET Framework тоже может вызывать рассматриваемую проблему из-за отсутствия каких-то собственных файлов. И этот компонент также нужно скачать и установить.

Но есть определенный, правильный порядок ее инсталяции:

Сначала необходимо инсталируйте версию 2.0 (вот ссылка). Попробуйте запустить программу, в которой раньше возникала проблема, если она не исчезла, устанавливайте дальше.

После этого 3.0 (скачать).

Если не помогает, 4.0 (ссылка).

Наконец, если у вас Windows Vista SP2, 7 SP1, 8, 8.1, Server 2008 SP2, Server 2008 R2 SP1, Server 2012 или Server 2012 R2, установите 4.6.2 (скачать).

Скачивание происходит точно так же, как и в случае с пакетами Visual C++.

Если ничего из этого не помогает, значит, случился сбой самой операционной системы. Пригласите на дом специалиста или сдайте компьютер в сервисный центр, если это возможно.

В крайнем случае, сделайте откат системы или вовсе переустановите ее.

Источник