エクセルがともだち

会社で一番のともだちがエクセル・・・のアラフィフパート事務員のつれづれ帳。備忘録もかねてエクセルのことやVBAのことを書いていくつもりです。

【Excel/VBA】配列をループするとき配列の最後にダミーを入れると最後の処理がスマートになることがある

エクセルのシートをIDごとに切り分けて別ファイルで保存、とかしたりするのに切り分けキーの列を配列に入れて(入れなくてもいいんだけど、行数が多いときはいちいちセルにアクセスすると時間がかかるので、いったん配列に入れてから処理する)、開始行と終了行をディクショナリに登録したりする、ということをやっています。

 

たとえば、こんな感じの表を切り分けたいとします。

f:id:mwke:20210620163041p:plain
A列のIDで切り分けたい。

最初はid「1001」開始行は「1」終了行は「1」

次はid「1002」開始行は「2」終了行は「5」

・・・

最後はid「1005」開始行は「11」終了行は「12」

 

切り分けキーidと開始行、終了行を登録していくコードを書きました。

(ディクショナリを利用するために参照設定でMicrosoft Scripting Runtimeを事前に設定しています)

f:id:mwke:20210620164918p:plain

Sub test()

 'Variant型配列にセル範囲の値を代入
 Dim arr As Variant
 arr = Range("A1:A12")

 'ディクショナリを宣言。
 'キー値にid、値に配列で[開始インデックス、終了インデックス]代入
 Dim dic As Dictionary
 Set dic = New Dictionary

 '開始インデックス、終了インデックスを一時格納する変数
 Dim stridx As Long, endidx As Long
 '最初の開始インデックスを代入しておく
 stridx = 1

 '配列をループ。
 Dim i As Long
 For i = LBound(arr) To UBound(arr) - 1
  '次のIDと異なる場合、dicに格納する
  If arr(i, 1) <> arr(i + 1, 1) Then
   '終了インデックスをendidxに代入
   endidx = i

   'キー値ID、stridxとendidxを配列にしてdicに代入する
   dic.Add arr(i, 1), Array(stridx, endidx)

   '次のインデックスをstridxに入れておく
   stridx = i + 1

  End If
 Next i

 Stop

End Sub

 

Stopのところで止まったところでローカルウインドウを見てみると、

あら残念。最後のid「1005」がdicに登録されていません。

f:id:mwke:20210620163423p:plain

 

ループを抜けた後に、最後のidの値をdicに登録するコードを書き足しました。

*******************

'配列をループ。
Dim i As Long
For i = LBound(arr) To UBound(arr) - 1
  '次のIDと異なる場合、dicに格納する
 If arr(i, 1) <> arr(i + 1, 1) Then
  '終了インデックスをendidxに代入
  endidx = i

  'キー値ID、stridxとendidxを配列にして代入する
  dic.Add arr(i, 1), Array(stridx, endidx)

  '次のインデックスをstridxに入れておく
  stridx = i + 1

 End If
Next i

'最後の値をdicに代入する
dic.Add arr(UBound(arr), 1), Array(stridx, UBound(arr))

********************************

赤字部分が足したコードです。

これでOK。最後のid「1005」が入りました。

f:id:mwke:20210620163811p:plain

 

でも、なんかかっこ悪い。最後に残ったやつを入れるのがかっこ悪い気がする。

そうだ配列arrの最後にダミーidをいれれば、書き足さなくても配列ループだけで必要な情報がdicに入れられるのでは!

***************************

'1行多く配列に代入しておく
Dim arr As Variant
arr = Range("A1:A13")

'ダミーを配列の最後に代入
arr(13, 1) = 9999

***************************

ちなにみダミーの値を入れなくても1行多く配列に代入しておくだけでも大丈夫でした。

改良後のコード

********************************

'Variant型配列にセル範囲の値を代入
'1行多く配列に代入しておく
Dim arr As Variant
arr = Range("A1:A13")

'ダミーを配列の最後に代入
arr(13, 1) = 9999

'ディクショナリを宣言。
'キー値にid、値に配列で[開始インデックス、終了インデックス]代入
Dim dic As Dictionary
Set dic = New Dictionary

'開始インデックス、終了インデックスを一時格納する変数
Dim stridx As Long, endidx As Long
'最初の開始インデックスを代入しておく
stridx = 1

