[VBA] 連想配列について

質問

こちらの識者の方々にはいつもお世話になっています。
VBAの質問です。

環境は下記になります。
OS=windowsXP SP3
Office=Excel2003(11.8347.8403) SP3

データ量が多いのでVLOOKUPの処理を連想配列を使ってやりたいのですが、できなくて困っています。
言葉では説明しずらいので、やりたいことのコードを提示します。

:Sheet2
品名　　1月　　2月
りんご　2　　　3
ばなな　4　　　5

下記、VBAコード
----------------------------------------------------
Option Explicit

Sub test()
　Dim oDict As Object
　Dim Ary As Variant
　Dim EndRow As Long
　Dim i As Long

　Ary = Sheets("Sheet2").Range("A2:C3").Value
　Set oDict = CreateObject("Scripting.Dictionary")

　For i = 1 To UBound(Ary)
　　oDict(Ary(i, 1)) = Ary(i, 2)
　　oDict(Ary(i, 2)) = Ary(i, 3)
　Next i

　With ThisWorkbook.Sheets("Sheet1")
　　EndRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　Ary = .Range("A1:Y" & EndRow).Value
　　For i = 2 To EndRow
　　　Ary(i, 24) = oDict(Ary(i, 1))
　　　Ary(i, 25) = oDict(Ary(i, 2))
　　Next i
　　.Range("A1:Y" & EndRow) = Ary
　End With

　MsgBox "done"
End Sub
----------------------------------------------------
というコードなのですが、
　For i = 1 To UBound(Ary)
　　oDict(Ary(i, 1)) = Ary(i, 2)
　　oDict(Ary(i, 2)) = Ary(i, 3)
　Next i
が、oDict(Ary(i, 1)) = Ary(i, 2)だけ配列に格納される状態です。
指定の文字列(Key)に対応するItemを返すのが連想配列だと思うのですが、上記コードで、
oDict(Ary(1, 1))=2
oDict(Ary(2, 1))=4
oDict(Ary(1, 2))=3
oDict(Ary(2, 2))=5
という配列に格納されない理由がわかりません。
Scripting.Dictionaryで辞書に格納できるデータになにかしらの制限があるのでしょうか？

質問に不備不足等ございましたらご指摘ください。
ご面倒お掛けしますがよろしくお願いします。

回答No.2

keithin · ベストアンサー

連想配列では、キーと値は１対１です。そのうえで、アイデア次第でやりようは幾らでも考えられると思います。

たとえばアナタのマクロに「比較的」近い方法：
sub macro1()
　dim oDic as object
　dim a as variant
　dim i as long

　a = worksheets("Sheet2").range("A2:C3").value
　set odic = createobject("Scripting.Dictionary")

　for i = 1 to ubound(a)
　　odic(a(i, 1) & "_2") = a(i, 2)
　　odic(a(i, 1) & "_3") = a(i, 3)
　next i

　with worksheets("Sheet1")
　for i = 2 to 4
　　.cells(i, "X") = odic(.cells(i, "A") & "_2")
　　.cells(i, "Y") = odic(.cells(i, "A") & "_3")
　next i
　end with
end sub

たとえば：
sub macro2()
　dim oDic as object
　dim h as range
　dim i as long

　set odic = createobject("Scripting.Dictionary")

　for each h in worksheets("Sheet2").range("A2:A3")
　　odic(h.value) = h.offset(0, 1).resize(1, 2).value
　next

　with worksheets("Sheet1")
　for i = 2 to 4
　　.cells(i, "X").resize(1, 2).value = odic(.cells(i, "A").value)
　next i
　end with
end sub

お礼

私の言いたいことがものすごく分かり辛かったにもかかわらず、勘違いしているところと、やりたいことを予測し、的確なコードをご提示いただいてありがとうございます。
Keyとなる値に文字列を追加する方法と、Keyとなる値に対し、Itemを範囲で指定して格納する方法ですね。
目から鱗が落ちたとはまさにこのことです。
配列に取り込んで処理することと、書き出したいセルが隣り合っていないことも想定し、macro1の方法でやってみたいと思います。
ありがとうございました。

回答No.1

pringlez

>Scripting.Dictionaryで辞書に格納できるデータに
>なにかしらの制限があるのでしょうか？

…このコードだったら、そもそもそういう問題ではないでしょうね。
連想配列云々は全く関係ない気がするのですが。

おそらくは
Ary(1, 1) = 2
Ary(2, 1) = 4
Ary(1, 2) = 3
Ary(2, 2) = 5
だということが絶対的な前提なのですよね。

まず、それができているのかどうかを確かめましょう。

あなたの場合は、そもそもデバッグの仕方を覚えるところからはじめた方がいいと思います。デバッグの仕方はわかりますか？Debug.print などを使って中身の確認をしてみましょう。

お礼

仰る通り、debug.printでイミディエイトウィンドウを確認してみたところ、きちんと、
oDict(Ary(1, 1)) = 2
oDict(Ary(2, 1)) = 4
oDict(Ary(1, 2)) = 3
oDict(Ary(2, 2)) = 5
の値が格納されていました。
Sheet1のA列に
りんご
りんご
ばなな
ばなな
ばなな
りんご
のデータがあり、X列に
2
2
4
4
4
2
Y列に
3
3
5
5
5
3
を反映させたかったのですが、
Ary(i, 25) = oDict(Ary(i, 2))
は、ary(i,2)の値に対応したItemを返すので、ary(i,2)に仮に2の値が入っていれば3を返すという意味なんですね。
連想配列に対する理解が足りなかったことがわかりました。
きちんとデータは格納されていたにも関わらず、その取り出し方が間違っていたということですね。
ありがとうございました。