VBAでツールを作っていた時にハマったのでメモ。

要件

シートAのRangeと同一範囲のセルを
シートBのRangeとして取得したい

実装と問題

ひとまず考え付く実装がこう。

    ' SheetAの"A1"を取得
    Dim rangeA As Range
    Set rangeA = Worksheets("SheetA").Range("A1")
    
    ' SheetBについてRange.addressを用いることでSheetAと同一のセルを取得 
    Dim rangeB As Range
    Set rangeB = Worksheets("SheetB").Range(rangeA.Address)

上記の実装で基本的にはうまくいくのだが、Rangeが複雑でRange.Address()の返り値が255文字以上の場合、想定しない結果となる。

つまりどういうことかというと、

こういうワークシートがあったときに、

下記のコードを実行すると、

    ' Range.Address()が255文字を超えるよう、あえて複雑なRangeを設定
    Dim rangeA As Range
    Set rangeA = Worksheets("SheetA").Range("A1:L13").SpecialCells(xlCellTypeBlanks)
    
    ' Rangeを可視化
    rangeA.Interior.ColorIndex = 3
    
    ' SheetBについてRange.addressを用いることでSheetAと同一のセルを取得    
    Dim rangeB As Range
    Set rangeB = Worksheets("SheetB").Range(rangeA.Address)
    
    ' Rangeを可視化    
    rangeB.Interior.ColorIndex = 3

結果がこうなる。

シートA

シートB

これは、rangeA.Address()の返り値が255文字以上の場合、切り捨てられた状態で返ってくるためである。

解決方法

要するにRanse.Adressの文字列が長すぎことが原因。一度分割したものを再度結合すればよい。
下記のメソッドを実装した。

' 指定したRangeと同範囲のRangeを別シートのものとして取得する
Function GetRangeAsOtherWorksheetsRange(fromRange As Range, _
                                        toWorksheet As Worksheet) As Range
    
    ' Copy先のワークシートのセルを初期値として設定
    Dim returnRange As Range
    Set returnRange = toWorksheet.Range(fromRange.Areas(1).Address)

    ' RangeをAreaごとに分割しUnionメソッドで結合する
    Dim r As Range
    For Each r In fromRange.Areas
        Set returnRange = Union(returnRange, toWorksheet.Range(r.Address))
    Next

    Set GetRangeAsOtherWorksheetsRange = returnRange
End Function

    ' Range.Address()が255文字を超えるよう、あえて複雑なRangeを設定
    Dim rangeA As Range
    Set rangeA = Worksheets("SheetA").Range("A1:L13").SpecialCells(xlCellTypeBlanks)
    
    ' Rangeを可視化
    rangeA.Interior.ColorIndex = 3
    
    ' SheetBについてRange.addressを用いることでSheetAと同一のセルを取得    
    Dim rangeB As Range
    Set rangeB = GetRangeAsOtherWorksheetsRange(rangeA, Worksheets("SheetB"))
    
    ' Rangeを可視化    
    rangeB.Interior.ColorIndex = 3

うまくいった。