'配列をループ。
Dim i As Long
For i = LBound(arr) To UBound(arr) - 1
 '次のIDと異なる場合、dicに格納する
 If arr(i, 1) <> arr(i + 1, 1) Then
  '終了インデックスをendidxに代入
  endidx = i

  'キー値ID、stridxとendidxを配列にして代入する
  dic.Add arr(i, 1), Array(stridx, endidx)

  '次のインデックスをstridxに入れておく
  stridx = i + 1

 End If
Next i

Stop

********************************

これでオッケー。

開始行と最終行はインデックスを指定して取り出します。

f:id:mwke:20210620164556p:plain

ディクショナリのIDを開始行と最終行で切り出して、dicのキー値をファイル名にしてファイル分割したりしています。

 

【Excel/VBA】ループの中で変数の宣言をしても、ループするごとに変数が作成されるわけではない

ループの中で変数の宣言をしても、ループするごとに変数が作成されるわけではない

ということに、初めて気が付きました!

 

てっきり宣言部分を通過するときに、通過するごとに別の変数が作成され、メモリ領域がとられているものかと思っていました。

 

なぜ気が付いたかというと、ループの中の変数が、ひとつ前の値を引きずっていたからです。

 

たとえばですが、このような表があり、100点以上だったら変数bufに合格を代入します。100点未満だったらbufにはなにもいれません。

f:id:mwke:20210612160300p:plain

 

以下のようなコードを書きました。まったく意味のないコードですが・・・。

For分の中にbufを宣言するコードを書いており、その都度変数が初期化されるものと信じて書いています。最終的にbufが合格だったらメッセージボックスを表示するようにしています。

 

Dim i As Long
For i = 1 To 5
    Dim buf As String
    If Cells(i, 1).Value >= 100 Then
        buf = "合格"
    End If
Next i

If buf = "合格" Then
    MsgBox "おめでとうございます"
End If

最後の人は30点なので、bufは空っぽと信じていますが、実はbufには3行目の人の「合格」が入ったままになっており、メッセージボックスが表示されます。

 

デバックでステップ実行してみると、なんと!ステップインしたとたん、変数の宣言部分まで進んでもいないのに、ローカルウインドウには変数 i も buf もできちゃっています!

 

f:id:mwke:20210612161248p:plain

 

f:id:mwke:20210612161715p:plain

 

変数はプロシージャ内のどこに書こうと、最初に実行されちゃうんですね!

達人の皆様からしたら「え?知らなかったの?」と思われちゃうでしょうか、私は新発見だったので、忘れないように記しておくことにしました。

 

上記のような場合は都度変数を初期化しなければなりませんね。

 

Dim i As Long
For i = 1 To 5
    Dim buf As String

 buf = ""       '前の値を引きずらないように初期化!
    If Cells(i, 1).Value >= 100 Then
        buf = "合格"
    End If
Next i

 

初期化されないならわざわざループの中に書く必要もないですね

Dim i As Long, buf As String       '最初に持ってきた
For i = 1 To 5

 buf = ""       '前の値を引きずらないように初期化!
    If Cells(i, 1).Value >= 100 Then
        buf = "合格"
    End If
Next i

 

t-hom's diary さんのブログで勉強させていただきました。ありがとうございました。

VBA 変数宣言はIfやFor等のコードブロック中に書いても機能する - t-hom’s diary (hateblo.jp)

【Excel/VBA】続・ディクショナリに配列を持たせる セル範囲で値を渡す/おまけ・Functionの「代入式の左辺の関数呼び出しは、バリアント型・・・」のエラーメッセージ

 前に、ディクショナリの値に配列を持たせる、をやりました。

mwkexcel.hatenablog.com

その時に、値で持たせる配列を指定するときに、セル範囲でがばりと入れると二次元配列の形になるので扱いにくいということを書きました。

が、セル範囲で指定して、FUNCTIONで一次元配列にして返せばいいんだ、と思いつきました。

 

これが前に使った表

f:id:mwke:20210515122231p:plain

前に書いたコード

Dim dic As Dictionary
Set dic = New Dictionary

Dim i As Long, j As Long
With ActiveSheet
 For i = 2 To .Range("A1").End(xlDown).Row
  Dim v As Variant
  v = .Range(.Cells(i, 2), .Cells(i, 5)).Value
  dic.Add .Cells(i, 1).Value, v
 Next i
End With 

 

