Access vba если ошибка то

VBA Errors Cheat Sheet

Errors

See more VBA “Cheat Sheets” and free PDF Downloads

VBA Error Handling

VBA Error Handling refers to the process of anticipating, detecting, and resolving VBA Runtime Errors. The VBA Error Handling process occurs when writing code, before any errors actually occur.

VBA Runtime Errors are errors that occur during code execution. Examples of runtime errors include:

• Referencing a non-existent workbook, worksheet, or other object (Run-time Error 1004)
• Invalid data ex. referencing an Excel cell containing an error (Type Mismatch – Run-time Error 13)
• Attempting to divide by zero

VBA On Error Statement

Most VBA error handling is done with the On Error Statement. The On Error statement tells VBA what to do if it encounters an error. There are three On Error Statements:

• On Error GoTo 0
• On Error Resume Next
• On Error GoTo Line

On Error GoTo 0

On Error GoTo 0 is VBA’s default setting. You can restore this default setting by adding the following line of code:

On Error GoTo 0

When an error occurs with On Error GoTo 0, VBA will stop executing code and display its standard error message box.

Often you will add an On Error GoTo 0 after adding On Error Resume Next error handling (next section):

Sub ErrorGoTo0()

On Error Resume Next
    ActiveSheet.Shapes("Start_Button").Delete
On Error GoTo 0

'Run More Code

End Sub

On Error Resume Next

On Error Resume Next tells VBA to skip any lines of code containing errors and proceed to the next line.

On Error Resume Next

Note: On Error Resume Next does not fix an error, or otherwise resolve it. It simply tells VBA to proceed as if the line of code containing the error did not exist. Improper use of On Error Resume Next can result in unintended consequences.

A great time to use On Error Resume Next is when working with objects that may or may not exist. For example, you want to write some code that will delete a shape, but if you run the code when the shape is already deleted, VBA will throw an error. Instead you can use On Error Resume Next to tell VBA to delete the shape if it exists.

On Error Resume Next
    ActiveSheet.Shapes("Start_Button").Delete
On Error GoTo 0

Notice we added On Error GoTo 0 after the line of code containing the potential error. This resets the error handling.

In the next section we’ll show you how to test if an error occurred using Err.Number, giving you more advanced error handling options.

Err.Number, Err.Clear, and Catching Errors

Instead of simply skipping over a line containing an error, we can catch the error by using On Error Resume Next and Err.Number.

Err.Number returns an error number corresponding with the type of error detected. If there is no error, Err.Number = 0.

For example, this procedure will return “11” because the error that occurs is Run-time error ’11’.

Sub ErrorNumber_ex()

On Error Resume Next
ActiveCell.Value = 2 / 0
MsgBox Err.Number

End Sub

Error Handling with Err.Number

The true power of Err.Number lies in the ability to detect if an error occurred (Err.Number <> 0).  In the example below, we’ve created a function that will test if a sheet exists by using Err.Number.

Sub TestWS()
    MsgBox DoesWSExist("test")
End Sub

