Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access VBA Better Error handling


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


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

Microsoft AccessVBA

Avatar of undefined
Last Comment
Daniel Pineault

8/22/2022 - Mon
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
    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

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

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck