asked on

Access VBA Better Error handling

Hi

In the following code how would I add error handling that would give the most descriptive error message?

Thanks

Public Function ExcelExportOnly(ByVal CRecordset As DAO.Recordset, ByVal CSheetName As String) As Object

  On Local Error Resume Next

  Dim Excel As Object ' Excel.Application
  Dim Workbook As Object ' Excel.Workbook
  Dim Worksheet As Object ' Excel.Worksheet
  
  Dim Count As Long
  
  Set ExcelExport = Nothing
  Set Excel = CreateObject("Excel.Application")
  Set Workbook = Excel.Workbooks.Add
  Set Worksheet = Workbook.Sheets(1)
  Worksheet.Name = CSheetName
  
  For Count = 0 To CRecordset.Fields.Count - 1
      Worksheet.Range("A1").Offset(, Count).Value = CStr(CRecordset.Fields(Count).Name)
  Next Count
  
  Worksheet.Range("A2").CopyFromRecordset CRecordset
  Set ExcelExport = Worksheet
  Excel.Visible = True
  Set Worksheet = Nothing
  Set Workbook = Nothing
  Set Excel = Nothing

End Function

Open in new window

Fabrice Lambert

Basically, write an error handler and test the Err.Number property.

Public Function Myfunction()
On Error Goto Error
   '//-------------------
   '// random code goes here
   '//-------------------
Exit Function
Error:
    Select Case (Err.Number)
    Case 1
        '// do something
    Case 2
        '// do something
    Case 3
        '// do something
    Case Else
        '// do something
    End Select
End Function

Open in new window

Side note:
Usually, people just display a msgbox.
But you must analyse if the error has an impact on the caller because once trapped, the error vanish (thus the caller never know something wrong happened).
It can be wise to escalate the error with the Err.Raise instruction.

Fabrice Lambert

PS:
On Local Error is an old (and obsolête) version of the On Error instruction.
Both has the same effect.

ASKER CERTIFIED SOLUTION

Daniel Pineault

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial