Excel VBA 指定したRange範囲をCollectionに注入する

関数 makeColl(arg1,arg2)as Collection

第一、第二引数にRange(arg)のarg部分をString型で指定することで、指定範囲の要素が注入されたcollectionオブジェクトを返す。
【使用例】
Dim coll as new Collection
Set coll =makeColl("A1","A5")

関数 cCelltoRange(rowN ,colN)as Range

第一、第二引数にlong型で行,列を指定することで、Rangeオブジェクトを返す。
【使用例】
Dim coll as new Collection
Set coll =makeColl(cCelltoRange(1,1),cCelltoRange(5,1))

Option Explicit
Sub main()
Dim targetCellName1 As String
Dim targetCellName2 As String
Dim str As Variant
Dim targetColl As Collection
Set targetColl = New Collection
  targetCellName1 = InputBox("", "範囲セル１を指定")
    If checkCellValue(targetCellName1) = False Then
        MsgBox "無効なセル範囲"
        End
    End If
  
  targetCellName2 = InputBox("", "範囲セル2を指定")
    If checkCellValue(targetCellName2) = False Then
        MsgBox "無効なセル範囲"
        End
    End If
  
  Set targetColl = makeColl(targetCellName1, targetCellName2)
    For Each str In targetColl
      MsgBox (str)
    Next

End Sub

Function makeColl(arg1 As String, arg2 As String) As Collection
  'argにrange範囲を指定し、collectionで返す
  '「【arg1 例】B2」「【arg2 例】B4」→B2:B4
  
Dim coll As Collection
  Dim targetRange As Range
  Dim item As Variant
  Set coll = New Collection
  
  Set targetRange = Range(arg1 & ":" & arg2 )
    For Each item In targetRange
      coll.Add (item)
    Next
  
  Set makeColl = coll
End Function

Function cCelltoRange(rowN As Long, colN As Long) As Range
  '行、列番号を指定でRangeオブジェクトを返す
  Set cCelltoRange = Range(Cells(rowN, colN).Address)
End Function

Function checkCellValue(str As String) As Boolean
'要参照設定 Microsoft VBScript Regular Expressions 5.5
  Dim reg As New RegExp
    With reg
      .IgnoreCase = False
      .Pattern = "[A-Z]+[1-9]+\d*"
    End With
  checkCellValue = reg.test(str)
End Function