VBA Error Handling
 

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?

VBA Error Handling
 

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

VBA Error Handling
 

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

VBA Error Handling
 

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

VBA Error Handling
 

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