v = .Range(.Cells(i, 2), .Cells(i, 5)).Value

で、変数vに列BからEの一行分の値を入れて

 

dic.Add .Cells(i, 1).Value, v

で、ディクショナリに配列になっている変数vを入れます。

 

ここを直します。

 

Dim dic As Dictionary
Set dic = New Dictionary

Dim i As Long, j As Long
With ActiveSheet
 For i = 2 To .Range("A1").End(xlDown).Row
  Dim v() As Long
  v = getarr(.Range(.Cells(i, 2), .Cells(i, 5)).Value)
  dic.Add .Cells(i, 1).Value, v
 Next i
End With 

 

赤い部分を直しました。

配列を受け取る変数 v をVariantではなくLongにし、変数の後にかっこを付けました。

Function(あとで作成します)のgetarrの引数として列B~Eの一行分のセル範囲の値を渡しています。

 

Function部分のコードです。

 

Function getarr(ByVal values As Variant) As Long()
    
    Dim arr_() As Long       ①

    Dim i As Long         ②
    Dim idx As Long: idx = 0             ③


    For i = LBound(values, 2) To UBound(values, 2)    ④
        ReDim Preserve arr_(i)      ⑤
        arr_(idx) = values(1, i)     ⑥
        idx = idx + 1          ⑦
    Next i

    getarr = arr_          ⑧

End Function

 

①一時的に使用する変数arr_を宣言します。この変数arr_に配列用データを蓄積し、最後にgetarrに代入して配列をもとのプロシージャに返します。

②カウンタ変数iを宣言

③配列インデックスに使用する変数idxを宣言。初期値0を代入します

④配列の最初から最後までループ。ここで注意するのはただUbound(values)とすると1要素目の最後まで・・・となり、1回しかループが行われません。

UBound(values, 2) として、2要素目の最後までループするようにします。

f:id:mwke:20210606140755p:plain

 

⑤配列用変数arrをReDimで再宣言し、要素数を変更します。ReDimのあとにPreserveをつけないと、それまでに代入した値が消えてしまうので、ご注意を。(私はよく付け忘れます)

⑥変数arr_に配列valuesの値を代入します。caluesは二次元配列なので(1,i)と要素番号を指定します。

⑦配列のインデックス番号変数idxをカウントアップします

⑧最後にgetarrに配列arr_を代入します。元のプロシージャにリターンします。

 

 

実は、初歩的、すごくあほなミス、のようなのですが、このFunctionを書くときに失敗しています。

ミスったコードは以下

Function getarr(ByVal values As Variant) As Long()
    Dim idx As Long: idx = 0
    Dim i As Long
    For i = LBound(values, 2) To UBound(values, 2)
        ReDim Preserve getarr(idx)
        getarr(idx) = values(1, i)
        idx = idx + 1
    Next i

End Function

 

f:id:mwke:20210606141931p:plain

 

コンパイルエラー 代入式の左辺の関数呼び出しは、バリアント型またはオブジェクト型の値を返さなければなりません。」

というエラーメッセージが出ます。

エラーメッセージが何を言いたいのかさっぱりわかりませんが、どうやら値を返すのは最後の一回きりにしないといけない、ということかと思われます。

このコードだと、ループの中で

getarr(idx) = values(1, i)

と、何度も値を返すことになっているのかもしれません!

retern Value は最後の一回だけにしないとね!ということですかね。

おはずかしや。

 

 

 

【office365】二台目へのインストール

office365 business を利用しています。月額900円(たぶん税抜き)で最新のofficeが利用できるし、アクセスも入ってるし、なかなか良いです。

いつもはリビングにおいてあるHPのデスクトップパソコンを使っているのですが、テレワークでリビングから移動してやりたい(子供のユーチューブがうるさい)と思い、思い切ってマウスコンピューターのノートパソコンを購入しました。

mouseB5-i5というやつ。当時はセール対象品だったのですが、さっき見たら現在取り扱いしていないとでていた。在庫処分だったのかしら。使用感は問題ないです。

 

で、office365をどうやってインストールするんだっけ?とそこそこ悩んだので、備忘録として記録しておきたいと思います。

 

まず、officeのページにアクセスします。

Office 365 ログイン | Microsoft Office

 

右上のサイイインをクリックして、ID、パスワードを入力し、サイイインします。

IDは何だっけ?といつもなるのですが、

