Excel VBAプログラムへの入門

調査研究とデータ解析の技法
スペシャルエディション
プログラミング
西山 茂
年 月 日
½
マクロとＶＢＡ
プログラミング入門です。記録マクロを利用すれば機械的にプログラムが作れますが、
データ数や入力場所など少しでも状態に変更があれば使えないなど、やはり プログラミングを基
前半は
礎から身につけたほうが遥かに生産的です。
記録マクロで小手調べ
個のデータ
ちに計算できるように、計算手順を記録しておきましょう。記録マクロの作り方はテキスト第２章『一
変量の度数分布と確率分布』
（
ページ）に説明があります。その手順に沿って早速やってみましょう。
の平均と分散を求めましょう。 個のデータでも値を変えたときに平均と分散がどのように変わるのか直
記録するマクロ名は「分散の計算」にします。
マクロができたら
個のデータを
に変更してマクロを呼び出しましょう。呼び出しはツール→マクロ→マクロをメニュー選択してもいい
を押下してもできます。
しばらく反復してみてください。 個のデータで試したらデータ数を増やしたり減らしてみてください。
し、もっと簡単に
記録マクロは確かに便利ですが大きな弱点があります。
小樽商科大学ビジネススクール
¯ データの数やデータの位置が少しでも変わると対応できない。
¯ いちいちマクロ実行選択ダイアログを表示させないといけない。
¯ 記録マクロが作成するプログラム文は劣悪な水準で読んでも分かりにくいし修正するにも修正し
にくい。
¯ 場合分け、エラー処理など複雑な処理は無理である。
特に最初の欠点は致命的であり、一度
プログラムを覚えると、 利用の実際の場で記録マ
クロを利用する局面はほとんどありません。
編集画面が練習の場
マクロ「分散の計算」は、
プログラムとして自動作成されています。それを見ましょう。
を押下して 画面を表示させましょう。左上のプロジェ
キー
クト・エクスプローラーに「標準モジュール」があります。その中に が保存されているはずで
す。その を右クリックして「コードを表示」を選択しましょう。記録マクロで自動作成された
に基づいて記述されています。 は世界中で利用されています。特に「日曜プログラ
マー」や Æ にとっては定番の道具になっています。 Æ を自在に操るための 環境を と呼んでいるわけです。
プログラム・コードが表示されます。このプログラムはマイクロソフト社が提供している
では早速簡単なテストをしてみましょう。その前にワークシートをクリアしておいてください。
画面の最下行にカーソルを移動してください。そうして
データ
を入力してください。 行目のステートメントは 文で新しいマクロ命令を定義するためのコード
としてマクロ呼び
です。入力したらファイル保存してください。ワークシートに戻ってから
! が含まれているはずです。それを
出しをかけてください。いま作ったマクロ命令
実行して動作を確かめてください。
" 画面に戻ります。
せましょう。" 行目以下に
には 行のステートメントしかありません。これを膨らま
特定の
キーコマンドを定義しておけばこの欠点は解消できます。しかし定義したキーを忘れるかもしれません。
"
を入力してください。ファイル保存し ワークシートを一度クリアしてから を実
行してください。どうなりますか。 !
双方ともワークシートの特定のセルあるいは範囲
が提供している変数名 です。市販の プログラムは分
厚いのが普通ですが、それは !
以外にも 上であらかじめ利用できる変数が提供
をプログラム中で操作するために
されておりその数が膨大だからです。
# いま入力した " 行のステートメントを削除し、代わりに
"# $ %$ $
& $
を入力してください。入力したら実行して動作がどう変わるか確認してください。
最初の課題を作り直す
プログラムでワークシートを自在に操ることができそうだという感じがつかめませんか？では、
個のデータの分散を計算するプログラムを作ってみましょう。
まず、
の冒頭に
'$ &
$$
を入れておきます。このステートメントは詰まらないミスを防ぐコツですから、必ず入れるようにし
ましょう。
" 次に $(
$(
$(
$(
定義文の直後に（空行を入れてもかまいませんが）
$ $#
)$*$ ) を加えてください。これらはプログラムで使う値を格納する変数です。
# これで準備は整いました。最下行の +& $ から以下のステートメントを追加してください。
偏差
二乗偏差
)$*$ ,
-# $ . 必ずしも保存しなくともいいのですがプログラムを変更したら保存しておきましょう。
オブジェクトと呼んでいます。
#
)$*$ )$*$ % !
% $ +& $
)$*$ )$*$ / !
0 )$*$
-# $ . !
% $ !
% $ 1 )$*$
!
% $ !
% $ 2 +& $
) -# $ )
+& $
) ,
. ) % !
% $ ) / )
!
0 で実行してみてください。その前にワークシー
追加入力が終わったら保存して、同じように
トを一度クリアしてください。どうなりましたか。
記録マクロで自動作成した「分散の計算」と同じ結果が得られるように自分でプログラムすることは
このように簡単な作業で済むのです。とはいえ欠点もあります。自動作成した「分散の計算」ではデー
タを変更して何度でも反復計算できます。しかし、自作した は一度実行できるだけ
でデータの変更がききません。この点を改善して反復処理に耐えるマクロプログラムを完成しておきま
しょう。
先ず
$( 3 #
を変数宣言文として追加しておきます。変数 3 は最後のデータが何行目にあるかを記憶し、後の計
算に利用するためです。プロシージャ名も から 45 に直しておきましょう。
" 完成されたプログラムは次のようになります。内容を吟味してください。準備は に「データ」、そ
の下に好きな数だけデータ値を入れておくだけです。
45
$( $ #
データを入れず真っ白なワークシートのままで を呼び出した時はどうなるか試してみてください。メッセージウィ
ンドウが表示されたらデバッグを選びましょう。どうなりますか？今日は時間の関係からデバッグ作業については説明を割愛しま
す。
$
$(
$(
$(
$(
)$*$ ) 3 #
偏差
二乗偏差
3 &
33 6 最後のデータの行番号
)$*$ ,
-# $ . 3
)$*$ )$*$ % !
$ +& $
)$*$ )$*$ / 3 1 !
3 % )$*$
-# $ . 3
!
$ !
$ 1 )$*$
!
$ !
$ 2 +& $
) ,
-# $ . 3
) ) % !
$ +& $
) ) / 3 1 )
!
3 % データを変更して色々な場合を試してみてください。答えが正しいか
の関数 # で検算してください。
自前の関数コマンドを作ろう
プログラミングは自転車やスキーと同じで自分でやってみる時間数に比例して（確実に）上達します。
反復して行う作業はできるだけ
プログラムにしておくほうが生産性が上がります。
分散の話しをしてきました。本来この計算は7範囲 で求められましたから、改めてプログラム
にする必要は薄いと言えます。とはいえ、データの散らばりを測る指標は分散だけではありません。分
散＝平均二乗偏差ではなく平均絶対偏差で散らばりを求める場合も結構あります。しかし、この計算は
関数コマンドとしては提供されていません。それを自作してみましょう。名前は 7 ではなく 8 に
しましょう。 さっき作った 45 の後ろに追加すればいいでしょう。
以下のプログラムでうまく動作します。「動くプログラム」を読んで基礎知識を整理するのが最速の
上達法です。内容を吟味して、自由に質問をしてください。
-$ #9# $(
$(
$(
$(
$ #
& )
#
)$*$ #3!
$( &
$( )
-# $ . &$ #!
$
+& $
)$*$ -# $ )$*$
+& $
)$*$ ,
. )$*$ % &$
)$*$ / -# $ .
)$ +& $
-# $ .
)$ +& $
&$ 1 )$*$
)$
)$*$ ,
-# $ . )$*$ )$*$ % )$
絶対
の をとったわけです。
+& $
)$*$ )$*$ / #9 )$*$
-$
自作した関数コマンドの呼び出しは普通の関数と同じように
#9:;
のようにして利用することができます。必ず「標準モジュール」にプログラムを置くようにしてください。
まず簡単なデータ
で計算が正しいかどうか確認するところから始めてください。
¾
反復作業こそ自動化しよう
でプログラムにしておくと生産性が非常に上がることは実感できたのではないで
しょうか。特に定期的に行うパターン化された計算作業は には好適です。この種の作業は何ステッ
プにも分かれていることが多く、ワークシートに備わった関数コマンドだけで作業を進めるのは案外手
間を要し時間がかかります。たとえば会計処理、試験の成績評価などはその典型です。 でグラフ
一定の作業を
を作るのも面倒です。使いやすい画面を表示させ、使い方も柔軟に設計したいところです。
後半の内容はコマンドボタンとユーザーフォームの利用法です。
コマンドボタンと入力ウィンドウを使い慣れる
組、 組に実施した「統計学」の試
% 名です。クラスごとに平均、分散、標準偏差を計算
し、併せて各学生の偏差値を求めておきましょう。最後に &% 点以上、
% 点以上・
・
・と % 点刻みに区分
して分布を描くことにしましょう。これだけの作業は各クラスごとに行うだけでも結構大変です。
ファイル 5< &
のワークシート「ステップ２」には
験得点が入力されています。学生数は各クラス
でプログラムすることにより、コマンドボタン得点の整理 をクリックした後、各クラスをマウスでド
ラッグするだけで、必要な作業を全部行ってくれるようにしましょう。各クラスの結果は新しいワーク
シートに書き込むようにします。
'
コマンドボタンとイベント・プロシージャ
コマンドボタンを配置する
まず表示→ツールバー→コントロールツールボックスをメニュー選択してコマンドボタンなどのコン
トロールをワークシート上に配置できるようにします。
次に図
のように「デザインモード」ボタンを押下してコントロールの動作内容を変更できるように
します。
図
( デザインモード
そうしてから、コマンドボタン・コントロールをクリックして、適当な場所に配置します。こうする
と図
" のようになります。
ボタンのイベント・プロシージャ
こうしてから配置したコマンドボタンをダブルクリックすると、
画面に移動します。既に
7#$ !((5 =!
$*
が挿入されています。これはいま配置したコマンドボタンのイベント・プロシージャで、ボタンをクリッ
クしたときの動作を定めるプログラムをここに記述すればよいのです。イベント・プロシージャは「標
準モジュール」ではなく、ボタンが配置されたワークシート「ステップ２」に保存されることを左のプ
ロジェクト・エクスプローラーで確認してください。
ここで行いたいことは
ボタンをクリックしたらデータ範囲やグラフ作成の有無などを選択できる画面を表示する
です。そこで
!)!
)3
図
"( デザインモード
とだけ入力しておきます。!)!
はクラスを選択するためのウィンドウ（＝ユーザーフォーム）
の名称です。
ユーザーフォームのデザイン
クラス名、データ範囲、グラフ描画の有無などを指定するユーザーフォームをデザインしましょう。
ウィンドウから挿入→ユーザーフォームをメニュー選択します。するとフォーム・デ
ザイン画面になります。このフォーム中にボタン、テキスト等々、色々な部品を配置していけばよいの
です。ユーザーフォームには、原則、 ) ボタンと * ボタンを配置します。
) ボタンのイベントプロシージャには、このユーザーフォームに入力された値に基づいてどのよう
なことをさせたいのかを記述します。行いたい作業内容そのものです。また * ボタンのイベント・
そのために
プロシージャは、通常は作業の中止です。何もせずにユーザーフォームを画面から消去するだけにしま
しょう。
#
ユーザーフォーム !)!
は、コントロール・ツールボックスを利用して、図 のようにデザイ
ンします。
こうした後、ユーザーフォームをクリックしてユーザーフォームのプロパティを表示させ
&
を
図
#( ユーザーフォームのデザイン
*+*」にします。ボタンは左側に ) 、
「データの選択」にします。それからオブジェクト名を「
* を配置します。それぞれボタンのプロパティから $ を選びこのように変更します。
右側に
実際にデータ範囲を指定するコントロールは "$ コントロールを使います。ユーザーフォームに配
置する色々なコントロールには全てオブジェクト名が付けられていますので確かめておいてください。
たとえばデータ範囲を指定する "$ に入っているアドレスを取得するには "$ のよ
うに参照すればいいわけです。
ユーザーフォーム上のイベント・プロシージャ
* ボタンのイベントプロシージャを記述するにはボタンをダブルクリックします。すると自動
的にプログラムコード編集画面になりますから
7#$ !((5=!
$*
<
としておきます。これは自分自身（ユーザーフォーム自体）を消去しメモリーからも解放せよという意
味です。それまでの作業を中止するわけです。
) ボタンのイベント・プロシージャは作業本体に相当します。次のように入力します。
7#$ !((5 =!
$*
$(
$(
$(
$(
$(
$(
# & "#> , # # #
$ #
( 3!)# !)#'?
%
# "$ #3!
$( &
( $$ ##
$$ 7#
-# $ &$ +& $
-# $ &$ +& $
. #!
$
1 ( / . , % , @ &$
A#*) "#:$ )
-# $ !
+& $
-# $ !
+& $
-# $ !
+& $
学生番号
得点
偏差値
学生数
平均点
標準偏差
最高点
最低点
. % $ $
. % $ #!
$
. % $ &$ ,
" "
"
"
"
(
$$&#
$$$#
" '$5 .# .) 6 ヒストグラムを描くときのみ動作させる
-# $ . ,
"#>$ ,
+& $
-# $ . # #!
$ B ,
! #
! ,
"#> "#> %
!
"#> "#> %
! "#> "#> %
! "#> "#> %
! "#> "#> %
! "#>; "#>; %
! ;
"#>C "#>C %
! C
"#>0 "#>0 %
! 0
"#>D "#>D %
! D ,
"#> , "#> , %
+& $
-# $ . D
!
$ C
5# , @ $ 1 E 1 E =
5# , @ $ 1 % D
!
$ 0
"#>$
+& $
!
, C
D,1 ,,
!
, 0
"#> ,
3!)# $ )!)#'?, ,, ,, ,,
A$) 3!)#!)#
# #:$ ) :) , 7
54:&
!
(
4!(.4 &
!
(!
#
"
F.$
.#
!)#.$
.& ヒストグラム
A$)
"
<
実際にボタン得点の整理 を押下して何度か動作を試してみてください。偏差値とは周知の用語だと
思いますが、
標準値
から求められる標準値から
偏差値
として算定される値です。平均
平均値
, 値標準偏差
, % ¢ 標準値 - %
% を中心に全体の中の位置づけがわかる指標になっていることがわか
るでしょう。偏差値という尺度は、平均得点が高いときにも低いときにも、共通して使用できる物差し
です。
¿
まとめ
大体
プログラミングの雰囲気はわかったでしょうか。 でプログラムを組めば を自由
自在に動かすことができます。今日の資料を一回で完璧に理解しようと思わないでください。特に後半
に説明した内容は初歩的なものとは言えません。とはいえ、参考書に目を通すだけで相当の短期間でこ
の資料に述べた程度の作業はできるようになるはずです。
参考文献
新井雅行『 &' 完全制覇』日経 . 社、&&
年
" 大村あつし、栗山恵吉、田中博人『"%%% 実践プログラミングリファレンス』、エーアイ
出版、"%%% 年
#