Excel 2010 VBA, цикл по именованным диапазонам, ошибка времени выполнения «1004»:

Question

Я назначаю один и тот же макрос нескольким кнопкам Option. Я пытаюсь определить, в каком из названных диапазонов находится щелчок OButton. Таким образом, я получаю ячейку кнопки и перебираю все именованные диапазоны в моей рабочей таблице, чтобы найти пересекающиеся

Мой код до сих пор:

Sub OptionField()
Dim r As Range
Dim nm As Name
Set r = ActiveSheet.OptionButtons(Application.Caller).TopLeftCell

For Each nm In ActiveSheet.Names
  If InRange(r, ActiveSheet.Range(nm)) = True Then           
  'Loop with ActiveSheet.Range(nm) seems to cause this issue
   Debug.Print nm.Name
  End If
Next nm
End Sub

Function InRange(Range1 As Range, Range2 As Range) As Boolean
'returns True if Range1 is within Range2
 Dim InterSectRange As Range
 Set InterSectRange = Application.Intersect(Range1, Range2)
 InRange = Not InterSectRange Is Nothing
 Set InterSectRange = Nothing
 End Function

В небольших вариациях моего кода я получаю два сообщения об ошибках, которые указывают мне на одну и ту же ошибку

Сообщение об ошибке 1: ошибка приложения или объекта

Сообщение об ошибке 2: Ошибка времени выполнения «1004»: метод Диапазон объекта «_Global» не выполнен

Я нашел документацию по MSDN, но мне просто не удалось «получить» или как это исправить.

https://support.microsoft.com/en-us/kb/178510/en-us

Так что я знаю, что VBA требует ссылки, и мне нужно выпустить ее, меня раздражает то, что цикл работает хорошо в течение нескольких итераций (не только в первый раз), а затем завершается ошибкой ...

Редактировать: происходит сбой, когда именованный диапазон ссылается на те же ячейки, что и предыдущий. Так что проясняется вещь итерации

Я пытался изменить цикл к этому, но это ничего не меняет со ссылкой.

For Each nm In ActiveSheet.Names
Dim t As Range
Set t = ActiveSheet.Range(nm) 'it stops working here, when an ranges with same cells are called    
 If InRange(r, t) = True Then      
   Debug.Print nm.Name
  End If
Set t = Nothing
Next nm

Любая помощь с этим будет принята с благодарностью

большое спасибо

Answer 1

Это улучшает ситуацию?

Function InRange(Range1 As Range, Range2 As Range) As Boolean
    'returns True if Range1 is within Range2
    Dim InterSectRange As Range

    InRange = False
    If Range1 Is Nothing Then
        MsgBox "Range1 is nothing"
        Exit Function
    End If
    If Range2 Is Nothing Then
        MsgBox "Range2 is nothing"
        Exit Function
    End If

    Set InterSectRange = Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
 End Function

EDIT # 1

Кроме того, в sub OptionField() именованные диапазоны, вероятно, переопределены . Изменить:

ActiveSheet.Range(nm)

просто:

Range(nm)

EDIT # 2

например

Sub demo2()
    Dim r As Range
    Set r = ActiveSheet.Range("A1")
    MsgBox r.Address
    MsgBox ActiveSheet.r.Address
End Sub

первый MsgBox работает, но второй выходит из строя ............ это потому, что как только r был полностью квалифицирован, ему не требуется лист для его повторной квалификации.