Excel VBAに手を出してみる

最近Excelでデータ管理することが増えてきた。自動化した方が早いよなーと思うことも多々あり、VBAのスキルがあったら何かと捗るので勉強してみる(ここでトライしたことは、いずれはPythonでソフトウェア化したいなーなんて思っている)。VBA、開発環境にはExcelにデフォルトで入っているVBE(Visual Basic Editor)を使用すれば良いらしい。うん、導入からして楽!!

本題に入る前にずっと気になっていたことがあって、それはVBAとVB(Microsoft Visual Basic)との関係。言語仕様など同じなのだろうか?調べた結果、VBAとはVisualBasic for Applicationsの略で、特定の用途(Application)に特化したVBということらしい。言うなれば、VBをカスタマイズしたもの。例えばExcelVBAならExcelシートにアクセスするための機能が盛り込んであり、AccessVBAならテーブルを扱うための機能が盛り込んである。このようにVBをカスタマイズすることで、ユーザがすぐに使えるようにしてあるらしい。おそらくExcelVBAはVB+αというわけではなく、VBではできたことの一部ができなくなっていることもありそうだ。VBなんて使えたら最高に格好いいのでいつか手を出したい。

Excelの初期設定

はじめに、エクセルVBA超入門!たった10分でスタート地点に立つための方法を参考に初期設定を行った。実施項目は以下。

  • 開発タブをリボンに表示した
    ファイルタブ>オプション>リボンのユーザー設定>メインタブ>開発にチェック
  • 変数宣言を強制にした
    デフォルトのVBAでは変数宣言が強制でないらしいが、これはバグの原因になるため強制に切り替えておく。開発タブからVBEを起動し、ツールタブ>オプション>編集>変数の宣言を強制するにチェック
  • 自動構文チェックを停止した
    これが動作していると構文エラーの度にダイアログが出てきて煩わしいらしい。停止しても構文エラーのハイライトは残るため停止しておく。ツールタブ>オプション>編集>自動構文チェックのチェックを外す

やってみたいこと

ものすごーく単純なデータ処理。随時記載。

指定の列に対して、数値のある最終行アドレスを出力する

今回の用途では処理対象のセル範囲が不定の場合がままあるため、まず必要となる処理。

準備

まず、挿入タブ>標準モジュールからコードウインドウを開く。ここに振る舞いを記載して、裏のスプレッドシートで動作を確認できるようだ。また表示タブ>イミディエイトウィンドウを有効にするとデバックコンソールのようなものが出てくる。Cで言うprintf()のような標準出力関数で変数の中身を表示できたり、デバッグ用途で使えるものらしい。

言語仕様など何も知らないので先行き不安だったが、やりたいことそのものが入力されているデータの最終セルを取得するで紹介されていた。VBAは利用人口が多く、ネットの情報が満載なので習得に苦労は無さそう。

コード

早速、上のサイトのコードに1行追加して動作を見てみた。A列の最終行番号をダイアログで表示し、(B, 2)セルに代入するコード。インテンドはTABでやったが、半角スペース4つ分に変換された。Pythonの推奨記法と同じで嬉しい。

Sub Hazimete_Sono1()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox "最終行は" & LastRow & "です"
    Worksheets("Sheet1").Cells(2, 2).Value = LastRow
End Sub
結果

「ユーザーフォームの実行」なるボタンがあったので押したら裏のシートで実行された。
どこにアクティブセルを置いても正しく表示してくれた。VBA……ちょっと面白いかもしれない。

説明

アルゴとしては、一旦ワークシートの最終行まで遡ってから、上に向かって数値のあるセルをサーチするというもの。わざわざ最終行から遡るのは空白行対策なので、空白行が無いことが保証されている場合は上から下に向かってサーチしても良い。ワークシートの最終行はExcel 2003までなら65536行のセルで、Excel 2007以降なら1048576行のセルらしく、それを取得してうまく動かしている。

まずVBAの基本的なことから。VBAはプロシージャと呼ばれるまとまりで記述することになっていて、今回はSubプロシージャなるものを使っている(プロシージャにはいくつか種類があるらしい)。以下のように振る舞いを書くことで、1つのプロシージャが完成する。コメントはシングルクオーテーション「’」の後に書くらしい(複数行コメントは無いらしい……!)。

Sub tes() 'ここから
'(中略)ここに振る舞いを書く
End Sub 'ここまで

今回は取得した最終行番号をダイアログで表示したりセルに代入したり、つまり使いまわすので変数に入れておくと便利。変数宣言は以下のように書くらしい。DimとはDimension、つまり“次元”のことだけど、何で変数宣言で使うのかは不明。2次元配列とか扱うときに考慮するのかな。

Dim numTest As Long '整数を入れる変数を宣言
Dim strTest As String '文字列を入れる変数を宣言
Dim dateTest As Date '日付を入れる変数を宣言

最終行はRows.Countで取得している。ワークシートの行はRowsコレクションなるもので管理されているらしく、そこのCountにアクセスしている。Cells(Rows.Count, 1)はA列の最終セル、つまりアドレスで言うと(1048576, 1)を示す。.End(xlUP)はEndプロパティの動作で、引数(?)がxlUpなら上にサーチする(他にはxlDown、xlToLeft、xlToRightがあって、いずれも埋まっているセルをサーチするらしい)。このままだとLastRowに代入されるのはRangeオブジェクトなるものらしく、行番号を代入するために.Rowを付けている。オブジェクトとかよく分からないけど、今は調べないで先に進むことにする。

MsgBoxはダイアログ表示のメソッド。セルへの代入は.Valueでできた(省略可らしいが、しばらくは省略しないで使う)。Worksheets(“Sheet1”)は宣言しなくて問題ないが、シートをまたぐ処理が発生する場合は書く必要があるため、今のうちから慣れておいたほうが無難だと考えて記載した。

デバッグコンソール(イミディエイトウィンドウ)を使う

最初に書いた通り、printf()のような標準出力関数で変数の中身を表示できるのがここ。Debug.Printという命令を使う。色々捗りそう。

Debug.Print LastRow 'LastRowの中身をデバッグ用プリント

また、デバッグタブ>ステップインからステップ実行もできた。この状態で変数にカーソルを合わせると中身を表示してくれる。

結果の保存

VBEのファイルタブから保存できた。マクロ有効ブック形式(.xlsm)で保存すること。