DictionaryのItemに格納したExcelのRangeは更新可能か？

以前の記事でDictionaryのItemに配列を格納して、後から更新しようとして出来なかったが、懲りずに今度はRangeを格納して試してみた。

z1000s.hatenablog.com

結論から言うと、今回は更新可能でした！！！

DictionaryのItemを更新することで、ワークシートの値も更新されます。
また、ワークシートの対象Rangeのセルの値を更新すると、DictionaryのItemを参照しても更新された値を取得できます。

サンプルコード

Public Sub dictionaryItemUpdate()

    Const TARGET_SHEET_NAME As String = "Sample"
    Const KEY_ROW           As Long = 2
    Const TARGET_COLUMNS    As Long = 6

    Dim dicRange    As Dictionary
    Dim sLineBefore As String
    Dim sLine       As String
    Dim i           As Long

    Set dicRange = New Dictionary

    With Worksheets(TARGET_SHEET_NAME)
        '現在のセルのデータを取得、表示用に結合
        For i = 1 To TARGET_COLUMNS
            sLineBefore = sLineBefore & vbTab & .Cells(KEY_ROW, i).Value
        Next i

        '現在のセルのデータを表示
        Debug.Print "基準となるセルデータ"
        Debug.Print Mid$(sLineBefore, 2) & vbCrLf

        '対象となるRangeをDictionaryに追加
        dicRange.Add 2, .Range(.Cells(KEY_ROW, 1), .Cells(KEY_ROW, TARGET_COLUMNS))

        'Dictionaryに格納されているRangeから、データを取得、表示用に結合
        For i = 1 To TARGET_COLUMNS
            sLine = sLine & vbTab & dicRange(KEY_ROW).Parent.Cells(KEY_ROW, i).Value
        Next i

        'Dictionaryに格納されているデータを表示
        Debug.Print "Dictionaryに格納されているデータ（更新前）"
        Debug.Print Mid$(sLine, 2) & vbCrLf

        '格納したRange内のセルの値を更新
        Debug.Print "ワークシートの値を更新"
        Debug.Print "C2(更新前):" & .Cells(KEY_ROW, 3).Value
        .Cells(2, 3).Value = "----- " & .Cells(KEY_ROW, 3).Value & " -----"
        Debug.Print "C2(更新後) :" & .Cells(KEY_ROW, 3).Value & vbCrLf

        'DictionaryのItemを更新
        Debug.Print "DictionaryのItemを更新"
        Debug.Print "D2セル相当の値を更新（更新前）:" & dicRange(KEY_ROW).Parent.Cells(KEY_ROW, 4).Value
        dicRange(KEY_ROW).Parent.Cells(KEY_ROW, 4).Value = "+++++ " & dicRange(KEY_ROW).Parent.Cells(KEY_ROW, 4).Value & " +++++"
        Debug.Print "D2セル相当の値を更新（更新後）:" & dicRange(KEY_ROW).Parent.Cells(KEY_ROW, 4).Value & vbCrLf

        sLine = ""

        'Dictionaryに格納されているRangeから、データを取得、表示用に結合
        For i = 1 To TARGET_COLUMNS
            sLine = sLine & vbTab & dicRange(KEY_ROW).Parent.Cells(KEY_ROW, i).Value
'            sLine = sLine & vbTab & dicRange(KEY_ROW).Cells(1, i).Value
        Next i

        Debug.Print "Dictionaryに格納されているデータ（更新後）"
        Debug.Print "更新前:" & Mid$(sLineBefore, 2)
        Debug.Print "更新後:" & Mid$(sLine, 2)

        sLine = ""

        '最終のセルのデータを取得、表示用に結合
        For i = 1 To TARGET_COLUMNS
            sLine = sLine & vbTab & .Cells(KEY_ROW, i).Value
        Next i

        '最終のセルのデータを表示
        Debug.Print "最終のセルデータ"
        Debug.Print Mid$(sLine, 2) & vbCrLf
    End With

End Sub

サンプルコードを試すときは、参照設定忘れると動きませんので・・・

実行結果

call dictionaryItemUpdate
基準となるセルデータ
1 4820831 1256948 AFPKAUNYAI gKoo0fpjSf ゑたさえんぐそつぢろ

Dictionaryに格納されているデータ（更新前）
1 4820831 1256948 AFPKAUNYAI gKoo0fpjSf ゑたさえんぐそつぢろ

ワークシートの値を変更
C2(更新前):1256948
C2(更新後) :----- 1256948 -----

DictionaryのItemを更新
D2セル相当の値を更新（更新前）:AFPKAUNYAI
D2セル相当の値を更新（更新後）:+++++ AFPKAUNYAI +++++

Dictionaryに格納されているデータ（更新後）
更新前:1 4820831 1256948 AFPKAUNYAI gKoo0fpjSf ゑたさえんぐそつぢろ
更新後:1 4820831 ----- 1256948 ----- +++++ AFPKAUNYAI +++++ gKoo0fpjSf ゑたさえんぐそつぢろ
最終のセルデータ
1 4820831 ----- 1256948 ----- +++++ AFPKAUNYAI +++++ gKoo0fpjSf ゑたさえんぐそつぢろ

ポイント

ItemとしてRangeを格納したので、dicRange(KEY_ROW)がRangeを返します。
なので、セルの値は、

dicRange(KEY_ROW).Parent.Cells(KEY_ROW, i).Value

のように取得していますが、Parentの有無でCellsのアドレス指定が変わってきます。
今回の例でC2のセルの値を取得したい場合、

Parentがある場合	dicRange(KEY_ROW).Parent.Cells(2, 3).Value
Parentがない場合	dicRange(KEY_ROW).Cells(1, 3).Value

と指定する必要があります。
Parentがある場合には、普段使用している行、列の指定となります。
Rangeに対するParent（親）なので、ParentはWorksheetとなるためです。

? typename(dicRange(KEY_ROW).Parent)
Worksheet

として確かめることが出来ます。

一方Parentがない場合には、
ワークシート全体で見れば２行目のセルですが、Dictionaryに格納したRangeの左上のセル（A2：ワークシート基準でのCells(2,1)）を１行目、１列目とみなした相対的な行、列として指定する必要があります。従って、同じ２行目なので行を「1」としなければいけません。

今回のサンプルでは、A列から始まるRangeを使用したのですが、B列以降から始まるRangeを指定した場合には、列であっても同様のことが言えます。

Parentの有無、どちらの方法でも出来ますので、どのような処理を行うかによって使い分ければよいかと思います。