xxxxx@会社名.onmicrosoft.com

というやつです。office365購入時に作成したです。

xxxxはその時自分でつけたやつで、会社名の部分もその時に自分で作った適当な会社名です。

 

じつはここからどうしたか忘れてしまったのですが、たぶん左上の■のポチポチ部分をクリックして、「管理」というところを選択したら画面遷移し、「Offifeアプリ」というところが登場したのでそこで「インストール」というボタンがあり、ここをぽちっとしてインストールしたような。。。

f:id:mwke:20210530163413p:plain

ははは。ひどい説明ですな。。。

 

まあ、無事にインストールできてよかった。。。

 

【Excel/VBA】セル範囲に名前を付けた場合の扱い方法

エクセルでセル範囲(単一セルでもOK)に名前を定義すると、

Range(”名前”)といった様に決めておいたセル範囲を名前で扱うことができるようになります。

ある定型書式のセルに「担当者」欄とか「支店名」欄とか、名前を付けておくと、

書式の変更があってセルをずらした時など、セルがずれたから「Range(”A1”)をRange(B1”)に変更しなきゃ」とかしなくてもよい

定型書式のファイルを操作するマクロでもセルの名前を利用できるし、その定型書式ファイルを集計する別のマクロでもセルの定義された名前を利用し「担当者」欄の値を持ってきて~とか、利用できます。

でも、結合セルに名前を定義したときに、ClearContentsはできるのに、値を入力しようとするとエラーが出てできない、、、ということがあり、あれこれやった結果「配列 もしくは Rangeの範囲として扱えばいいんだ!」という結論にたどり着きました。

備忘録として残しておきます。

 

1.まず、セル範囲に名前を定義します。

リボン「数式」から「名前の定義」を選択します。

f:id:mwke:20210523103228p:plain

「名前」を入力します。「参照範囲」を適宜変更します。今回はB2:D2の結合セル範囲を指定しました。OKをクリックして終了です。

f:id:mwke:20210523103532p:plain

対象のセルをクリックすると、左上に先ほど定義した名前が表示さていることが確認できます。

f:id:mwke:20210523104017p:plain

※名前の定義の方法はほかにもありますので、これは一例です。

 

2.名前を定義したセルの操作

さて、この名前を定義された結合セルに

①値のクリア

②値の入力

③値を取得

という三つの操作をしてみます。

 

コードです。

 '①名前の定義「セル範囲」の値を消去
 Range("セル範囲").ClearContents

 '②名前の定義「セル範囲」に値を入力
 Range("セル範囲").Value = "かきくけこ"

 '③名前の定義「セル範囲」の値を取得してメッセージボックス表示
 Dim buf As String
 buf = Range("セル範囲").Value
 MsgBox buf

 

①成功。②成功。③でエラーが出てしまいました。

f:id:mwke:20210523105736p:plain

③のコードをこのように変えたらうまくいきました。

 buf = Range("セル範囲")(1).Value

「セル範囲の左から一つ目のセルの値」という指定の仕方。

Range(”セル範囲”)の後ろに「範囲の中のどこ」という指定をしてあげます。

そうするとうまくいきました。

f:id:mwke:20210523110027p:plain

 

3.表などのセル範囲に名前を定義した場合の操作方法

(1)表の範囲に名前を定義する

こんどは、表の範囲に名前を定義してみます。

名前を定義したいセル範囲を選択した状態でエクセルの左上の「名前ボックス」につけたい名前を入力します。これで名前の定義することができました。

今回はセルB4~D6に「セル範囲2」という名前を定義しました。

f:id:mwke:20210523110530p:plain

(2)まずは消去の書き方

範囲の全体の値を消去 

 '①名前の定義「セル範囲」の値を消去
 Range("セル範囲2").ClearContents

名前の定義「セル範囲」の一部分を消去する書き方
一か所指定

 '②上から2番目、左から2番目の値を消去
 Range("セル範囲2")(2, 2).ClearContents

一行指定

 '②上から1番の行の値を消去
 Range("セル範囲2").Rows(1).ClearContents

列指定
 '②左から3番の列の値を消去
 Range("セル範囲2").Columns(3).ClearContents

 

