On error GOTO statement in VBA

Question

I have this code to find a particular value in an excel sheet using the Ctrl+F command , but when the code does not find anything i want it to throw a message.

    sub test()
    f=5
    do until cells(f,1).value=""    
    On Error goto hello  
        Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
                    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate

f=f+1

        hello: Msgbox"There is an error"

    loop

    endsub

The problem is that even if no error is found the message is still getting shown. I want the message box to be shown only when there is an error.

Use ```Err.Number```, example: ```If Err.Number <> 0 then Msgbox"There is an error"``` — Daniel Dušek, Aug 13 '15 at 11:05
ok what if i have multiple such conditions , how will VB know which err.number belongs to which condition — Anarach, Aug 13 '15 at 11:06
```Err``` object contains informations about runtime-errors. The properties of ```Err``` object will be filled when an error ocures. So the ```Err``` object doen't belong to any condition it just informs if error occured or not. See ```Err.Clear``` as well. — Daniel Dušek, Aug 13 '15 at 11:13

R.Katnaan · Accepted Answer · 2015-08-13T11:36:57.257

7

For that case you should use Exit Sub or Exit Function and let your hello label to the last part of code. See sample:

Sub test()

    f = 5

    On Error GoTo message

check:
    Do Until Cells(f, 1).Value = ""

        Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
              lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
              MatchCase:=False, SearchFormat:=False).Activate
    Loop

    Exit Sub

message:
    MsgBox "There is an error"
    f = f + 1
    GoTo check

End Sub

edited Aug 13 '15 at 11:36

answered Aug 13 '15 at 11:06

R.Katnaan

2,486
4
24
36

what is the difference between exit sub and end sub? – Anarach Aug 13 '15 at 11:09
I dont want the code to end after the error, i want it to continue , – Anarach Aug 13 '15 at 11:10
Nothing, `Exit` is a keyword to stop current process. So, for exit `Funciton` use `Exit Function`, for exit `Sub`, use `Exit Sub` – R.Katnaan Aug 13 '15 at 11:11
I have updated the question, i dont want to come out of the do until loop. – Anarach Aug 13 '15 at 11:13
Ok so , assuming the code has found error in its first iteration and it has printed the message, now will it go back to the loop and continue executing the rest of the statements until the " DO until" statement is satisfied? – Anarach Aug 13 '15 at 11:17
No, when error found, it will shown error and will stop process. If not, message will not come out. – R.Katnaan Aug 13 '15 at 11:18
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86873/discussion-between-anarach-and-nicolas). – Anarach Aug 13 '15 at 11:18
how to do that? write another goto statement after the message to go back into the loop? will it work that way ? will it continue – Anarach Aug 13 '15 at 11:23
`End Sub` is a syntax element that signals the end of the definition of a `Sub`. `Exit Sub` is a statement that causes control to leave the current `Sub`. – Paul Ogilvie Aug 13 '15 at 11:44

score 4 · Answer 2 · edited May 23 '17 at 10:26

4

You need an exit sub (or exit function if this is part of a function instead of a sub) line of code before hello: Msgbox"There is an error", or else the code below it will always be executed. See this post as a reference-

How to stop VBA macro automatically?

Code example-

on error goto bad
    call foo
    exit sub
bad:
    msgbox "bad"
    'clean up code here
exit sub

public sub foo
    msgbox 1/0  'could also trigger the error handling code by doing err.raise, to use user defined errors
end sub

Update:

To fix your loop, you should move the error handling code outside of the loop, but still keep the exit sub before it, to prevent it from being executed regardless.

sub test()
f=5

do until cells(f,1).value=""    

On Error goto hello  

    Cells.Find(what:=refnumber, After:=ActiveCell, LookIn:=xlFormulas, _
                lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate


loop

exit sub

hello: 
    Msgbox"There is an error"

endsub

edited May 23 '17 at 10:26

Community

1
1

answered Aug 13 '15 at 11:07

John Smith

7,243
6
49
61

I have updated the question , i dont want to come out of the do until loop – Anarach Aug 13 '15 at 11:13
I understand that on finding the error it will come out but i want it to go back in the loop. is it possible – Anarach Aug 13 '15 at 11:21
@Anarach you would need a separate goto statement (in the error handling code), which brings you back into the loop (conditional test before of course). – John Smith Aug 13 '15 at 11:22
how to do that? write another goto statement after the "message" to go back into the loop? will it work that way ? will it continue – Anarach Aug 13 '15 at 11:24
IMO it's a messy practice to do it like that though, I would recommend rethinking whatever you are designing because it seems like you expect there to be errors very frequently. – John Smith Aug 13 '15 at 11:28
nicholas beat me to it, but that is how it would look if you wanted the error code to re-enter the loop. yes, it would be similar to a "continue" if an error happened- although i would recommend against using this tactic to handle errors (it would capture all errors, beyond the specific err you are anticipating) unless you cannot redesign to minimize errors happening all together – John Smith Aug 13 '15 at 13:04

On error GOTO statement in VBA

2 Answers2