Saturday, January 12, 2013

Error Handling in VBA

Error Handling in VBA

You do small code in your Excel in VBA. you code works but get bigger and bigger. Sometimes you get error message ans your worksheet or Excel software crash. I have a simple solution for you. So simple that you can't miss it.


Error handling , simple way:

public sub test()
on error goto ErrHandling

'place you code here
ErrHandling:
if err.number <>0 then
msgbox "err.number"
end if
end sub



There is another way to easily deal with error in you VBa, is to skip it.

public sub test()
on error Resume Next
'place you code here
end sub

So if you code encounter a bug, it will not stop your program and simply continue.

Of course, il important write down your error and correct them, but since you may not be a time programmer an you juste want to make something to work, why not.

Now that i have your attention, is a good idea to improve your error handling just a little bit. I hope this helps you and your friends.

Exemple : On the next line, i'll intentionnaly make a code and of course it migth crash.


public sub readdirectory()

dim index1 as integer
on error goto ErrHandling

for each drive
get directory in drive
next

ErrHandling:
if err.number = 1 then
msgbox "drive not available"
resume next
else
msgbox "unknown error, function will stop"
end if

end sub



You run yuo code and your program tries to read an invalid mapped drive or CD-Rom with any cd. Well now that your code intercepted the error, is easier to fix it later: Here is what you could do to prevent it.

public sub readdirectory()

dim index1 as integer
on error goto ErrHandling

for each drive
if drive.isready then
get directory in drive
end if
next

ErrHandling:
if err.number = 1 then
msgbox "drive not available"
resume next
else
msgbox "unknown error, function will stop"
end if

end sub





VBA Excel Post related: 




About 

I invite you to visit my blog for more articles and leave a comment. Check Technologies represents more than 10 years .... Computer and computer aided design.

Official Website Check Technologies



No comments:

Post a Comment