[Excel で VBA] シートへのアクセス

これまで、Excel の上で VBA を学んで来たが、本ページでは Excel らしさを活かし、
Excel のシート (表) にアクセスする方法を学ぼう。

シートの読み込みと書き出し

プログラムには入力と出力があることが多い。

プログラムに数字や文字列を入力する方法としては今まで２通りの方法を学んだ。 利用例は 

(1)
	x = 5
(2)
	x = InputBox("数字を入力してください")

などである。
InputBoxは 第二回や第三回で用いたもので、キーボードから入力した数値が x に格納されるのだった。

一方、出力方法として今まで多く取り扱ったのは する方法である。これはほぼ毎回用いているのでお馴染みであろう。

ここで、本ページでは入力と出力に対して新たな手法を学ぶ。 それは 方法である。

これにより、「Excel の表からデータを取得し (入力)、Excel の表にデータを書き出す (出力)」などの処理が可能になる。

まず、準備として Excel の表のセル A1 と A2 に、それぞれ整数を入力しよう。



この 10 と 20 の和を VBA で計算することを考えよう。

もちろん、情報処理入門で学んだように、VBA を使わず Excel のみでも 二つのセルの和は計算できる。
しかし、それをあえて VBA で行うのがここでの目的である。

では、Excel シート上の 10 と 20 の和を VBA で計算し、結果を再びExcel シート上に記述するプログラムを示す。 

Sub prog()
Dim x As Integer, y As Integer

    x = Sheet1.Cells(1, 1).Value
    MsgBox "(1, 1)の値は" & x

    y = Sheet1.Cells(2, 1).Value
    MsgBox "(2, 1)の値は" & y

    Sheet1.Cells(1, 2).Value = x + y
End Sub

上記のコピー可能なプログラムを Excel 上の VBA に貼り付けよう。

注意すべきことは、シートに 10、20と記入した Excel ファイルで VBA を開くということである。
貼り付けると以下のようになる。



実行すると、以下のように「(1, 1) の値は10」という MsgBox が現れる。
これは、皆さんがセル A1 に書いた 10 という数字を VBA から読み取れたことを意味する。



上のMsgBox で OK ボタンを押すと、以下のように「(2, 1) の値は20」という MsgBox が現れる。
これは、皆さんがセル A2 に書いた 20 という数字を VBA から読み取れたことを意味する。



上のMsgBox で OK ボタンを押すとプログラムが終了するが、
エクセルのシートを見てみると、下図のように確かにセル (1,2) 、すなわちセル B1 に結果が書き込まれていることがわかる。



つまり、上のプログラムは、シートから値を入力として受け取り、シートに値を出力した、というわけである。

ここで、ポイントは という部分であり、これは の意味を表す。なお、(j, i) は縦 j 行目、横 i 列目の意味であり、数学の行列の添字と同じ記法となっている。

これを整理した図が以下である。



そして下図のように、Sheet1.Cells(1, 1).Value のような記述を式の右辺に書けばシート上の値の読み取りになり、
左辺に書けばシートへの値の書き込みになる。



以上によりエクセルのシートのセルの値を VBA から利用できる。
プログラミングの用語では、これを「エクセルのシートのセルの値にアクセスする」という言い方をする。

なお、シートへのアクセスでは、Sheet1.Cells(j, i) のような記述が多くなり、若干記述量が多い。
記述量を多少減らすため、以下のような「With ～ End With 文」が用意されている。 

Sub prog()
Dim x As Integer, y As Integer

	With Sheet1
		x = .Cells(1, 1).Value
		MsgBox "(1, 1)の値は" & x

		y = .Cells(2, 1).Value
		MsgBox "(2, 1)の値は" & y

		.Cells(1, 2).Value = x + y
	End With
End Sub

この「With ～ End With」内部では「Sheet1」を省略して記述できる。

シートに対するデータの作成

次に、Excel のシートにたくさんのデータを一気に出力するプログラム例を見よう。
ここで新しい知識を学ぶわけではないが、これまで学んだ知識の多くを活用するので、若干難しく見えるだろう。

以下のプログラムは、 「Excel のシートに sin 関数のデータを生成するプログラム」である。
入力は取らず、出力先が Excel のシートとなっている。

もちろん、VBA を使わず、Excel のみで sin 関数のデータを出力することは可能であるが、
それを VBA を用いて実行するのがここでの目的である。 

Sub prog()
	datafunc -3.14, 3.14, 60
End Sub

Sub datafunc(x1 As Double, x2 As Double, n As Integer)
Dim i As Integer
Dim x As Double, y As Double, dx As Double

	dx = (x2 - x1) / n

	With Sheet1
		For i = 0 To n
			x = x1 + dx * i
			y = Sin(x)
			.Cells(i + 1, 1).Value = x
			.Cells(i + 1, 2).Value = y
		Next i
	End With
End Sub

このプログラムを理解するためのポイントは以下である。 以上を図に表すと、下図のようになる。



さて、提示されたプログラムを VBA のウインドウに貼り付けると以下のようになる。



上記プログラムを実行すると、Excel のシートに以下のようなデータが出力されている。



A列とB列のデータをマウスで選択し、下図のように「挿入」タブから「散布図(直線)」を選択してグラフを描くと下図のようになる。



確かに sin 関数のグラフになっていることがわかる。
このように、VBA を用いてデータを生成し、その結果を Excel でグラフ表示することができることがわかった。

第一回で紹介した３つの例にも関連するが、Excel で VBA を用いる一番のメリットは、Excel のシートをプログラムの入力または出力として用いることができることである。
すなわち、本講義の後半にしてようやくそのメリットまでたどり着いたことになる。


←プロシージャの再帰的呼び出し／ シートへの高速なアクセス→

Excel で学ぶ Visual Basic for Applications (VBA)に戻る