[[20201109184945]]　『VBA セル範囲をDictionary配列へ格納』（れんこん）　ページの最後に飛ぶ

『VBA セル範囲をDictionary配列へ格納』（れんこん）

いつもお世話になっております。

VBAで特定のセル範囲をDictionary配列へ格納さようとしていますが、
エラーが出てしまいます。

セル範囲は2列で、行数は可変です。

Sub test()

    Dim AllStore, Store As Variant
    Dim AllStoreSt As Variant
    Dim AllStoreAry As Object

    'Dictionary起動
    Set AllStoreAry = CreateObject("Scripting.Dictionary")

    'シート設定
    Set AllStoreSt = Sheets("店舗一覧")

    '店舗一覧範囲選択　※タイトルは抜きで A2～A最終行 までを選択
    AllStore = AllStoreSt.Range(Cells(2.1), Cells(Rows.Count, 1).End(xlUp))

    '配列に格納
    For Each Store In AllStore

            If Not AllStoreAry.Exists(Store) Then

                AllStoreAry.Add Store, Store.Offset(0, 1)　'※※※

            End If

    Next

End Sub

※※※の部分で「オブジェクトがありません」と出てしまいます。
変数:Storeは範囲の中のセルを示しているのでオブジェクトはあると思うのですが....

まだまだ勉強をしだしたばかりで、簡単なことかもしれませんが、
どなたかわかる方教えて頂けませんでしょうか？

よろしくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

ぱっと見ですが。

 Dim AllStore, Store As Variant

 ↓ではどうなりますか？

 Dim AllStore AS Range, Store As Range

（OK） 2020/11/09(月) 19:09

 >Dim AllStoreSt As Variant

 これも

 Dim AllStoreSt As Worksheet
（OK） 2020/11/09(月) 19:10

 >AllStore = AllStoreSt.Range(Cells(2.1), Cells(Rows.Count, 1).End(xlUp))
 これを
 Set AllStore = Range(AllStoreSt.Cells(2, 1), AllStoreSt.Cells(Rows.Count, 1).End(xlUp))
 こうする

 ・Set しないで代入すると配列になります。配列要素 Store はRangeオブジェクトじゃありません。
 ・Cells(2.1) じゃなくて、Cells(2, 1)
 ・Cellsの親オブジェクト指定しましょう。指定しないとActiveSheetです 
（´・ω・`） 2020/11/09(月) 19:15

 因みにですが・・

  ＞AllStoreSt.Range(Cells(2.1), Cells(Rows.Count, 1).End(xlUp))
  　　　　　　　　　 ~~~~~~~↑~
  　　　　　　　　　　　　 ここピリオドになっています。 大丈夫？

  DictionaryのキーにRangeオブジェクトを採用することはちょっと考えられないです。 大丈夫？

（半平太） 2020/11/09(月) 19:44

 >DictionaryのキーにRangeオブジェクト
 Rangeオブジェクトそのものがキーではなくて、デフォルトプロパティでしょう
（´・ω・`） 2020/11/09(月) 20:33

横から失礼します。
　
ああ、デフォルトプロパティと思うかも知れませんが、
オブジェクトになりますね。
.Valueなどの補完はしてくれません。
　
同じ値を持つ二つのセルについて実験してみると、
セルのアドレスが違うので、キーが二つできることがわかります。

（γ） 2020/11/09(月) 20:46

 そうなんですか、知らなかったというか、
 よく調べもせず、軽率でした。すみません
 一つ知識がふえました
（´・ω・`） 2020/11/09(月) 20:56

 ＞セルのアドレスが違うので、キーが二つできることがわかります。

 同じセルでも、２回Addができます。キーも二つになります。

    AllStoreAry.Add Range("A1"), Range("B1")
    AllStoreAry.Add Range("A1"), Range("B1")

 どうやって呼び出すのやら、まったく分かりません。
（Rangeオブジェクトをキーにすると使い道がないのではないかと思います）

 ※これは、昔、γさんに啓発されて調べたものです。

（半平太） 2020/11/09(月) 21:05

う、しまった。すっかり忘れていました。一つでもよかったのですね。
私も、Rangeオブジェクトをキーにする例は見たことがないですね。

（γ） 2020/11/09(月) 21:20

（OK）　　　　さん
（´・ω・`）さん
（半平太）さん
（γ）さん

コメントありがとうございます。

まずは皆さんから指摘ありました、Cells(2.1)のピリオドはすぐ直しました...
凡ミスですみません...

まずは、やりたい事が最終的にできたコードを載せます。

'変数格納
Dim AllStore, Store As Variant
Dim AllStoreSt As Variant
Dim AllStoreAry As Object

'Dictionary有効化
Set AllStoreAry = CreateObject("Scripting.Dictionary")

'シート設定
Set AllStoreSt = Sheets("店舗一覧")

#1'店舗一覧範囲選択　※タイトルは抜きで A2～A最終行までを選択
Set AllStore = AllStoreSt.Range(AllStoreSt.Cells(2, 1), AllStoreSt.Cells(Rows.Count, 1).End(xlUp))

