1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
'**********************************************************************************************************
' Name: isSheetValid
' Author: mielk | 2012-11-16
'
' Comment: Function to check if the given Excel worksheet is valid and you can refer to its
' properties and methods without errors.
'
' Using this function is very helpful since it lets you avoid
' Run-time error '-2147221080 (800401a8)': Automation error.
' This error is generated, when the code tries to refer to a property or a method
' of a Sheet type variable, but the sheet pointed by this variable had been deleted
' before or the Excel workbook containing this sheet had been closed.
'
' Parameters:
' wks The Excel worksheet to be checked.
'
' Returns:
' Boolean True - if the given worksheet is valid and you can refer to it without any errors.
' False - if the given worksheet is invalid, that means it is corrupted or had been
' closed or deleted before this method has been called and referring to it
' will raise an error.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-11-16 mielk Function created.
'**********************************************************************************************************
Public Function isSheetValid(wks As Excel.Worksheet) As Boolean
Const METHOD_NAME As String = "isSheetValid"
'------------------------------------------------------------------------------------------------------
Dim strSheetName As String
'------------------------------------------------------------------------------------------------------
On Error Resume Next
strSheetName = wks.name
'Check method is very easy - if the name of the given worksheet has been assigned to the variable
'[strSheetName], this worksheet is valid and you can refer to it. Otherwise, error would be generated
'and step [strSheetName = wks.name] would be skipped because of [On Error Resume Next] statement above.
isSheetValid = VBA.Len(strSheetName)
End Function