月: 2015年12月

追記: 改善版です！

• 【Excel VBA】【改善】フォーマットワークシートに指定した変数に対応する場所に書き込むクラスのリファクタリング・機能追加いたしました！ – oki2a24
• 【Excel VBA】【改善2】座標でなく、フォーマットシートに設定したキーに対応する場所に書き込むクラスの仕様変更とリファクタリング♪ – oki2a24

なぜ実現したかったのか？

• エクセル方眼紙での帳票は、ちょっとデザイン変更すると簡単に絶対座標がずれてしまい、VBA の座標修正が辛い。
• ExcelCreator のように、ワークシートに変数として入力したセルの内容を書き換えて出力したら楽になると考えた。
  • 製品情報 – ExcelCreator 2012 | アドバンスソフトウェア株式会社
  • “A1 参照形式”、”座標形式”、”セルの名前”、”変数名”の 4 つの方法で値を差し込むセルの指定が可能。”変数名”での差し込みを目指したのが本投稿
• セルの座標を気にする必要がなくなることがメリット
  • テンプレートのワークシートのデータを反映したいセルに変数を書き込めば良い。座標は気にしなくて良い。
  • VBA ではセルの変数に対してデータを差し込めば良い。座標を指定しなくて良い。
  • セルの名前を使う方法もある。ただし、セルの名前は扱いやすい形でワークシートに表示することができず、辛い。

処理の流れ

ワークシートテンプレートのセルに記入する変数と、その座標と、VBA に書くワークシートテンプレートの変数名と、代入する値をどうやって紐付けるかが重要です。

次に挙げるポイントの考えでつなげるようにいたしました。

ポイント

• テンプレートの変数をキーに、そこに対応するワークシートの座標を値にして VBA で取り込んでディクショナリ 1 を作る。
• テンプレートの変数をキーに、そこに当てはめる値を値にしてのディクショナリ 2 を作る。
• ディクショナリ 2 をループして、テンプレートの変数を仲立ちにして、変数に当てはめる値と、ワークシートの場所をひも付け。
• ワークシートへの書き込みは、2次元配列を Range に代入して 1 回で行う。速さを保つ。

もう少し具体的にしますと、次のようになります。

VBA コード

• CollectionToArrary の返却値は Variant だが、本当はどんな型なのか限定させたかった。。。
• 汎用性を考慮して、また、Arrary(“い”, “ろ”, “は”) などで配列を生成するときは Variant の変数とすることを考慮して、CollectionToArrary の返却値は Variant に決定した。
• ループ内で ReDim して配列サイズを毎回変更することはしないようにした。
• 要素を回すのに、元となるコレクションをループする方法と、作られる配列をループする方法とがある。今回、コレクションをループする方法を採った。
  • コレクションのインデックスは必ず 1 から始まる。配列のインデックスの開始は配列の定義時に自由に設定できる。
  • 配列をループしてそのインデックスに対応するコレクションのインデックスを計算して取り出すよりも、コレクションをループして配列の開始インデックスをインクリメントしていく形で要素を詰め込んでいく方がシンプルに書ける。
  • よって、ループする前に LBound で配列の最低のインデックスを取得して変数に代入しておき、ループ内の最後で変数を + 1 することで配列の添字を動かしていく。