Excel+VBAで論理的にプログラムする方法を考えてみた

この文書の目的

　VBAでExcelの処理を自動化するとき、ワークシートのセルの読み書きが必要になります。
　セルの指定方法を工夫することで、セルの挿入や削除で読み書きしたいセルの位置（アドレス）がずれると正しく読み書きできなくなります。
　普段、Access+VBAでプログラムをしていると項目名でデータにアクセスできるので、この即物的にアドレスを指定する方法は本当にやりにくい。
　そこで、セルのアドレス指定を工夫することで、ワークシートの変更に強いVBAを作る方法について検討します。
　この記事ではExcel+VBAで処理を行っていますが、Accessでも処理できます。
　例えば複数の人にExcelで書類を作成してもらい集約するとき、確実にデータを採取できれば自動化することが可能になります。
　データの集約には、Accessと組み合わせて使うことが効果的です。

想定する読者

　Excelにあまり詳しくないAccessユーザー(私のことだ・・・)

用語の定義

• 名前
  　ワークシートの一部に名称をつけて識別することができます。正しくは名前付き範囲といいます。
• 関数
  　ワークシート上で使えるワークシート関数とVBA内で使える関数の二種類があります。
• プログラム
  　処理を行うものを示しVBAやワークシート関数などを包括する。

サンプルの状況

　次のようなワークシートを例にします。
　起点と入力されているセルはB3と表されますが、VBAでcellsを使うときは（行、列）で位置を表しcells(3,2)となります。

セルを直接指定する問題点「参照先が動く」

　関数やVBAからセルをB3のように指定すると、常にB3の位置にあるセルを参照することになります。
　もし、「起点」がC2に移動すると正しく参照できなくなります。
　このため列や行の加除を行い、セルの位置が移動すると関数やVBAが正しく動作しなくなり、修正する必要が生じます。
　作成者自身が使用するワークブックなら、自分で修正することで対応できますが、他の人に配布した場合など対応が難しくなります。

原因と対策

　「参照先が動く」ことの原因は、Excelでのセル指定がワークシート上のアドレスを直接指定すること（以下、即物的指定）が原因です。
　そして対応策として、ワークシートやワークブックを保護し勝手に列や行の加除をできなくなるする方法がありますが、のちにワークシートを変更する際、パスワードを忘れて保護を外せなくなるなどの弊害もあります。
　Accessなどのデータベースでは、データを参照する時は項目名を使います。
　このため項目の順番がずれても正しく参照することが保証され、処理を論理的に行うことが可能になります。
　Excelでもプログラムからデータを論理的に参照できれば、プログラムの動作はより安定するはずです。
　

名前の定義と利用法

名前の定義

　Excelではワークシート上の任意の範囲に名前を定義できます。
　先に名前をつけたい範囲を選択し、2のテキストボックスで任意の名前を入力すると定義できます。
　次の例ではB3のセルに「起点」と名前をつけたので、起点という名前で参照することができます。

　またはメニューから【数式】→【名前の定義】でも行えます。　

名前で参照の基本

　VBAでワークシートのデータにアクセスするとき、rangeとcellsの二つの方法があり、変数を使いやすいcellsを使うことが多いと思います。
　また、rangeを使うときも range("B3") のようにすることが多いと思います。
　すでにB3に「起点」という名前をつけているので、 range("起点") でB3を参照でき、B3のセルが移動しても参照することになります。
　セルをワークシートの升目のこととと考えず、下の図のようにデータの入れ物¹と考え、データの入れ物が整然と並び、識別するためにアドレスを割り振っていると考えるとわかりやすいかもしれません。

複数範囲を手間無く参照する

　データ処理をする際、複数のデータを参照することがよくあります。
　rangeはアドレス指定が文字列のため繰り返し処理などと相性が悪く、アドレスを数値で指定するcellsを使います。
　しかしcellsを使うと、ワークシート内の絶対参照となることと、アドレスを数値で列と行の組として表すため処理内容がわかりにくくなります。

起点からの相対位置で表現してみる

　ワークシート上の位置に意味のある名前をつけて、そこから相対的な位置でデータを参照できるとプログラムが楽になります。下の図では起点B3から二行下のセルはB5になります。

　この用途に使える関数として、Rangeで指定したセルで使えるoffset関数があります（正しくはRangeオブジェクトのoffsetメソッド）。
　上の図の起点を原点に「二個した」を参照するにはRange("起点").offset(0,2)とします。
　offsetでは相対位置を数値で表現するため変数が使いやすくなり、次のような記述もできます。

	Dim idx as long 
	For idx = 1 To 3
		'一行ずつ参照する。
		Debug.Print Range(strTxt).Offset(idx, 0).Value
	Next

　明細書の明細を読み取る処理などで使えそうです。

配列として扱ってみる

　VBAでワークシートにループ処理でアクセスすることは処理速度から不利とされています。
　そこで下の図のように「起点」セルの下３つのセルを基点データと名前付けします。

　こうすると、Excelのワークシート関数transposeを使って次のようにアクセスできるようになります。
　このサブルーチンでは起点データ内のセルの値をイミディエイトコンソールに出力します。

Sub test01()
	Dim tmpVar As Variant
	Dim idx As Long
	    
	tmpVar = WorksheetFunction.Transpose(Range("起点データ"))
	For idx = 1 To UBound(tmpVar)
		Debug.Print idx, tmpVar(idx)
	Next
End Sub
	

　Transpose関数は配列を引数と戻り値にする関数で、ワークシートに配列データを書き込むこともできます。
　ワークシートの範囲を二次元配列として扱うこともできますが、処理内容の理解しやすさも考慮する必要がありそうです。
　ワークシートを二次元配列と考えて、列と行の値を足したものをまとめてワークシートに展開するプログラムは次のようになります。

Sub test02()
    Dim data(100, 100)  As Double
    Dim idxX As Long
    Dim idxY As Long
    
    For idxY = 0 To 99
        For idxX = 0 To 99
            data(idxY, idxX) = idxX + idxY
        Next
    Next
    Range(Cells(1, 1), Cells(100, 100)) = WorksheetFunction.Transpose(data)
  
End Sub

　書き込み先の指定に名前を使うと次のようになります。
　（A1をBaseと名前づけている）

Sub test03()
    Dim data(100, 100)  As Double
    Dim idxX As Long
    Dim idxY As Long
    
    For idxY = 0 To 99
        For idxX = 0 To 99
            data(idxY, idxX) = idxX + idxY
        Next
    Next
    Range("Base", Cells(100, 100)) = WorksheetFunction.Transpose(data) 
End Sub

　実用的には帳票を作る時、表に文字列、数値が混在することになるので、Variant型の配列を定義し、それに書き込むデータを作成して、最後にまとめてTranspose関数で書き込みます。
　

1. オブジェクトという概念になります。rangeなどの関数はメソッド、valueはプロパティとよびます。 ↩