VisualBasic for Applications (Microsofot Excel編)

はじめに

Excel等のｱﾌﾟﾘｹｰｼｮﾝは便利ですが、あともう少しこの機能があればということが多いと思います。ExcelにはVisualBasicの機能があるので、この機能をつかえばExcelの利用価値はますます向上します。また、あくまで言語はBasicなので昔N88(86)Basicをつかったことがある人には扱いやすいです。Basicと違うところはかなりオブジェクト指向が強いところでしょう。このせいで、挫折する人も多いと思います。でも慣れてしまえば逆にオブジェクト指向が使いやすいことがわかると思います。また、ExcelにはVisualBasicのヘルプが付属しているので基本さえわかってしまえばヘルプファイルを参考にするだけでどんどんプログラミングできます。特に集計作業には大きな力を発揮するでしょう。この講座ではあまりオブジェクトの使い方については触れず、かなり実践的なサンプルを例に挙げます。オブジェクトの詳しい使い方はヘルプを参考にして下さい。また、プログラムの記述の方法など基本的事項についても省きます。あくまで実践的です。Excelのバージョンは97（Ver8）以降を対象にしています。

独自の関数を作ろう！！！

Excelには様々な関数が用意されていますが、こんな関数があれば・・・などと思ったことはありませんか？セルにかなり複雑な関数式を設定したことはありませんか？セルに関数を記述するときのネスト回数も７回までに制限されていて、複雑な処理をセルのみでさせるのはかなり無理があります。

（例）セルに以下のような式を入力しているとします。
= if ( c1<=1 , "Very Bad" , if ( c1<=5 , "Bad" , if ( c1<=10 , "Normal" , if ( c1<=20 , "Good" , if(c1>20, "Very Good" , "" )))))

VBを利用した場合のプロシージャ
Function judge(a As Integer)
If a<=1 then
judge="Very Bad"
ElseIf a<=5 then
judge="Bad"
ElseIf a<=10 then
judge="Normal"
ElseIf a<=20 then
judge="Good"
ElseIf a>20 then
judge="Very Good"
Else
judge=""
Endif
End Function

ＶＢを利用したときのセル
=judge(c1)

なんだかＶＢを利用した方が文字が多いような気がしますね。確かにセル１ヶ所のみにこの式を利用するのであればＶＢのほうが長ったらしく使いにくそうな気がします。でも、もし６００ヶ所のセルで使っていたら？しかも頻繁にこの式が変更するようなことがあれば・・・？ＶＢではプロシージャに関数を１つだけ登録すればあとはその関数を自由に使うことができます。変更が発生した場合でもプロシージャを変更するだけでセルを変更する必要はありません。通常複雑な式を１ヶ所しか使わないなんてことは無いと思います。数十ヶ所以上使うと思います。こうして考えるとＶＢのほうが管理が楽だし、入力量も少なくてすみます。

Cellsﾌﾟﾛﾊﾟﾃｨを使ってみよう！！！

いきなりオブジェクト指向の登場です。Cellsﾌﾟﾛﾊﾟﾃｨを使えれば通常必要な作業はこれだけで大丈夫です。とは言い過ぎかもしれませんが。Excelを使うときにユーザーが使うのは大半がセルです。Cellsﾌﾟﾛﾊﾟﾃｨはセルを操作するときに使います。CellではなくCellsと複数形にであることからわかるようにCellsﾌﾟﾛﾊﾟﾃｨだけで全てのセルを操作できます（あくまで１つのセルを扱います。セル範囲を扱うにはRangeオブジェクトを使います）。実際に使ってみましょう。

（例）以下のtestサブプロシージャは５行３列目のセルの式の値をセル３行２列目にコピーします。
Sub test( )
Cells(3,2).Value=Cells(5,3).Value
End Sub

注意していただきたいのがあくまで値であることです。例えばセル５行３列に式 =24+32 が入っていても、セル３行２列には56という値が入力されます。では式をそのままコピーしたい場合にはどうすれば良いのでしょうか？ValueﾌﾟﾛﾊﾟﾃｨのかわりにFormulaﾌﾟﾛﾊﾟﾃｨを使えば可能です。

（例）以下のtest2サブプロシージャは５行３列のセルの式をセル３行２列にコピーしています。
Sub test2( )
Cells(3,2).Formula=Cells(5,3).Formula
End Sub

FunctionプロシージャとSubプロシージャ

上記の例からもわかると思いますが、FunctionプロシージャはExcelワークシートから呼び出すことができる関数を作るときに使用し、Subプロシージャは［ツール］→［マクロ］→［実行］として呼び出すか、シート上にボタンを作成し、そのボタンに割り当てて使用します。注意点として、SubプロシージャとFunctionプロシージャのモジュールシートは別々に作成すべきです。理由はわかりませんが、同じモジュールシート上に作成すると、処理がとんでもなく遅くなります。数百倍の時間がかかる関数ができあがってしまいます。もしかしたらバージョン10以降は改良されているかもしれませんが。

Rangeオブジェクト

このオブジェクトはセルの範囲を扱います。Cellsﾌﾟﾛﾊﾟﾃｨと使い方はにています。RangeはオブジェクトなのにCellsは何故かﾌﾟﾛﾊﾟﾃｨなので変だと思うかもしれませんが、ヘルプを確認したので間違いありません。

（例）セル範囲A1:H8に式　=rand()　を入力しています。
Sub test3( )
Range("A1:H8").Formula = "=rand()"
End Sub

次にRangeオブジェクトとCellsﾌﾟﾛﾊﾟﾃｨを組み合わせて使ってみましょう。

（例）１行１列〜５行５列目までのセルに式　=rand()　を入力しています。
Sub test4( )
Range(Cells(1, 1), Cells(5, 5)).Formula = "=rand()"
End Sub

（例）６行１列〜１０行５列のセルに１行１列〜５行５列目までのセルの値を入力（代入）しています。
Sub test5( )
Range(Cells(6, 1), Cells(10, 5)).Value = Range(Cells(1, 1), Cells(5, 5)).Value
End Sub

制御構文とCellsﾌﾟﾛﾊﾟﾃｨを使って簡単な集計を行ってみます。

（例）１行１列〜１０行１列のセルに2が入力されている数を１行２列目のセルに入力（代入）しています。
Sub test5( )
Dim i As Integer , n As Integer
n=0
For i=1 to 10
if Cells(i, 1).Value = 2 Then n = n + 1
Next i
Cells(1, 2).Value = n
End Sub