VBA Error Handling

  • Thread starter Thread starter Robin Coe
  • Start date Start date
R

Robin Coe

In VBA I have code that accesses an Excel Workbook. The code has multiple Private Subs which is looking for various worksheets in the Excel Workbook.Should a Private Sub be looking for an worksheet that does not exist VBA returns an error and stops going through the remaining private subs. I would like to create an Error Handler that upon a Private Sub not locating a particular worksheet it exits that private sub and continues on to the next private sub without stopping the processing. Is this possible?
 
Hi Robin,

Am Fri, 18 Oct 2013 13:17:00 -0700 (PDT) schrieb Robin Coe:
In VBA I have code that accesses an Excel Workbook. The code has multiple Private Subs which is looking for various worksheets in the Excel Workbook. Should a Private Sub be looking for an worksheet that does not exist VBA returns an error and stops going through the remaining private subs. I would like to create an Error Handler that upon a Private Sub not locating a particular worksheet it exits that private sub and continues on to the next private sub without stopping the processing. Is this possible?
Click to expand...

write as first code line
On Error Resume Next


Regards
Claus B.
 
Optional approach is to use this function in an If...Then construct!

Function bSheetExists(WksName As String) As Boolean
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(WksName)
bSheetExists = (Err = 0)
End Function

Example:

If bSheetExists("Sheet1") Then
'do stuff with it
End If

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Here's another that lets you specify the workbook...

Function SheetExists(Sheetname$, Optional wb As Excel.Workbook =
Nothing) As Boolean
'Modified from a Chip Pearson example
On Error Resume Next
SheetExists = CBool(Len(IIf(wb Is Nothing, ActiveWorkbook,
wb).Sheets(Sheetname).name))
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
In VBA I have code that accesses an Excel Workbook. The code has multiplePrivate Subs which is looking for various worksheets in the Excel Workbook.. Should a Private Sub be looking for an worksheet that does not exist VBA returns an error and stops going through the remaining private subs. I would like to create an Error Handler that upon a Private Sub not locating a particular worksheet it exits that private sub and continues on to the next private sub without stopping the processing. Is this possible?
Click to expand...


This works great....thank you
 
Back
Top