VBA Error Handler with "With" Statement

Question

Without setting a Label, is it possible for a VBA Error Handler to resume at the beginning of a With statement?

For example:

Dim rst As DAO.Recordset
Set rst = Nothing

On Error GoTo ErrHand
With rst
    .AddNew
    !MyValue = 1
    .Update
    .Bookmark = .LastModified
End With

ErrHand:

If Err.Number <> 0 Then
    Call SetRST 'Assume this procedure opens the recordset appropriately
    Resume
End If
End Sub()

The code will cause an error on the ".AddNew" line, and then when it goes through the error handler will set the recordset, but then it will Resume on the ".AddNew" line. The problem is that it is still within the "With" statement where CommentRST Is Nothing. Is there a way to tell the error handler to resume at the "With RST" line instead or the ".AddNew" line without creating a label before the "With" statement or checking for a blank recordset first?

I know there are ways around this (as I've just suggested 2 of them), but am curious as to whether this is possible.

Are you handling it this way because your top `rst` might not be open when you call `.AddNew`? — Brad, Feb 25 '15 at 17:25
http://stackoverflow.com/questions/17611545/check-if-adodb-connection-is-open — Brad, Feb 25 '15 at 17:27
@Brad why ADO with MS Access? http://stackoverflow.com/questions/2631678/vba-check-if-object-is-set — Fionnuala, Feb 25 '15 at 17:28
@Fionnuala OP declared their recordset as an ADO recordset. I just stuck with that. Plenty of legit reasons to you ADO, right? — Brad, Feb 25 '15 at 17:32
@Brad good point . It is just that DAO is nearly always better with MS Access. — Fionnuala, Feb 25 '15 at 17:33
@Fionnuala but I think I see what else you're getting at here. We don't know if the error is happening because the `rst` object is not set or it is set and not open, or there is something else throwing the error. — Brad, Feb 25 '15 at 17:34
I can check the recordset and I intend on actually doing it that way, but as I mentioned, I'm curious as to whether this is possible. This was more of an example I had to create the issue I am talking about. As far as ADO, there really is not reason for the ADO recordset (and in actuality, my recordset is DAO, and I'm not sure why I declared it as ADO in this post. I'll update that). — OpiesDad, Feb 25 '15 at 17:37
You're asking whether there is something like `Resume Previous` (there is not). The code executes sequentially and would not go "backwards" or to some previous point without explicitly being told where to go. In this case, how would the compiler *know* to resume at the beginning of the `With` block (and not at some other point)? — David Zemens, Feb 25 '15 at 17:46
you could have another label at the start of the `with` then a `goto` to that label after your error handler but there are certainly better ways of doing something than that. — Brad, Feb 25 '15 at 17:47
`Resume` or `Resume Next` is intuitive, the compiler simply picks up running at the next line after the error was raised, assuming the error handler adequately resolves the error. Anything else would have to be explicit, with a label. — David Zemens, Feb 25 '15 at 17:48
@David. Thanks. That's a very good point. I was thinking that since the line is logically related that it would make sense to jump back to the original With statement, but that doesn't take into consideration many other coding constructs that would leave the compiler confused. — OpiesDad, Feb 25 '15 at 17:49

iDevlop · Answer 1 · 2015-02-25T17:59:22.230

4

Just add a byRef argument to SetRST.

e.g:

Sub SetRST(byref myrs as recordset)
   'do stuff
   set myrs = ...

By the way, your error handling sample sucks: just add an Exit Sub before ErrHand:,
so you won't need to test if err.number<>0, because you know it will always be.

In your error handling, use:

call SetRST rst

Edit:
I would prefer something like:

If rst Is Nothing Then
    Set rst = something
End if
With rst
   'continue here

edited Feb 25 '15 at 17:59

answered Feb 25 '15 at 17:52

iDevlop

24,841
11
90
149

1

I never write the 'Call', which is useless, I just wrote it here to keep asker's style. You're right, it's useless and (therefore) unelegant. – iDevlop Feb 25 '15 at 18:08
Sorry for the confusing code. I understand that the code as written would not open the recordset. This, however, isn't the issue I was having. Even if I added the ByRef arg, this still wouldn't work as when the error handler resumes, it will still resume on the ".AddNew" line which is referencing the Nothing recordset. – OpiesDad Feb 25 '15 at 18:11
@OpiesDad: yes it would – iDevlop Feb 25 '15 at 18:13
@OpiesDad pass parameters instead of using globals, most of your problems will start fading away... – Mathieu Guindon Feb 25 '15 at 18:23
5

I like you. We could always use [more VBA Reviewers on Code Review](http://codereview.stackexchange.com/questions/tagged/vba) if you get tired of SO. – RubberDuck Feb 25 '15 at 18:28

Mathieu Guindon · Answer 2 · 2015-02-25T18:04:49.413

3

A With block holds an instance of an object, and releases it at End With. If you jumped outside the With block, the object is gone.

~~So the answer is no, you can't Resume into the middle of a With block.~~ well you actually can, but things get ugly and confusing.

This is a common misuse of the With statement you have here - you're merely using it because you're being lazy (no offense intended), and don't want to type rst. in front of every line that uses that object.

Proper usage would have the With block itself hold the reference, like this:

With GetRecordset 'a function that returns a recordset
    .AddNew
    !MyValue = 1
    .Update
    .Bookmark = .LastModified
    .Close
End With

..not that I'd recommend working with recordsets like this, but you get the idea ;)

edited Feb 25 '15 at 18:04

answered Feb 25 '15 at 17:51

Mathieu Guindon

69,817
8
107
235

1

You **can** `Resume` into a with block as long as the error handler doesn't execute an `End With`. – David Zemens Feb 25 '15 at 18:02
1

@DavidZemens ouch! I'd rather keep my innocence and believe you can't do that... wouldn't that smell terrible? – Mathieu Guindon Feb 25 '15 at 18:03

score 3 · Accepted Answer · answered Feb 25 '15 at 17:54

In this case, how would the compiler know to resume at the beginning of the With block (and not at some other point)? It would not, and although it may be logically connected (i.e., it's within the With block) that's still no reason to assume by rule that execution should resume at the start of that block, without explicit reference to resume at that point.

What you're asking essentially assumes the source of the error, and then expects that VBA has this assumption built in, but it would certainly not be applicable for all or even most cases, consider below, assume the rst is open/etc., the actual error raises at the Bookmark property, your error handler doesn't account for that, and so resuming at the beginning of the block will cause infinite failure loop!

On Error GoTo ErrHand
With rst
    .AddNew
    !MyValue = 1
    .Update
    .Bookmark = "George Washington"
End With

See the documentation on the Resume statement:

https://msdn.microsoft.com/en-us/library/office/gg251630.aspx

There are your options to Resume

If the error occurred in the same procedure as the error handler, execution resumes with the statement that caused the error. If the error occurred in a called procedure, execution resumes at the statement that last called out of the procedure containing the error-handling routine.

Or to Resume Next:

If the error occurred in the same procedure as the error handler, execution resumes with the statement immediately following the statement that caused the error. If the error occurred in a called procedure, execution resumes with the statement immediately following the statement that last called out of the procedure containing the error-handling routine (or On Error Resume Next statement).

Or to Resume <line>

Execution resumes at line specified in the required line argument. The line argument is a line label or line number and must be in the same procedure as the error handler.

VBA Error Handler with "With" Statement

3 Answers3