#2'連想配列に格納　※Offsetにより 1列目と2列目を格納
For Each Store In AllStore

        If Not AllStoreAry.Exists(Store.Value) Then

            AllStoreAry.Add Store.Value, Store.Offset(0, 1).Value

        End If

''''''''''''''''テスト用 ※ここからは質問にはなかったコードです※

'対象のキーを削除

 AllStoreAry.Remove Range("A2").Value

'配列に残ったものをアクティブセルに出力
For j = 0 To AllStoreAry.Count - 1

    Cells(j + 2, 5).Value = AllStoreAry.Keys()(j)
    Cells(j + 2, 6).Value = AllStoreAry.items()(j)
Next j

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

#1にあるように、Set で変数設定しました
#2では Store オブジェクトの値を .Value で取得しました

変数　= セル範囲　で変数に配列が格納されることを勉強したので、Offsetを使って
セル範囲の横の列もついでに配列に格納しちゃおう！
なんだったらついでに連想配列にしちゃおう！

理解した今見るとちんぷんかんぷんですが、当初は上記の事ができると思ってしまってました..

皆様から意見いただきまして、「Set 変数　= セル範囲」とし変数にセル範囲をいれて、

For Each で「セル範囲の一つ Store」を Store.Value で存在確認して、

存在しなければ Store.Value でキー格納 Store.Offset(0, 1).Value で横の列をアイテム格納

という形にしました。

テスト用は連想配列に格納後、やりたい事を簡略化したコードです。
一連の流れでこのコードもうまく動きました。

皆さんありがとうございました！！！
（れんこん） 2020/11/10(火) 11:47

（OK）さん！
返信のコードには変数の型直ってませんが、無視したわけではありません...

すみません

ちゃんと型を宣言するようにします....
（れんこん） 2020/11/10(火) 11:50

 細かい話で恐縮です。
 (1)
 Dim AllStoreSt As Variant
 は、As Worksheetと限定したほうがよいと思います。（触れられていますね。番号狂うのでそのまま行きます）
 (2)
 AllStoreAryというのは、配列から出発した経緯によるんでしょうけど、
 出来上がってみると、dictionaryということが不明なので、
 ネーミングを再考されたほうがよいかもしれない。
 (3)
 If Not AllStoreAry.Exists(Store.Value) Then
      AllStoreAry.Add Store.Value, Store.Offset(0, 1).Value
 End If
 についてですが、A列に重複がなければ、単に、
 AllStoreAry(Store.Value) = Store.Offset(0, 1).Value
 でもよいかと思います。
 既存のキーを調査するコストを省けます。コードもスッキリするかも。

 (なお、重複がある場合、今のコードは最初のデータだけが作成され、
  上記コードでは、最後のデータだけが作成されます。
  重複チェックが目的なら、重複した旨のメッセージが必要でしょう。）

 (4)
     For j = 0 To AllStoreAry.Count - 1
         Cells(j + 2, 5).Value = AllStoreAry.Keys()(j)
         Cells(j + 2, 6).Value = AllStoreAry.items()(j)
     Next j
 は
     Cells(2, 5).Resize(AllStoreAry.Count, 1) = Application.Transpose(AllStoreAry.Keys)
     Cells(2, 6).Resize(AllStoreAry.Count, 1) = Application.Transpose(AllStoreAry.Items)
 などとも書けます。ご参考まで。
 AllStoreAry.Keys()(j)という書き方がトリッキーな印象。
 Transposeも唐突と言えば唐突で、いい勝負かもしれないが。
（γ） 2020/11/10(火) 12:09

(4)のところは、多数のデータの場合、一セル毎に書き出すと遅いので、
配列のまま書き込んだ方がよい、ということで、
提示の書き方が割と多いと思います。
（γ） 2020/11/10(火) 12:14

 途中から参戦且つ本題とは別で、すみませんが
 ＞    For j = 0 To AllStoreAry.Count - 1
 ＞        Cells(j + 2, 5).Value = AllStoreAry.Keys()(j)
 ＞        Cells(j + 2, 6).Value = AllStoreAry.items()(j)
 ＞    Next j
 こちらの運用は、γさんが指摘されている通りに是非直していただいたほうがよろしいかと。
 理由は以前検証したことがあったので、かなり無駄なことしております。
 特に「Keys()(j)」「items()(j)」この部分

[[20200811090110]]　『「Dictionary」からの取出し』（T20）　

 ループのたびに、本を１ページずつ最後までコピーして、
 コピーした本の１ページだけを読みに行くようなもので、
 直接本のページを開けばいいじゃないかというコードになってます。
（稲葉） 2020/11/10(火) 12:44

ああ、超遅いのでしたね。
朧気ながら記憶がありますが、恐ろしく記憶容量が減少していますな。これはヤバイ。

（γ） 2020/11/10(火) 14:31

コメント返信：

[ 一覧(最新更新順) ]

[[20201109184945]] 『VBA セル範囲をDictionary配列へ格納』（れんこん） ページの最後に飛ぶ

[[20201109184945]]　『VBA セル範囲をDictionary配列へ格納』（れんこん）　ページの最後に飛ぶ