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?

write as first code line
On Error Resume Next


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

On Friday, October 18, 2013 4:17:00 PM UTC-4, Robin Coe wrote:
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?


This works great....thank you