Is range valid


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:                 isRangeValid
' Author:               mielk | 2013-04-26
'
' Comment:              Function to check if the given Excel range 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 an Excel range that had been already closed.
'
' Parameters:
'   rng                 The Excel range to be checked.
'
' Returns:
'   Boolean             True - if the given range is valid and you can refer to it without any errors.
'                       False - if the given range 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 -----------------------------------------------------------------------------------------
' 2013-04-26        mielk           Function created.
'**********************************************************************************************************
Public Function isRangeValid(rng As Excel.Range) As Boolean
    Const METHOD_NAME As String = "isRangeValid"
    '------------------------------------------------------------------------------------------------------
    Dim lngRangeRow As Long
    '------------------------------------------------------------------------------------------------------

    On Error Resume Next
    lngRangeRow = rng.row

    'Check method is very easy - if the row of the given range has been assigned to the variable
    '[lngRangeRow], this range is valid and you can refer to it. Otherwise, error would be generated
    'and step [lngRangeRow = rng.row] would be skipped because of [On Error Resume Next] statement above.
    If lngRangeRow > 0 Then isRangeValid = True


End Function