(3)値の入力の書き方

 '名前の定義「セル範囲」に値を入力
 Range("セル範囲2")(1, 1).Value = "あ"
 Range("セル範囲2")(1, 2).Value = "い"
 Range("セル範囲2")(1, 3).Value = "う"
 Range("セル範囲2")(2, 1).Value = "え"
 Range("セル範囲2")(2, 2).Value = "お"
 Range("セル範囲2")(2, 3).Value = "か"
 Range("セル範囲2")(3, 1).Value = "き"
 Range("セル範囲2")(3, 2).Value = "く"
 Range("セル範囲2")(3, 3).Value = "け"

f:id:mwke:20210523111317p:plain

(4)値の取得

同様に、範囲の「どの位置のセル」という指定の仕方をします。 

 Dim buf As String
 buf = Range("セル範囲2")(2,2).Value
 MsgBox buf

f:id:mwke:20210523112128p:plain

(5)値の取得。配列に入れてみる。

セル範囲の一番上の行の値を配列vに入れます。 

 Dim v As Variant
 v = Range("セル範囲2").Rows(1).Value


セル範囲の値をすべて配列v2に入れます。
 Dim v2 As Variant
 v2 = Range("セル範囲2").Value

 

ローカルウインドウで配列の中身を見てみます。

ちゃんと入ってますね。

f:id:mwke:20210523112543p:plain

 

最後までお読みいただきありがとうございました。

セル範囲についてはこちらでも扱っています。

 

mwkexcel.hatenablog.com

 

 

mwkexcel.hatenablog.com

 

【Excel/VBA】ディクショナリに配列を持たせる

VBAのディクショナリが好きです。

ということで、前回はキーと値をディクショナリにセットしていく方法を書きました。

 

mwkexcel.hatenablog.com

 

今回はディクショナリの値の部分に複数の値を入れていこうと思います。

たまにやろうとすると、あれ、どうやって配列の値を取り出すんだっけ???って毎回焦るので、この機会に整理。

 

値に配列を入れるやり方をやってみます。

こんな表があります。

IDがキーで、IDに対する各科目の点数を値として入れていきます。

f:id:mwke:20210515122231p:plain

(1)ディクショナリに配列を入れる

コードです。

①B~E列の数値を配列arrにいったん入れて、

Dim dic As Dictionary
Set dic = New Dictionary     '①

Dim i As Long, j As Long
With ActiveSheet
 For i = 2 To .Range("A1").End(xlDown).Row
  Dim arr(3) As Long      '②
  For j = 0 To 3
   arr(j) = .Cells(i, j + 2).Value
  Next j
  dic.Add .Cells(i, 1).Value, arr  '③
 Next i
End With

 

①ディクショナリの宣言。

参照設定をしているときの書き方です。

参照設定はVBAエディターで「ツール」→「参照設定」から、Microsoft Scripting Runtime にチェックを入れ、「OK」で設定します。

参照設定していないときは

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

このように書きます。

 

②B~E列の数値を配列arrにいったん入れています。

 

③ディクショナリdicにキー:A列のID、アイテム:配列arrを入れます。

 

(2)ディクショナリに入れた配列の値の取り出し方法

 dic(キー)(配列インデックス)

と書くと、キー値に対応する配列の指定されたインデックスの値が取り出せます。

もしくは

 変数=dic(キー)

とすると変数にdicのキー値に対応する配列を入れられます。変数はVariantにしておくか、変数() と配列の形にしておきます。

 

15行目以降にA列のIDに対応する点数を入れていきます。

f:id:mwke:20210516103006p:plain

インデックスを指定して出力する方法です。

Dim key As String
With ActiveSheet
 For i = 15 To 18
  key = .Cells(i, 1).Value
  For j = 0 To 3
   .Cells(i, j + 2).Value = dic(key)(j)   ’①
  Next j
 Next i
End With

①でインデックスを指定して取り出ししています。

 

いったん配列に入れてから出力する方法です。

 Dim key As String, arr1 As Variant
 With ActiveSheet
 For i = 15 To 18
  key = .Cells(i, 1).Value
  arr1 = dic(key)        ’②
  For j = 0 To 3
   .Cells(i, j + 2).Value = arr1(j)   ’③
  Next j
 Next i
 End With

②でいったん配列arr1に入れます。

③で配列の値を出力しています。

 

ちなみに、セルの範囲で配列に入れると、二次元配列の形になりちょっとうざいです。

Dim dic As Dictionary
Set dic = New Dictionary

