VBA error handling for function to check if worksheet exists
I have a function that will add a worksheet and rename it, but first checks if a sheet with that name exists. For this I am using a fairly widely available function here -
Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next ' Set to Resume Next as don't want to end macro if this fails
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function
My problem is that I have already set On Error GoTo error_exit
in the parent OU that calls this function, so On Error GoTo 0
after checking if the sheet exists, nullifies that call.
I tried On Error GoTo error_exit
in this function, but I am getting the following error -
Match error: shortcut not specified
Does anyone know how I can get around this?
source to share
When you exit the function WorksheetExists
, your error area will automatically resume to the previous situation. You don't need to do anything to resume On Error Goto error_exit
in the caller of the sub / function.
It is interesting to note that if you remove error handling from this function and the worksheet with the passed name does not exist, your code will resume execution at the calling error handling label (that is, at the error_exit: label in the calling code)
source to share
I guess what you mean is not using On Error Resume Next, which most programmers don't like, and also means that while debugging, you cannot use "Break On All Errors" to stop the code from running (Tools-> Options -> General → Error Trapping-> Break on All Errors).
For me one solution is to bury Any On Error Resume Next in a compiled DLL, in the old days it would have been VB6. You can use VB.NET today, but I prefer using C #.
If Visual Studio is available for you, then here is some source. Sample is similar to C # TryParse, which returns a boolean indicating successful, but also returns the result in the return parameter (or outside of the parameter in C #)
Here is the C # source, start the class library, naming it BuryVBAErrorsCS, set ComVisible (true), add a reference to the "Microsoft Excel n" COM library, click Register for Interop.
using Microsoft.Office.Interop.Excel;
using System;
using System.Runtime.InteropServices;
namespace BuryVBAErrorsCS
{
// Requires adding a reference to COM library Microsoft Excel
// In AssemblyInfo.cs set ComVisible(true);
// In Build tab check 'Register for Interop'
public interface ICollectionItemTry
{
bool SheetsItemTry(Sheets worksheetsCol, object vItem, out Worksheet result);
bool WorkbooksItemTry(Workbooks workbooksCol, object vItem, out Workbook result);
}
[ClassInterface(ClassInterfaceType.None)]
[ComDefaultInterface(typeof(ICollectionItemTry))]
public class CCollectionItemTry : ICollectionItemTry
{
public bool SheetsItemTry(Sheets worksheetsCol, object vItem, out Worksheet result)
{
result = null;
try
{
result = worksheetsCol.Item[vItem];
return true;
}
catch (Exception)
{ }
return false;
}
public bool WorkbooksItemTry(Workbooks workbooksCol, object vItem, out Workbook result)
{
result = null;
try
{
result = workbooksCol.Item[vItem];
return true;
}
catch (Exception)
{ }
return false;
}
}
}
For Excel VBA client code, there is a source here
Sub TestCCollectionItemTry()
Dim o As BuryVBAErrorsCS.CCollectionItemTry
Set o = New BuryVBAErrorsCS.CCollectionItemTry
Dim ws As Excel.Worksheet
Debug.Assert Not o.SheetsItemTry(ThisWorkbook.Sheets, "Sheet3366", ws)
Debug.Assert ws Is Nothing
'* sanity check
Set ws = ThisWorkbook.Worksheets.Item("Sheet1")
Debug.Assert Not ws Is Nothing
'* assuming Sheet1 exists
Set ws = Nothing
Debug.Assert o.SheetsItemTry(ThisWorkbook.Sheets, "Sheet1", ws)
Debug.Assert Not ws Is Nothing
Set ws = Nothing
Debug.Assert o.SheetsItemTry(ThisWorkbook.Sheets, 1, ws)
Debug.Assert Not ws Is Nothing
'* workbooks
Dim wb As Excel.Workbook
Debug.Assert o.WorkbooksItemTry(Application.Workbooks, 1, wb)
Debug.Assert Not wb Is Nothing
Set wb = Nothing
Debug.Assert o.WorkbooksItemTry(Application.Workbooks, ThisWorkbook.Name, wb)
Debug.Assert Not wb Is Nothing
Set wb = Nothing
Debug.Assert Not o.WorkbooksItemTry(Application.Workbooks, "BonzoDogDoodah.xls", wb)
Debug.Assert wb Is Nothing
End Sub
NOTE WELL Go to the sheet gallery instead of the Worksheets collection.
source to share