Excel 私のマクロ作成・黄金パターン(2)マクロの記述

Excel マクロ、VBA

スポンサーリンク

Excel 私のマクロ作成・黄金パターン(1)下準備

2019年1月30日
前回の記事では、Excelブックにマクロを組み込むに当たって、下準備といえる段階のことを書いていった。

ここからは、実際にExcelにマクロを書いていく手順を示す。

前回作成した、VBEの「モジュール」というやつには、「変数の宣言を強制する」という設定がされていれば「Option Explicit」という文言が自動で入る。

この文字は消さず、これの後に続けてソースコードを書いていく。

ソースコードのひな形的なもの

では、私が書いていくマクロのソースコードのパターンは、だいたい次のようなものだ。
後で解説も書くし、長ったらしいからサラッと見てもらえれば良いが。

処理の流れ概説

ソースコードで実施されている処理の流れを、改めて文章で書いてみる。

処理の流れ
  1. 最初にメッセージボックスMsgBoxで、実行確認をする。OKなら、以降に進んでいく。
  2. まずはExcelシートのセルなどは極力いじらず、配列とか変数とかを内部的に計算していくだけの処理をしていく。
    この内部処理に留まっているうちは、もし失敗してもExcelシートに被害は無いからね。
  3. 内部処理が終わったら、メインシートwsを別の新規ブックにコピー。
    ws.Copy‘新規ブックにシートをコピー
    の部分ね。
  4. メインシートwsを新規ブックにコピーしたコピーシートnewWsは、もはやどこにも保存されていないので、単なる使い捨てシートとして扱うことができる。
    メインシートws自体の方は決して変更せず、コピーシートnewWsの方に、内部処理で求めた値などをセットしていく。

といった感じだ。

2次元配列を使うことも多い

ちょっと難しい、というか中級者以上向けの話。
上記で<内部処理>と書いてあるプロセスでは、あれこれ計算して2次元配列というやつを生成することが多い。

それをセルに代入する処理をするんだけど、ソースコードでは

    newWs.Cells(3, 1).Resize(rSize,cSize).Value = arr

の部分ね。

特に大量のセルに値をセットする場合は、全部その2次元配列というやつに値を格納しておいて一気に放出・セルに代入するほうが、遥かに高速だ。

それに、先述のようにセルそのものをいじらず配列っていう内部の話だけで済ませておけば、無用なトラブルを防げるしね。

セルそのものをいじるのは、ラストでこの2次元配列を代入するとき1回限りにしたいものだ。

この2次元配列の値をセルに代入するっていうのは、Excel VBAの高速化においては必ず取り上げられる手法だが、私が考えるに皆そこまでこのテクを徹底利用していないと思う。

私は「配列を制する者はExcel VBAを制す」くらいに思っているくらいだ、・・・とまあそんな話はここではこのくらいにしておく。

処理が終わったらボタンを削除する

そして、上手い人でも割と知らない部分だと思うが、マクロ実行ボタンを削除する処理をソースコードの終盤に入れている。

    ‘wsには処理実行ボタンを付けているが、それがwsをコピーしたnewWsに残っている。
    ‘それをButtons.Deleteで全て削除する。

    newWs.Buttons.Delete

の部分だ。

マクロの記述(コーディングともいう)を実際にやる人には知っておいてもらいたいことだが、マクロを書くに当たっては入力補完機能に頼ることができる。

たとえば「appl」まで文字を打ったところでショートカットキーCtrl + Spaceを打てば、入力補完が働いて「Application」まで単語が完成されてくれるわけだ。

しかしこのnewWs.Buttonsという記述には、この入力補完が働いてくれない。

それもあってこの処理は、割とマイナーじゃないだろうか。

そして、ソースコードのコメントにも書いてあるが、この処理はコピーシートnewWsにこびり付いて残ったマクロ起動ボタンを削除する処理だ。

これにより、コピーシートは、マクロの痕跡も残さず単なる独立したシートとして切り離せるわけだ。

ひとまずここまでが、私がマクロ入りExcelファイルを作る流れの、大まかなパターンだ。

まあ、マクロを普通に勉強していけば、大体の事項は自分で流儀ができていくと思うけどね。

最近はTwitterがメインになってて久しぶりにブログ記事を書いたけど、思ったより長くなってしまった。

スポンサーリンク