Dim i As Long, j As Long
With ActiveSheet
 For i = 2 To .Range("A1").End(xlDown).Row
  Dim v As Variant
  v = .Range(.Cells(i, 2), .Cells(i, 5)).Value
  dic.Add .Cells(i, 1).Value, v
 Next i
End With

Dim arr As Variant
arr = dic("A01")

f:id:mwke:20210516103627p:plain

dic("A01")(1, 2) こうすると取り出せるけどいまいちうざい。

 

以上です。

最後までお読みいただきありがとうございました。

【Excel/VBA】ディクショナリって配列より素敵だと思う瞬間

突然ですが。ディクショナリが好きです。

キーを入れるだけで対応する値を取り出してくれるから。

配列だとキー値を探すのに最初からひとつずつチェックしないといけないのに比べてなんか簡単な気がするので。

ループせずとも一発であるかないかが判明するのが魅力。

 

ちなみにこの記事内ではディクショナリを使うために参照設定した場合の書き方をしています。

参照設定はVBAエディターで「ツール」→「参照設定」から、

Microsoft Scripting Runtime にチェックを入れ、「OK」で設定します。

f:id:mwke:20210508133911p:plain

 

参照設定したほうが、ディクショナリを使うときにメソッドやプロパティの入力候補が出てくるのでお勧めです。

 

例えば、生徒IDと点数の一覧表があったとします。

これを配列、ディクショナリにいれて、そこから指定した生徒IDの点数を取り出す、というのをやってみようと思います。配列VSディクショナリです。

f:id:mwke:20210508140220p:plain

まずは配列を使った方法。

これがコード全文

Sub test配列()
 Dim arr As Variant
 With ActiveSheet.Range("A1").CurrentRegion
  arr = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Value
 End With

 Dim i As Long
 For i = LBound(arr) To UBound(arr)
  If arr(i, 1) = "B03" Then
   Debug.Print arr(i, 1) & "の点数は" & arr(i, 2)
   Exit Sub
  End If
 Next i
End Sub

 

解説です。

配列に値を入れます。

Dim arr As Variant
With ActiveSheet.Range("A1").CurrentRegion
 arr = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Value
End With

 

セルA1から一続きのセル範囲について、一つ下にずらし、縦の長さを一つ小さくし、横の長さはそのまま、のセル範囲の値を配列に代入する。という書き方をしています。

分けて解説すると

セルA1の一続きのセル範囲について With ActiveSheet.Range("A1").CurrentRegion 

一つ下にずらす          .Offset(1, 0)

サイズ変更(セル範囲の縦の長さ-1、横の長さ(そのまま))

                 .Resize(.Rows.Count - 1, .Columns.Count)

 .Rows.Count でCurrentRegionの範囲の行数を取得しています。.Columns.Countは範囲の列数をカウントします。Rows、Columnsの前の . (ピリオド)を忘れずに!

f:id:mwke:20210508142421p:plain

本当に狙ったところを指定できているのだろうか。。。と不安な時は、デバックで一行ずつ実行するときに試しにselectしてみると指定範囲を確認できます
With ActiveSheet.Range("A1").CurrentRegion
 .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Select

End With

 

話がそれました。

さて、生徒IDがB03の人の点数をイミディエイトウィンドウに出してみたいと思います。配列を最初からループして「B03」かどうかを配列の値をひとつずつ判定していき、ヒットしたらイミディエイトウインドウに出力し、ループを抜けます。


Dim i As Long
For i = LBound(arr) To UBound(arr)     ’配列の最初から最後まで
 If arr(i, 1) = "B03" Then         'もし配列の値がB03だったら
  Debug.Print arr(i, 1) & "の点数は" & arr(i, 2)    ’出力
  Exit Sub                    'ループを抜ける
 End If
Next i

 

ディクショナリを使ったコード全文

Sub testDic()
 Dim dic As Dictionary
 Set dic = New Dictionary

 Dim i As Long
 With ActiveSheet
  For i = 2 To .Range("A1").End(xlDown).Row
   dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
  Next i
 End With

 Debug.Print "B03の点数は" & dic("B03")

End Sub

 

解説です。

ディクショナリを宣言し、Newで利用できるようにしています。

Dim dic As Dictionary
Set dic = New Dictionary

これは参照設定しているときの書き方です。

参照設定していない場合は以下の書き方にすればよいです。

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

 

