カテゴリー: Microsoft

このクラスでできること

何種類もの表に対して、定期的に、同じ内容の編集を何度も繰り返し行う場合に役立つと存じます。同じ手作業を繰り返し行わなくて済むようになりますので、楽ができますの♪

行

• 指定行の除去
• 指定行の抽出
• 指定した列内で、値の重複している行を削除
• 指定した列内で、指定した値に一致した行を抽出します。
• 値の一致した行を削除
• 下側に 2 次元配列を追加

列

• 指定列の抽出(列の入れ替え)
• 指定した数分の列を最後に追加
• 列を最後に追加し、指定文字列を挿入
• 列を別の列へコピー
• 指定した関数を、列のすべての要素に適用
• 列の値を更新
• 右側に 2 次元配列を追加 (指定した 2 次元目が一致する条件で)
• キー、アイテムとなる列を指定して生成したディクショナリを返す。
• 列を 1 次元配列として返す。
• 列を 引数の 1 次元配列で更新

一方で、一度しか行わないのでしたら、本投稿のコードを使う必要性はない、という場合も多いかと存じます。

使うときのポイント

• シートへの反映は、Range(Cells(起点行, 起点列), Cells(RangeValue.RowCount, RangeValue.ColCount)) のように起点となる行番号・列番号を指定すれば自動的にサイズを調整して反映するので楽。
• 終点には RangeValue.RowCount・RangeValue.ColCount を指定するだけでよい。
• MapCol 関数、MapColWithObj 関数はとても処理が遅い。数百ならば問題なかったが、数千処理させたら 1 分以上かかった。なお、配列操作で代替すると処理時間は 1 秒未満だった。

Excel VBA には map reduce がございません。使いたいです (使いこなせているかどうかは別として)！これらのページ等を参考にすればできそうですの♪

• Excel VBA を学ぶなら moug モーグ | 即効テクニック | 指定されたオブジェクトのメソッドの実行/プロパティの値の取得/設定を行う(CallByName関数)
• いげ太の日記: [VBA] 長い長い FizzBuzz
• VBAHaskell ユーティリティを使ってみる – mmYYmmdd’s blog

map するためには、対象となる集合を用意する必要がございます。Excel VBA ですと配列、特に 2 次元配列を使うことが多いように感じます。これを map にかけるのは、、、なんだか難しそうですのでまずはコレクションで試してみたいですわ。

そのために、まずはコレクションを気軽に作れるようにしたいですわ♪そのようなわけで、どんな配列でもコレクションにする関数を書いてみましたの！

ポイント

• StrConv(string, vbWide) では半角英数記号も全角へ変換されてしまうため一工夫必要
• CreateObject(“VBScript.RegExp”) を使用して正規表現で半角カタカナを抽出する。
• 正規表現のヒット結果の集合と Replace 関数を用いて次の順番で処理する。
  1. 正規表現にヒットした部分をループし、
  2. ヒットした部分を全角へ変換した文字列を用意し、
  3. 変換対象文字列に対してヒットした部分を、全角変換後の文字列へ置換する。

仕様。何ができるクラスなの？

• 検索文字列は複数渡すことができる。
• 検索は完全一致、部分一致の両方に対応する。
• 結果は A1 参照形式、R1C1 参照形式のどちらでも得られる。例えばこんなイメージ。
  • A1 参照形式: (“A1”, “B3”, “C6”)
  • R1C1 参照形式: ((1,1), (2,3), (3,6))

ポイント

• 配列の要素数を意識しないで、かつ、インデックスを取得してループする。
• LBound でインデックスの最小値を、UBound でインデックスの最大値を取得する。
• LBound、UBound 関数で n 次元目のインデックスを取得するには 2 つ目の引数に n を指定する。
  • 1 次元目: LBound(values, 1) UBound(values, 1)
  • 2 次元目: LBound(values, 2) UBound(values, 2)

ポイント

• 対象範囲セルが 1 セルのみの場合、Range.Value は配列にならない。よってこの場合だけ別に処理をする。

ポイント

• 2次元配列から1次元目の要素 (配列) を取り出すことはできない。
• Variant を使用して、擬似的なジャグ配列を作成すれば可能
• 多次元配列とジャグ配列は異なり、同様に扱うことができない。
• 例えば、Range にジャグ配列を代入しても、シートにジャグ配列の要素は反映されない。

具体例

Excel 2013 VBA でのディクショナリ注意点！！

• ディクショナリのキー: 整数または文字列の使用が一般的だが、配列以外の任意の形式をとれる。
• ディクショナリの値: クラスをはじめ、なんでも入る、はず。
• イミディエイト ウィンドウにディクショナリオブジェクトをウォッチ表示したとき、確認できるのはキーのみ。
• イミディエイト ウィンドウでディクショナリのアイテムを一覧表示したいときは、ウォッチ式として dictionary.items を追加すること。
• ループでディクショナリのアイテムを取り出すには、dictionary.items を回す こと。dictionary を回そうとすると次のエラーとなった。
  [実行時エラー ‘424’: オブジェクトが必要です。]
• 同様に、ループでディクショナリのキーを取り出すには dictionary.keys を回せばよい、はず(未確認)。
• Add 関数の使い方。※1 の場合はキーが重複していてもエラーとならない。上書きする。
  • dictionary.Add "key", "item"
  • dictionary.Add key:="key, Item:="item"
  • Call dictionary.Add("key", "item")
  • Call dictionary.Add(key:="key", Item:="item")
  • dictionary("key") = "item" ※1
  • Set dictionary("key") = itemObject ※1

コードのポイント

• CSV データは一時作業用のエクセルファイルを新規作成し、そこにインポートする。このエクセルは最後に保存しないで削除する。
• QueryTables でインポートしたデータ範囲には、Name をあらかじめ指定できる。これを Range 関数で指定してデータを取得する。