557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Error-handling and error trapping in Excel Visual Basic macros
Part three of a four-part series of blogs
What happens when your macros go wrong? That depends what error-handling you have in place. Learn how to use ON ERROR and other commands to trap errors in a structured way.
A common requirement in VBA programs is to display an input box, and get the user to type in an integer. Here is a routine which will allow you to do just that:
Function GetInteger() As Long
Dim InputString As String
'first get something from user
InputString = InputBox("Input a number")
'if it's empty, user chose CANCEL
If Len(InputString) = 0 Then
GetInteger = -1
'otherwise, try to convert to integer
On Error GoTo NotInteger
GetInteger = CLng(InputString)
'if this worked, exit
'if the conversion failed, return error
GetInteger = -1
Here is how you could call this routine to get a whole number (integer), then display its square:
Dim NumberToSquare As Long
'get the number to take square of
NumberToSquare = GetInteger
'if this isn't an integer, say so
If NumberToSquare = -1 Then
MsgBox "You must enter an integer"
'otherwise, display answer
MsgBox "Square of " & NumberToSquare & _
" is " & (NumberToSquare ^ 2)
When you run the SquareNumber routine, if you enter text into the input box it will trigger an error and pass control to the NotInteger label. This in turn will set the value of the function to -1.
If you type in text like this, the code will trigger an error when you try to convert it to an integer.
You will then see an error message like this:
The message which will appear if you leave the input box blank or type in something which isn't a whole number.
This is a typical use of error trapping:
That's pretty much the complete story about error-trapping, but error-trapping aficionados might like to read the final part of this series, in which you can raise your own errors and learn how error messages bubble up through the call stack!
|Parts of this blog|
25 Aytoun Street