how to implement error handling in the VBA code

Question

I have code which picks up the database server name from "C10" cell of excel,after taking the value from the cell, it connects to the server and then execute the Stored procedure.

My Server Name in "C10" cell : LE64\SQL2012

Option Explicit

Sub Button1_Click()
        Call TS
End Sub

Sub TS()
        'Variable Declaration
        Dim OutPut As Integer
        If MsgBox("Do you want to take the Snapshot?", vbQuestion + vbYesNo, "MS Excel") = vbYes Then
             Dim con          As Connection
             Dim rst          As Recordset
             Dim strConn      As String
             Dim strDatabase  As String
             Dim myRange      As Range
             Dim objWorkbook  As Excel.Workbook
             Dim objWorkSheet As Excel.Worksheet

             Set objWorkSheet = ThisWorkbook.Sheets("MS Excel")
             objWorkSheet.Activate

             Set con = New Connection
             Let strDatabase = objWorkSheet.Range("C10").Value

             strConn = "Provider=SQLOLEDB;"
             strConn = strConn & "Data Source= " & strDatabase & ";"
             strConn = strConn & "Initial Catalog=Warehouse;"
             strConn = strConn & "Integrated Security=SSPI;"
             con.Open strConn
             Set rst = con.Execute("Exec [dbo].[LoadSP]")

             MsgBox "Snapshot successfully taken", vbInformation, "MS Excel"
             con.Close
        End If
End Sub

but when I change the value of the cell "C10" slightly like to "LE64\SQL201" it fails to connect to the server and throws an error below:

"SQL Server Doesn't exist or access denied" in a message box

I want to handle and show this above error in a different Message box highlighting "Invalid Database name" if it fails to connect to right Database Server after picking up the value from C10 cell.

I had a look at different error handling mechanism on the web but didn't able to implement it.

Can any one help me in adding a try catch block to my code or any error handling mechanism to deal with the above issue.

Answer 1

Have a look at this sample code..

 Sub MySub()

    On Error GoTo ErrorHandler
    . . .
    Exit Sub

 ErrorHandler:
    . . .
    Resume <or Exit Sub>
    . . .

 End Sub

this is simplest way of handling error..

for more advance option please refer Source