2行目から一続きの最終行(.Range("A1").End(xlDown).Row で取得)までループして、ディクショナリにキーと値を入れています。おい、ループしてんじゃねえか、と自分で突っ込みたくなりました。

ディクショナリにキーと値を追加するのは

dic.add キー, 値

と書きます。

 Dim i As Long
 With ActiveSheet
  For i = 2 To .Range("A1").End(xlDown).Row
   dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
  Next i
 End With

 

ちなみにディクショナリはキーに重複を認めませんので、キーに重複がある場合、上の書き方だけではエラーとなります。エラー対応の書き方は後述します。

 

取り出しは配列に比べて簡単に書けます。

dic(キー)で値を取り出しできます。一つずつのチェックは必要ありません。

 Debug.Print "B03の点数は" & dic("B03")

 

さて、

こんどは同じシートの14行目以降に指定した生徒IDの点数を出力していこうと思います。

f:id:mwke:20210509114339p:plain

配列を使ったコード全文

Sub test配列2()
 Dim arr As Variant
 With ActiveSheet.Range("A1").CurrentRegion
  arr = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Value
 End With

 Dim vRow As Long, idx As Long
 With ActiveSheet
  For vRow = 14 To 17
   For idx = LBound(arr) To UBound(arr)
    If arr(idx, 1) = .Cells(vRow, 1).Value Then
     .Cells(vRow, 2).Value = arr(idx, 2)
     Exit For
    End If
   Next idx
  Next vRow
 End With
End Sub

 

配列に入れることろは最初と同じです。

そのあと、14行目以降、キー値をワークシートから取得し、対応する値を配列から探し、ワークシートに点数を出力します。ここが二重ループになり、ダルイな、と思ってしまうんですよね。

 

ディクショナリを使ったコードです。

Sub testDic2()
 Dim dic As Dictionary
 Set dic = New Dictionary

 Dim i As Long
 With ActiveSheet
  For i = 2 To .Range("A1").End(xlDown).Row
   dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
  Next i
 End With

 Dim vRow As Long
 With ActiveSheet
  For vRow = 14 To 17
   If dic.Exists(.Cells(vRow, 1).Value) Then
    .Cells(vRow, 2).Value = dic(.Cells(vRow, 1).Value)
   End If
  Next vRow
 End With
End Sub

 

元データをディクショナリに入れるところは最初と同じです。

そのあと、14行目以降、キー値をワークシートから取得し、対応する値をディクショナリから取り出すところがループ一つで済むので、ここが好きです。

エラー対応で

If dic.Exists(.Cells(vRow, 1).Value) Then

と、dicにキー値があった場合、としていますが、確実にキー値がある場合はexistsで存在を調べることをせずに

.Cells(vRow, 2).Value = dic(.Cells(vRow, 1).Value)

だけでもだいじょうぶです。

 

dic.Exists(キー) は ディクショナリにキー値があるかどうかを調べることができる書き方です。あればTrue、なければFalseを返します。

ディクショナリに入れるとき、ディクショナリから出力するときにエラーで止まらないようにするためには、Ifで存在を確認してからディクショナリに入れる、または値を取得するという対応をよくやります。入れるときはキーが重複しているとエラーで止まり、出力するときはキー値がディクショナリに存在していないとエラーで止まってしまうので。

 

キー値が二回目に登場するときには無視する場合の書き方の一例は以下の通りです。

    Dim i As Long
 With ActiveSheet
  For i = 2 To .Range("A1").End(xlDown).Row
   If Not dic.Exists(.Cells(i, 1).Value) THen

              dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
           End If 

       Next i
 End With

 

キー値が二回目に登場するときには同じキー値に値を足したりする書き方の一例は以下の通りです。

 Dim i As Long
 With ActiveSheet
  For i = 2 To .Range("A1").End(xlDown).Row
   If Not dic.Exists(.Cells(i, 1).Value) Then
    '初登場の場合はdicに追加

    dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
   Else
    '二回目以降の登場の場合は値を追加

    dic(.Cells(i, 1).Value) = dic(.Cells(i, 1).Value) + .Cells(i, 2).Value
   End If
  Next i
 End With   

 

 

A04が重複している例で、足しこみコードを実行してみました。

16行目のA04の点数は21+5=26が出力されました。

f:id:mwke:20210509121108p:plain

以上です。

配列かディクショナリかは。。。好みですかね、、、

最後までお読みいただきありがとうございました!