Function DoesWSExist(wsName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = Sheets(wsName)
    
    'If Error WS Does not exist
    If Err.Number <> 0 Then
        DoesWSExist = False
    Else
        DoesWSExist = True
    End If

    On Error GoTo -1
End Function

Note: We’ve added a On Error GoTo -1 to the end which resets Err.Number to 0 (see two sections down).

With On Error Resume Next and Err.Number, you can replicate the “Try” & “Catch” functionality of other programming languages.

On Error GoTo Line

On Error GoTo Line tells VBA to “go to” a labeled line of code when an error is encountered.  You declare the Go To statement like this (where errHandler is the line label to go to):

On Error GoTo errHandler

and create a line label like this:

errHandler:

Note: This is the same label that you’d use with a regular VBA GoTo Statement.

Below we will demonstrate using On Error GoTo Line to Exit a procedure.

On Error Exit Sub

You can use On Error GoTo Line to exit a sub when an error occurs.

You can do this by placing the error handler line label at the end of your procedure:

Sub ErrGoToEnd()

On Error GoTo endProc

'Some Code
    
endProc:
End Sub

or by using the Exit Sub command:

Sub ErrGoToEnd()

On Error GoTo endProc

'Some Code
GoTo skipExit
    
endProc:
Exit Sub

skipExit:

'Some More Code

End Sub

Err.Clear, On Error GoTo -1,  and Resetting Err.Number

After an error is handled, you should generally clear the error to prevent future issues with error handling.

After an error occurs, both Err.Clear and On Error GoTo -1 can be used to reset Err.Number to 0. But there is one very important difference: Err.Clear does not reset the actual error itself, it only resets the Err.Number.

What does that mean?  Using Err.Clear, you will not be able to change the error handling setting. To see the difference, test out this code and replace On Error GoTo -1 with Err.Clear:

Sub ErrExamples()

    On Error GoTo errHandler:
        
    '"Application-defined" error
    Error (13)
    
Exit Sub
errHandler:
    ' Clear Error
    On Error GoTo -1
    
    On Error GoTo errHandler2:
    
    '"Type mismatch" error
    Error (1034)
    
Exit Sub
errHandler2:
    Debug.Print Err.Description
End Sub

Typically, I recommend always using On Error GoTo -1, unless you have a good reason to use Err.Clear instead.

VBA On Error MsgBox

You might also want to display a Message Box on error.  This example will display different message boxes depending on where the error occurs:

Sub ErrorMessageEx()
 
Dim errMsg As String
On Error GoTo errHandler

    'Stage 1
    errMsg = "An error occured during the Copy & Paste stage."
    'Err.Raise (11)
    
    'Stage 2
    errMsg = "An error occured during the Data Validation stage."
    'Err.Raise (11)
     
    'Stage 3
    errMsg = "An error occured during the P&L-Building and Copy-Over stage."
    Err.Raise (11)
     
    'Stage 4
    errMsg = "An error occured while attempting to log the Import on the Setup Page"
    'Err.Raise (11)

    GoTo endProc
    
errHandler:
    MsgBox errMsg
   
endProc:
End Sub

Here you would replace Err.Raise(11) with your actual code.

VBA IsError

Another way to handle errors is to test for them with the VBA ISERROR Function. The ISERROR Function tests an expression for errors, returning TRUE or FALSE if an error occurs.

Sub IsErrorEx()
    MsgBox IsError(Range("a7").Value)
End Sub

VBA Programming | Code Generator does work for you!

If Error VBA

You can also handle errors in VBA with the Excel IFERROR Function.  The IFERROR Function must be accessed by using the WorksheetFunction Class:

Sub IfErrorEx()

Dim n As Long
n = WorksheetFunction.IfError(Range("a10").Value, 0)

MsgBox n
End Sub

This will output the value of Range A10, if the value is an error, it will output 0 instead.

VBA Error Types

Runtime Errors

As stated above:

VBA Runtime Errors are errors that occur during code execution. Examples of runtime errors include:

• Referencing a non-existent workbook, worksheet, or other object
• Invalid data ex. referencing an Excel cell containing an error
• Attempting to divide by zero

You can “error handle” runtime errors using the methods discussed above.

Syntax Errors

VBA Syntax Errors are errors with code writing. Examples of syntax errors include:

• Mispelling
• Missing or incorrect punctuation

The VBA Editor identifies many syntax errors with red highlighting:

The VBA Editor also has an option to “Auto Syntax Check”:

When this is checked, the VBA Editor will generate a message box alerting you syntax errors after you enter a line of code:

I personally find this extremely annoying and disable the feature.

Compile Errors

Before attempting to run a procedure, VBA will “compile” the procedure. Compiling transforms the program from source code (that you can see) into executable form (you can’t see).

VBA Compile Errors are errors that prevent the code from compiling.

A good example of a compile error is a missing variable declaration:

Other examples include:

• For without Next
• Select without End Select
• If without End If
• Calling a procedure that does not exist

Syntax Errors (previous section) are a subset of Compile Errors.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Debug > Compile

Compile errors will appear when you attempt to run a Procedure. But ideally, you would identify compile errors prior to attempting to run the procedure.

You can do this by compiling the project ahead of time. To do so, go to Debug > Compile VBA Project.

The compiler will “go to” the first error. Once you fix that error, compile the project again. Repeat until all errors are fixed.

You can tell that all errors are fixed because Compile VBA Project will be grayed out:

OverFlow Error

The VBA OverFlow Error occurs when you attempt to put a value into a variable that is too large. For example, Integer Variables can only contain values between -32,768 to 32,768. If you enter a larger value, you’ll receive an Overflow error:

Instead, you should use the Long Variable to store the larger number.

Other VBA Error Terms

VBA Catch Error

Unlike other programming languages, In VBA there is no Catch Statement. However, you can replicate a Catch Statement by using On Error Resume Next and If Err.Number <> 0 Then. This is covered above in Error Handling with Err.Number.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

VBA Ignore Error

To ignore errors in VBA, simply use the On Error Resume Next statement:

On Error Resume Next

However, as mentioned above, you should be careful using this statement as it doesn’t fix an error, it just simply ignores the line of code containing the error.

VBA Throw Error / Err.Raise

To through an error in VBA, you use the Err.Raise method.

This line of code will raise Run-time error ’13’: Type mismatch:

Err.Raise (13)

VBA Error Trapping

VBA Error Trapping is just another term for VBA Error Handling.

VBA Error Message

A VBA Error Message looks like this:

When you click ‘Debug’, you’ll see the line of code that is throwing the error:

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

VBA Error Handling in a Loop

The best way to error handle within a Loop is by using On Error Resume Next along with Err.Number to detect if an error has occurred (Remember to use Err.Clear to clear the error after each occurrence).

The example below will divide two numbers (Column A by Column B) and output the result into Column C. If there’s an error, the result will be 0.

Sub test()
Dim cell As Range

On Error Resume Next
For Each cell In Range("a1:a10")

    'Set Cell Value
    cell.Offset(0, 2).Value = cell.Value / cell.Offset(0, 1).Value
    
    'If Cell.Value is Error then Default to 0
    If Err.Number <> 0 Then
         cell.Offset(0, 2).Value = 0
         Err.Clear
    End If
 Next
End Sub

VBA Error Handling in Access

All of the above examples work exactly the same in Access VBA as in Excel VBA.

Function DelRecord(frm As Form)
'this function is used to delete a record in a table from a form
   On Error GoTo ending
   With frm
      If .NewRecord Then
         .Undo
         Exit Function
      End If
   End With
   With frm.RecordsetClone
      .Bookmark = frm.Bookmark
      .Delete
      frm.Requery
   End With
   Exit Function
   ending:
   End
End Function

Избегание условий ошибки

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

Одним из ключевых элементов сокращения ошибок во время выполнения является запись небольших процедур, которые делают одно . Чем меньше процедур процедур приходится терпеть неудачу, тем проще код в целом — отлаживать.

Избежать ошибки времени выполнения 91 — Объект или С заблокированной переменной блока:

Эта ошибка будет повышена, если объект используется до назначения ссылки. Возможно, у вас есть процедура, которая получает параметр объекта:

Private Sub DoSomething(ByVal target As Worksheet)
    Debug.Print target.Name
End Sub

Если target не назначена ссылка, приведенный выше код вызовет ошибку, которую легко избежать, проверяя, содержит ли объект фактическую ссылку на объект:

Private Sub DoSomething(ByVal target As Worksheet)
    If target Is Nothing Then Exit Sub
    Debug.Print target.Name
End Sub

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

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

Избегайте ошибки времени выполнения 9 — Подкласс вне диапазона:

Эта ошибка возникает при доступе к массиву за пределами его границ.

Private Sub DoSomething(ByVal index As Integer)
    Debug.Print ActiveWorkbook.Worksheets(index)
End Sub

Учитывая, что индекс больше, чем количество листов в ActiveWorkbook , приведенный выше код вызовет ошибку времени выполнения. Простое предложение охраны может избежать этого:

Private Sub DoSomething(ByVal index As Integer)
    If index > ActiveWorkbook.Worksheets.Count Or index <= 0 Then Exit Sub
    Debug.Print ActiveWorkbook.Worksheets(index)
End Sub

Большинство ошибок времени выполнения можно избежать, тщательно проверив значения, которые мы используем, прежде чем мы их используем, и разветвляемся на другом пути выполнения, соответственно, используя простой оператор If — в сторожевых предложениях, который не делает предположений и не проверяет параметры процедуры, или даже в тело более крупных процедур.

Оператор Error

Даже с защитными пунктами, один не может реально всегда учитывать все возможные ошибки , которые могут быть подняты в теле процедуры. Оператор On Error GoTo инструктирует VBA перейти к метке линии и ввести «режим обработки ошибок» всякий раз, когда во время выполнения происходит непредвиденная ошибка. После обработки ошибки, код может возобновить обратно в «нормальное» исполнение с помощью Resume ключевое слово.

Линейные метки обозначают подпрограммы : потому что подпрограммы исходят из устаревшего кода BASIC и используют GoSub GoTo и GoSub и Return чтобы вернуться к «основной» процедуре, довольно легко написать жесткий код спагетти, если все не строго структурировано , По этой причине лучше всего:

• процедура имеет одну и только одну подпрограмму обработки ошибок
• подпрограмма обработки ошибок работает только в состоянии ошибки

Это означает, что процедура, которая обрабатывает его ошибки, должна быть структурирована следующим образом:

Private Sub DoSomething()
    On Error GoTo CleanFail

    'procedure code here

CleanExit:
    'cleanup code here
    Exit Sub

CleanFail:
    'error-handling code here
    Resume CleanExit
End Sub

Стратегии обработки ошибок

Иногда вы хотите обрабатывать разные ошибки с помощью разных действий. В этом случае вы будете проверять глобальный объект Err , который будет содержать информацию об ошибке, которая была поднята, и действовать соответственно:

CleanExit:
    Exit Sub

CleanFail:
    Select Case Err.Number
        Case 9
            MsgBox "Specified number doesn't exist. Please try again.", vbExclamation
            Resume
        Case 91
            'woah there, this shouldn't be happening.
            Stop 'execution will break here
            Resume 'hit F8 to jump to the line that raised the error
        Case Else
            MsgBox "An unexpected error has occurred:" & vbNewLine & Err.Description, vbCritical
            Resume CleanExit
    End Select
End Sub

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

Private Sub DoSomething(CheckValue as Long)

    If CheckValue = 0 Then
        On Error GoTo ErrorHandler   ' turn error handling on
        ' code that may result in error
        On Error GoTo 0              ' turn error handling off - same level
    End If

CleanExit:
    Exit Sub

ErrorHandler:
    ' error handling code here
    ' do not turn off error handling here
    Resume

End Sub

Номера строк

VBA поддерживает номера строк в стиле legacy (например, QBASIC). Скрытое свойство Erl можно использовать для идентификации номера строки, которая вызвала последнюю ошибку. Если вы не используете номера строк, Erl только вернет 0.

Sub DoSomething()
10 On Error GoTo 50
20 Debug.Print 42 / 0
30 Exit Sub
40
50 Debug.Print "Error raised on line " & Erl ' returns 20
End Sub

Если вы используете номера строк, но не последовательно, а затем Erl возвращает номер последней строки перед командой, вызвавшей ошибку.

Sub DoSomething()
10 On Error GoTo 50
   Debug.Print 42 / 0
30 Exit Sub

50 Debug.Print "Error raised on line " & Erl 'returns 10
End Sub

Имейте в виду, что Erl также имеет только Integer точность и будет бесшумно переполняться. Это означает, что номера строк за пределами целочисленного диапазона дадут неверные результаты:

Sub DoSomething()
99997 On Error GoTo 99999
99998 Debug.Print 42 / 0
99999
      Debug.Print Erl   'Prints 34462
End Sub

Номер строки не так актуален, как утверждение, вызвавшее ошибку, и строки нумерации быстро становятся утомительными и не совсем удобны в обслуживании.

Резюме ключевого слова

Подпрограмма обработки ошибок будет либо:

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

Ключевое слово Resume должно использоваться только в подпрограмме обработки ошибок, потому что если VBA встречает Resume не находясь в состоянии ошибки, возникает ошибка времени выполнения 20 «Возобновить без ошибок».

Существует несколько способов, по которым подпрограмма обработки ошибок может использовать ключевое слово Resume :

• Resume используется отдельно, выполнение продолжается в инструкции, вызвавшей ошибку . Если ошибка на самом деле не обрабатывается , прежде чем делать это, то та же ошибка будет поднят снова, и выполнение может войти в бесконечный цикл.
• Resume Next продолжает выполнение инструкции сразу после инструкции, вызвавшей ошибку. Если ошибка на самом деле не обрабатывается , прежде чем делать это, то выполнение разрешается продолжать с потенциально недействительными данными, которые могут привести к логическим ошибкам и неожиданному поведению.
• Resume [line label] продолжает выполнение на указанной метке строки (или номер строки, если вы используете номера строк в стиле устаревшего стиля). Обычно это позволяет выполнить некоторый код очистки до того, как будет чисто выйти из процедуры, например, чтобы закрыть соединение с базой данных, прежде чем вернуться к вызывающему.

Вкл.

Сам оператор On Error может использовать ключевое слово Resume чтобы проинструктировать среду выполнения VBA для эффективного игнорирования всех ошибок .

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

Вышеупомянутый акцент не может быть особо подчеркнут. On Error Resume Next эффективно игнорирует все ошибки и выталкивает их под ковер . Программа, которая взрывается с ошибкой во время выполнения с учетом недопустимого ввода, — это более эффективная программа, чем программа, которая работает с неизвестными / непреднамеренными данными — будь то только потому, что ошибка намного легче идентифицируется. On Error Resume Next можно легко скрыть ошибки .

Оператор On Error является областью действия процедур — поэтому в данной процедуре обычно должен быть только один , такой оператор On Error .

Однако иногда не удается избежать ошибки, и переключение на подпрограмму обработки ошибок только на Resume Next просто не кажется правильным. В этом конкретном случае утверждение с известным до невозможности может быть обернуто между двумя On Error :

On Error Resume Next
[possibly-failing statement]
Err.Clear 'resets current error
On Error GoTo 0

Команда On Error GoTo 0 сбрасывает обработку ошибок в текущей процедуре, так что любая дополнительная инструкция, вызывающая ошибку времени выполнения , будет необработанной внутри этой процедуры и вместо этого будет переходить в стек вызовов до тех пор, пока она не будет захвачена активным обработчиком ошибок. Если в стеке вызовов нет активного обработчика ошибок, он будет рассматриваться как необработанное исключение.

Public Sub Caller()
    On Error GoTo Handler
    
    Callee
    
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & " in Caller."
End Sub

Public Sub Callee()
    On Error GoTo Handler
    
    Err.Raise 1     'This will be handled by the Callee handler.
    On Error GoTo 0 'After this statement, errors are passed up the stack.
    Err.Raise 2     'This will be handled by the Caller handler.    
    
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & " in Callee."
    Resume Next
End Sub

Пользовательские ошибки

Часто при написании специализированного класса вы хотите, чтобы он поднимал свои собственные конкретные ошибки, и вам понадобится чистый способ для кода пользователя / вызова для обработки этих пользовательских ошибок. Оптимальным способом достижения этого является определение специального типа Enum :

Option Explicit
Public Enum FoobarError
    Err_FooWasNotBarred = vbObjectError + 1024
    Err_BarNotInitialized
    Err_SomethingElseHappened
End Enum

Используя встроенную константу vbObjectError пользовательские коды ошибок не перекрываются с зарезервированными / существующими кодами ошибок. Необходимо явно указать только первое значение перечисления, поскольку базовое значение каждого члена Enum 1 больше, чем предыдущий элемент, поэтому базовое значение Err_BarNotInitialized неявно является vbObjectError + 1025 .

Повышение собственных ошибок времени выполнения

Ошибка выполнения может быть повышена с Err.Raise оператора Err.Raise , поэтому пользовательская ошибка Err_FooWasNotBarred может быть повышена следующим образом:

Err.Raise Err_FooWasNotBarred

Метод Err.Raise также может принимать пользовательские параметры Description и Source — по этой причине рекомендуется также определять константы для хранения каждого пользовательского описания ошибки:

Private Const Msg_FooWasNotBarred As String = "The foo was not barred."
Private Const Msg_BarNotInitialized As String = "The bar was not initialized."

А затем создайте выделенный частный метод для повышения каждой ошибки:

Private Sub OnFooWasNotBarredError(ByVal source As String)
    Err.Raise Err_FooWasNotBarred, source, Msg_FooWasNotBarred
End Sub

Private Sub OnBarNotInitializedError(ByVal source As String)
    Err.Raise Err_BarNotInitialized, source, Msg_BarNotInitialized
End Sub

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

Public Sub DoSomething()
    'raises the custom 'BarNotInitialized' error with "DoSomething" as the source:
    If Me.Bar Is Nothing Then OnBarNotInitializedError "DoSomething"
    '...
End Sub

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

Примечание: наследие Error ключевое слово также может быть использован вместо Err.Raise , но это устаревшее / осуждается.

1. Обработка ошибок на этапе выполнения

Как
тщательно не проверялся бы код, на этапе
его выполнения неизбежно возникают
ошибки. Попытка деления на ноль – это
типичный пример ошибки времени выполнения.
В Access обработка ошибок выполняется с
помощью инструкции On Error. Имеется три
вида инструкций On Error:

1. On Error GoTo Метка –
   осуществляет переход на строку с меткой
   (Метка) при возникновении произвольной
   ошибки на этапе выполнения. Обычная
   часть кода, обрабатывающая ошибки,
   помещается в корпус процедуры. После
   обработки ошибки можно либо вызвать
   повторение той части кода, где произошла
   ошибка, либо проигнорировать ошибку и
   продолжить выполнение последующих
   инструкций. Для возвращения на строку
   с ошибкой используется ключевое слово
   Resume.

2. On
   Error Resume Next – игнорирует ошибку и
   продолжает выполнение последующих
   инструкций.

3. On
   Error GoTo 0 отключает обработку ошибок.

После
обработки первой ошибки инструкция On
Error GoTo Метка продолжает выполняться для
всех последующих ошибок, пока не
закончится выполнение данной процедуры
либо обработка ошибок не будет явно
отключена инструкцией On Error GoTo 0. Если
нет обработки какой-то ошибки или
обработка выключена, то при возникновении
необработанной ошибки приложение сразу
же выдаст сообщение об ошибке и прекратит
работу.

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

Private
Sub MyID_DblClick(Cancel As Integer)

On
Error GoTo Err_MyID_DblClick

Текст
процедуры

Exit_MyID_DblClick:

Exit
Sub

Err_MyID_DblClick:

MsgBox
Err.Description

Resume
Exit_MyID_DblClick

End
Sub

Объект
Err содержит информацию об ошибках
выполнения и обычно используется вместе
со структурой Select Case, чтобы определить,
какое действие предпринять в зависимости
от кода ошибки.

Select
Case Err

Case
58 To 76

Call
FileError- процедура обработки ошибок работы
с файлами

Case
281 To 297

Call
DDEError — процедура для обработки ошибок
DDE

Case
340 To 344

Call
ArrayError — процедура
ошибок
массивов

End
Select

Err=0
— отключение обработки необработанных
ошибок.

1. Работа с объектами и коллекциями

В
VBA можно работать с объектами и коллекциями
Access, библиотекой доступа к данным DAO
(Data Access Objects), библиотекой прямого доступа
к данным баз данных ODBC (ODBC Direct), библиотекой
доступа к данным ADO (ActiveX Data Objects) и другими
библиотеками. В Access, DAO и ADO все объекты
расположены внутри коллекций и доступны
в VBA. Каждый объект имеет свойства и
методы. Все библиотеки организованы в
виде иерархии объектов. Объекты имеют
коллекции (семейства) подчиненных
объектов и т.д. В Access имеется 8 базовых
объектов; их иерархия представлена на
Рис. 12.

Объекты
MS
Access:

1. Application – активное
   приложение;

2. Control
   – элемент управления;

3. DoCmd
   – объект вызова макрокоманд в VBA коде;

4. Form
   – открытая форма;

5. Module
   – объект, ссылающийся на стандартные
   модули;

6. Reference
   – объект, содержащий ссылки на объекты;

7. Report
   – открытый отчет;

8. Screen
   – ссылка на экран;

Названия
семейств формируются путем возведения
в множественное число названия
соответствующего объекта. В свою очередь
большинство объектов имеют присоединенные
коллекции свойств (Properties),
а формы и отчеты – коллекции разделов
и т.д. Так как все объекты в Access хранятся
внутри иерархически связанных коллекций,
то доступ к объекту на нижней ступени
иерархии можно получить, указав все
имена коллекций, разделенных точкой,
начиная от корневого объекта. Например,
Application.Forms(«Заказы»).Controls(0).Properties(0).
Большинство коллекций, к примеру,
коллекции форм и отчетов, являются
глобальными. Тогда к объекту этой
коллекции можно обращаться напрямую:
Forms(«Заказы») или Forms!Заказы.

Рис. 12. Иерархия
объектов Access

Поскольку
библиотека DAO всегда поставляется с
Access,
рассмотрим ее структуру и основные
методы более подробно (смотри Рис. 13).

Объекты
библиотеки DAO:

Database
– открытая база данных;

DBEngine
– ссылка на Microsoft Jet (ядро БД);

Error
– объект ошибок;

Field
– поле в таблицах, запросах, динамических
наборах и т.д.;

Index
– индекс;

Parameter
– параметр запроса;

QueryDef
– сохранённый запрос;

Recordset
– динамический набор данных;

Relation
– связь между таблицами;

TableDef
– сохраненная таблица;

Workspace
– активная сессия.

Рис. 13. Иерархия
объектов DAO

В
программах на VBA имеется набор свойств
объектов, которые возвращают ссылки на
подчиненные объекты:

Me
– ссылка на активную форму или отчет
(доступна в присоединенном модуле);

ActiveControl
– ссылка на активный элемент управления;

ActiveForm
– ссылка на активную форму (доступна в
объекте Screen);

ActiveReport
– ссылка на активный отчет (доступна в
объекте Screen);

Application
– ссылка на открытое приложение;

Parent
– ссылка на родительский объект, т.е.
на коллекцию;

DBEngine
–
возвращает
ссылку на Application.

Работа
с записями и полями

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

Me![Полная
цена] = Me![Цена]
* Me![Количество]

Me.Filter
= «[Адрес] Like
‘*» & Me![ПолеУсловияПоиска]
& «*'»

Me.FilterOn
= True

Если
есть желание читать и менять данные в
некотором поле базового набора, но не
видеть его на экране, то достаточно
поместить элемент управления Поле
на форму и задать свойство Visible
в False.
Но прямой доступ к базовому набору в
Access
невозможен. Можно лишь создать динамическую
копию базового набора и синхронизировать
все производимые действия с ней с базовым
набором самой формы. Для прямого доступа
к записям и полям используется объект
Recordset. Имеются четыре типа Recordset объектов
– table,
dynaset,
snapshot
и forwarrd-only:

1. Table:
   Может быть создан только на основе
   существующей или присоединенной
   таблицы. Предоставляет доступ ко всем
   методам и свойствам таблицы, а также к
   индексам, что дает намного более быстрый
   метод поиска (метод Seek);

2. Dynaset
   — может быть создан на основе таблицы
   или запроса. Позволяет обновлять данные
   в многотабличных запросах и в запросах
   к внешним БД. Обновление Dynaset
   объекта приводит к автоматическому
   обновлению всех участвующих в нем
   таблиц;

3. Snapshot
   — создает статическую копию и существует
   только в то время, когда он создан.
   Последующие изменения таблиц на него
   не воздействуют;

4. Forward-only
   создает статическую копию с просмотром
   только в прямом порядке.

Для
создания объекта типа Recordset используется
метод OpenRecordset:

Set
variable = database.OpenRecodset (source [type, options, lockedits]),
или

Set
variable = object.OpenRecodset ( [type, options, lockedits]),

где
database – это
переменная
типа
Database; object – переменная
типа
TableDef или
QueryDef; source – ссылка
на
объект
типа
TableDef или
QueryDef; type – тип
динамического
набора
(может
принимать
следующие
значения:
dbOpenTable, dbOpenDynaset, dbOpenSnspshot dbOpenForwardOnly);
options может
принимать
следующие
значения:
dbAppendOnly, dbReadOnly, dbForwardOnly,
…; lockedits
– аргумент,
определяющий
разрешение
конфликтов
в
многопользовательских
БД
(может
принимать
следующие
значения:
DbReadOnly, dbPessimistic, dbOptimistic). Например:

Dim db As Database

Dim rst As Recordset

Set db = CurrentDb()

Set rst =
db.OpenRecordset(«Клиенты»,
dbOpenDynaset)

Открыть
Recordset можно и основываясь на переменной
типа формы (допустим только для форм,
основанных на таблице или запросе) с
помощью метода RecordsetClone. Метод RecordsetClone
создает динамический набор, основываясь
на свойстве Источник
данных для
формы

Dim
rstOrders As Recordset

Set
rstOrders = Forms![Заказы].RecordsetClone

или
просто Me.RecordsetClone

Recordset
можно создать, также основываясь на
строке SQL

Set rst =
db.OpenRecordset(«SELECT * FROM Товары
WHERE Цена
> 1000», dbOpenDynaset, dbReadOnly)

После
завершения работы с динамическим набором
его необходимо закрыть. Существуют два
общих способа закрытия объектов в VBA.
Первый заключается в вызове метода
Close, второй – в присвоении соответствующей
объектной переменной значения Nothing.
Например,
rst.Close или
Set rst = Nothing.

Recordset
имеет
текущее
приложение
– current position. Для
синхронизации текущего положения
динамического набора с текущей записью
формы можно использовать свойство
Bookmark
(необходимо всегда помнить, что при
обращении к динамическому набору данные
берутся именно из текущей записи). Для
перемещения по динамическому набору
имеется ряд методов: MoveFirst,
MoseLast,
MoveNext,
MovePrevions,
Move[n].
Например:

Dim
rst As Recordset

Set
rst = Me.RecordsetClone

rst.MoveNext

Me.Bookmark
= rst.Bookmark

Для
определения начала и конца набора можно
использовать свойства BOF (before of file –
начало файла) и EOF (end of file – конец файла).
Если в наборе нет записей, то BOF и EOF равны
True. Если в наборе есть записи, то при
открытии курсор обычно устанавливается
на первой записи и BOF и EOF = False. Если курсор
находится перед первой записью, то BOF =
True, и если курсор находится после
последней записи, то EOF = True.

Число
записей в динамическим наборе можно
получить с помощью свойства RecordCount. Это
свойство возвращает реальное число
записей только для динамического набора,
основанного на таблице, т.е. имеющего
тип dbOpenTable. После открытия динамических
наборов любых других типов число записей
в нем будет неизвестно до тех пор, пока
курсор не будет перемещен на последнюю
запись. Для непосредственной проверки
на наличие в наборе записей лучше
проверить свойство EOF. Если EOF будет
равно True, то RecordCount также будет равен 0.

Public
Function RecCount() As Long

Dim
rstCount As Recordset

Dim
dbs As Database

Set
dbs = CurrentDB()

Set
rstCont = dbs.OpenRecordset(«Заказы»)

If
rstCount.Eof Then

RecCount
= 0

Else

rstCount.MoveLast

RecCount
= rstCount.RecordCount

End
If

rstCount.Close

Set
dbs = Nothing

End
Function

Поиск
определенной записи

Для
наборов, основанных на таблице, можно
использовать метод Seek, который ищет
запись, основываясь на сравнении данных
некоторого индекса с заданными значениями
(самый быстрый метод). Для примера запишем
программу, которая будет проверять
дублирование значений ключевого поля
таблицы Клиенты
перед сохранением записи. Для проверки
результатов поиска в объекте Recordset
имеется свойство NoMatch. Если NoMatch равно
False, то запись с заданными условиями
поиска отсутствует в наборе.

Dim
rst As Recordset

Set
rst = CurrentDb.OpenRecordset(«Клиенты»,
dbOpenTable)

rst.Index
= «PrimaryКey»

rst.Seek
«=»,
Me![КодКлиента]

If
Not rst.NoMatch Then

MsgBox
«Клинт с таким табельным номером уже
существует в базе»

DoCmd.GoToControl
«КодКлиента»

End
If

rst.Close

Метод
Seek всегда начинает поиск с начала набора,
поэтому поиск по одному и тому же условию
будет всегда приводить к одной и той же
записи. Для поиска по динамическим
наборам остальных типов можно применять
также методы FindFirst, FindLast, FindNext и
FindPrevions. Например, подсчитаем количество
заказов определенного клиента:

intCount
= 0

rstOrders.FindFirst
«КодКлиента=» & Me![КодКлиента]

Do
While rstOrders.NoMatch

rstOrders.FindNext
«КодКлиента=»
& Me![КодКлиента]

intCount
= intCount + 1

Loop

Для
поиска записей можно также применять
сортировку и фильтрацию. Для сортировки
записей лучше всего открыть новый
динамический набор, основанный на
запросе SQL с оператором ORDER BY. Для
фильтрации можно задать свойство Filter
объекта Recordset и затем обновить набор с
помощью метода OpenRecordset.
Ниже приведен текст программы,
осуществляющей фильтрацию произвольного
набора:

Function
FilterField(rstTemp As Recordset, strField As String, strFilter As
String) As Recordset

rstTemp.Filter
= strField & » = ‘» & strFilter & «‘»

Set
FilterField = rstTemp.OpenRecordset

End
Function

Обновление
динамических наборов

Обновление
данных возможно только при работе с
динамическими наборами типов dbOpenTable и
dbOpenDynaset. Для редактирования некоторой
записи необходимо вначале установить
курсор на ней, перевести динамический
набор в режим редактирования (метод
Edit), а затем сохранить изменения с помощью
метода Update.

rst.Edit

rst![Товар]
= «Pentium
100»

rst![Цена]
= 100

rst.Update

Метод
Cancel
отменяет внесенные изменения. Для
удаления текущей записи существует
метод Delete. Этот метод удаляет запись
без выдачи каких-либо предупреждений
и не меняет положения курсора. Для
перехода на следующую запись необходимо
вызвать метод MoveNext. Для добавления новой
записи служит метод AddNew. После заполнения
новой записи значениями ее необходимо
сохранить с помощью метода Update, потому
как если после добавления новой записи
перейти к другой записи без сохранения
или просто закрыть динамический набор,
то добавляемая запись будет потеряна.
Следующий пример изменяет значения
полей Цена
и Количество
таблицы Заказы,
отфильтрованной для определенного
клиента.

Dim
rst As Recordset

Dim
dbs As Database

Set
dbs = CurrentDb()

Set
rst = dbs.OpenRecordset(«Заказы»,
dbOpenDynaset)

rst.Filter
= «[КодКлиента]=» & Me![Код
клиента]

Set
rst = rst.OpenRecordset

If
Not rst.EOF Then

With
rst

Do
Until .EOF

.Edit

![Цена]
= rst![Цена]
* 1.2

![Количество]
= ![Количество]
* 2

.Update

.MoveNext

Loop

.Close

End
With

End
If

dbs.Close

Работа
с
полями

Получить
доступ
к
значениям
и
свойствам
полей некоторой таблицы можно, открыв
соответствующий динамический набор.
Коллекция полей Fields является коллекцией
по умолчанию для объекта типа Recordset.
Тогда получить доступ к некоторому полю
можно по его имени или по индексу в
коллекции, например: rst.Fields(Field1”),
rst.Fields(4) или rst![Field1]. После получения
ссылки на объект типа Field можно читать
и изменять его свойства, например:

Dim
fld As Field

fld.DefaultValue
= 1

fld.ValidationRule
= «BETWEEN 1 AND 1000»

fld.Value
= 100

Соседние файлы в папке Лабораторный практикум в Access

• #

  01.03.2016798.72 Кб118Articles2000.mdb

• #

  01.03.2016311.3 Кб117Articles_be_2000.mdb

• #
• #
• #
• #
• #
• #
• #

1

17.12.2012, 16:13. Показов 34381. Ответов 9

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

Sub ProcWithErrors()
        'Первый охраняемый блок
                On Error GoTo ErrHadler1        ' подключение 1-го обработчика ошибок
                        ' Первая часть процедуры, которая может вызвать ошибку.
                        ...
                On Error GoTo 0 отключение 1-го обработчика ошибок
 
        'Второй охраняемый блок
                On Error GoTo ErrHadler2        ' подключение 2-го обработчика ошибок
                        ' Вторая часть процедуры, которая может вызвать ошибку.
                        ...
                On Error GoTo 0 отключение 2-го обработчика ошибок
 
        'Третий охраняемый блок
                On Error GoTo ErrHadler3        ' подключение 3-го обработчика ошибок
                        ' Третья часть процедуры, которая может вызвать ошибку.
                        ...
                On Error GoTo 0 отключение 3-го обработчика ошибок
 
                RepeatPoint:            ' точка, с которой возобновляется выполнение
                        'после обработки ошибки в 3-ей части
                        ...
 
                Exit Sub                'выход из процедуры при отсутствии ошибок
                'ОбработкаОшибок:
                ErrHandler1:    
                        ' 1-ый обработчик ошибок
                        ...
                        Resume  'возврат к оператору, вызвавшему ошибку в 1-ой части
 
                ErrHandler2:    
                        ' 2-ой обработчик ошибок
                        ...
                        Resume Next     'переход к оператору, следующему за оператором 
                        'вызвавшим ошибку во 2-ой части
 
                ErrHandler3:    
                        ' 3-ий обработчик ошибок
                        ...
                        Resume RepeatPoint      'переход к строке, с которой возобновляется
                        'выполнение после обработки ошибки в 3-ей части
End Sub

4

The Basics

Before delving into actual programming functions and sub-routine, we must first establish what is an error handler.

An error handler is a bit of code which will do pre-defined actions whenever an error occurs. For instance, generate a message to the user or developer describing the nature of the error. For an error handler to be useful, it must provide a minimum of information in its message to the user.

Microsoft’s Error Handler

If ever you allow Microsoft to generate code block, say by converting a macro to VBA, and include their ‘error handler’ you will get something like:

Private Sub Command3_Click()
On Error GoTo Command3_Click_Err

    'The code will be here

Command3_Click_Exit:
    Exit Sub

Command3_Click_Err:
    MsgBox Error$
    Resume Command3_Click_Exit
End Sub

Sadly, this error handler is dismal! It will result in a message box displaying a description of the error, but nothing more. Below is an example of what you can expect from Microsoft’s error handler.

In most cases, this simply isn’t enough information, certainly not for a developer, to properly isolate an issue to remedy it.

Proper Error Handler

So what would be the minimum data to provide in an error handler? Well, that’s a very good question and the answer can vary depending on the environment you are deploying your solution, but, for me, a bare minimum would include things like:

• Error Number
• Error Description
• Error Source

With this in mind, below is an example of a typical error handler I use for code I post on my website. It is a great starting point if you are new to VBA coding/error handling.

On Error GoTo Error_Handler

    'Your code will go here
    
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit

A Concrete Example

Sub HelloWorld()
On Error GoTo Error_Handler

    MsgBox "Hello Word!"

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourModuleName/HelloWorld" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Taking Things Even Further

The basic error handler meets a basic need and is a step forward, but for more complex procedure it still can leave you deep in troubleshooting to determine exactly where in a procedure the error is occurring. This is where adding a Line Number in the Error Handler can greatly help!

Luckily, there is the little-known Erl gem! If we add Line Numbers to our procedures, Erl we in fact return the last executed line number (which should normally be the source of our error).

So with this in mind, we can update the Basic Error Handler to

On Error GoTo Error_Handler

    'Your code will go here
 
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

Notice I use a Switch function so the Error Handler will work with, or without, line numbers being present. The result being that now, when line numbers are added to your procedure you will get something like

Now, we starting to get information that will enable us to quickly identify, isolate and remedy an error! We instantly know the exact source of the error, the error number and description, as well as the line number within the procedure that raised the error. Doesn’t get much better than that.

Centralized Error Function

As any developer knows, it takes time to perfect code. Your Error Handler is no different and will probably go through several iterations until you get it just the way you like it. Now, if you implement the code shown above, then any changes will require you editing each procedure, 1 by 1, to update any changes you wish to implement to your error handler. This, of course, could be very time consuming.

This is why, IMHO, it makes much more sense to create and implement an Error Handling function and using it within your procedure. The end result is simplified procedural code (so we reduce the amount of code in each procedure/module) and set things up so that you can change all error handling in one location with ease.

I first found out about this approach, no this wasn’t my idea by any means, from the great Allen Browne when I read his article: Microsoft Access tips: Error Handling in VBA. One extra advantage of Allen’s approach is he logs the errors to a table enabling you to review them when you wish to and not solely have to rely on a user’s recollection of a popup message.

Over the years, I have used and improved upon Allen’s code, but it is an excellent starting and can easily be used as is in production.

So I mentioned having ‘improved’ upon it and people always ask me how exactly? Everything depends on context, but normally I add to the logging function (not displayed to the user, no need) in things like:

• the computer name
• OS Information (version, build, bitness, …)
• Office/Access Information (version, build, bitness, runtime or not, language, …)
• …

and in certain cases I also add things like

• user’s selected language (for my multi-lingual databases)
• I switch the language of the error MsgBox displayed to the user
• …

In such a function, you can (and I have) gotten into taking screenshots so you can see the form/report/… and see the exact data, and/or send the information by e-mail, change the message for certain specific error to make them more understandable!,…. The possibilities here are endless, add anything that may help you in your job of troubleshooting issue!

Automating Insertion of Your Error Handler

If you are going to be doing some serious vba (MS Access, Word, Excel, …) work and not just a little tinkering, you should most probably seriously consider looking into buying a copy of MZ-Tools as enables you to insert your error handler into any procedure at the click of a button. You can even manage multiple versions of your error handler. Another beautiful feature of using MZ-Tools is the fact that it can automatically include things like the function name, module name, … in your error handler at runtime, eliminating another manual task.

Testing Your Error Handler

Sometimes, it would be nice to be able to trigger an error, for instance, to test your error handler. Nothing could be easier in VBA, we can simply use the Raise method to do so. That’s actually how I produced the images for this article. Here’s a simple example:

Public Function ErrorDemo()
10    On Error GoTo Error_Handler

20        Debug.Print "Line 1"
30        Debug.Print "Line 2"
40        Debug.Print "Line 3"
50        Err.Raise 9
60        Debug.Print "Line 4"
70        Debug.Print "Line 5"
          
Error_Handler_Exit:
80        On Error Resume Next
90        Exit Function
       
Error_Handler:
100       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: ErrorDemo/mod_Helpers_Errors" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
110       Resume Error_Handler_Exit
End Function

which then generates

So on Line 50 we Raise error number 9 and as shown in the image above our error handler properly reports that back to us (and much more!).

So with a single line of code, you can easily test your code at any time you would like.

Where & When To Use Error Handling?!

Now this is one point that various developers have different viewpoints on.

We have to ask ourselves, what is the purpose of error handling? And there are a couple answers here:

• Avoid users being able to debug code when an error occurs so they can’t access the raw VBA code of our application
• Present comprehensible messages to the user when an error occurs
• Avoids Global Variables from being reset when an error occurs

I’ve always view it as you should have error handling in all your procedure (with very, very few exceptions). This makes your application bulletproof, even during development and ultimately there is no down side to implementing as such.

That said, as someone like Albert Kallal explains:

Thus, some argue that since we should be deploying database in a compiled state (mde, accde) that there is no need for error handling. (and no I’m not saying Albert is stating that, I’m not going to put words in his mouth, but his comments explain why some developer believe this)

As stated above, while true, it still doesn’t address these issues during development and testing and ultimately why I stand behind recommending implementing proper error handling throughout. I view it as there is no downside to adding error handling throughout. Furthermore, if you do implement some type of error logging system, you will even gain insight into the runnings of your solution making it even more valuable to add error handling throughout!

Alternative and Other Options

I thought I’d also mention a couple other tools well worth mentioning, mainly Great Access Tools – Access Crash Reporter.

I’ve also seen many people refer to UtterAccess’ Global Error Handler code which can be found at Global Error Handling, so that may also be worth your while to check out.

A Few Resources on the Subject

• Microsoft Access tips: Error Handling in VBA
• MZ-Tools – Productivity Tools for Visual Studio .NET (C#, VB.NET), Visual Basic and VBA
• On Error statement (VBA) | Microsoft Docs
• Error function (Visual Basic for Applications) | Microsoft Docs
• Raise method (Visual Basic for Applications) | Microsoft Docs
• Information.Erl Method (Microsoft.VisualBasic) | Microsoft Docs I couldn’t find any VBA documentation, only this .net that references VB Namespace?!