EXCEL VBA(Visual Basic for Applications)の活用

Copyright(C) 2004.4 千葉商科大学付属高等学校. All rights reserved.


第1回

VBAとはなにか

既に学んだ表計算ソフトEXCELには,マクロと呼ばれる拡張機能があります。

マクロとは,データ入力や集計,編集などの操作をコード(符号)化し,記録することです。

このコード化されたデータは,機械が解読しやすい言語で記述されています。これを人工言語といいます。

人工言語にはいろいろありますが,EXCELのマクロが利用するのは Visual Basic for Application 略してVBAという言語です。

VBAという言語をマスターすれば,いろいろなプログラムを作成できるようになります。

ですから,VBAのことをプログラム言語などともいいます。

問題1) VBA以外にどんなプログラム言語があるか?

マクロでできること

マクロ(自動記録機能)では,次のようなことが可能です。 sample

  1. 単純な繰り返し処理を自動化できる

  2. 条件によって処理を変える

  3. 独自に関数を作成できる

  4. 独自にデータ入力画面を用意し,入力の効率化を計れる

  5. 一つの独立したソフトウエアの開発ができる

本節では,1〜5までの内容を順に扱っていきます。

簡単なマクロの記録と実行

【記録】

Excelには,音声を記録する感覚同様に,操作を記録する機能があります。

自分のHドライブに,新規に”VBA”という名前でフォルダーを作成します。

次に,Excelを起動し新規にファイルを作成します。ファイル名は,”MacroLesson1”として上記のVBAに保存します。

セルA1に”書式の変更”と入力し,セルA1をアクティブにしておきます。

ここからの操作をマクロとして記録します。

[ツール]メニュー→[マクロ]→[新しいマクロの記録]を選びます。

現れた画面では,【マクロ名】を”書式の変更”にします。
※マクロ名は,先頭が文字で,全半角英数,ひらがな,漢字,カタカナが使用できます。ただし,記号は_(アンダーバー)のみです

画面に記録終了アイコンが現れます。(記録したい操作を終えたら終了ボタンをクリックします。)

セルA1の書式を次のように変更します。

《フォントをHG創英角,サイズを72pt,カラーを赤》

終えたら,終了ボタンをクリックします。

【実行】

[ツール]メニュー→[マクロ]→[マクロ]を選びます。

マクロ一覧から,”書式の変更”というマクロを選択したら実行します。

第2回

マクロの登録

記録したマクロは,シート上にボタンとして登録することができます。

[表示]メニュー→[ツールバー]→[フォーム]を選びます。

現れたツールバーからボタンアイコンをクリックしたら,シートの上で適当な大きさにドラッグして作成します。

次にマクロの登録画面が現れるので,”書式の変更”を選択します。

以後から,このボタンをクリックすれば登録したマクロが実行できます。

相対参照と絶対参照マクロ

あるセルのデータを,別にセルにコピーするマクロを作成する場合に,セルの参照方法として相対参照と絶対参照の2つがあります。

【絶対参照】

特に気にせずにマクロを記録した場合がこれです。

A1セルにExcelという文字を入力します。その後,A1セルをアクティブにします。

マクロ記録を開始します。マクロ名は”絶対コピー”にします。

クリックせずに,ツールバーからコピーを選び,右に3つ,下に2つ移動したD3セルに貼り付けます。

マクロ記録を終了します。

実行して確認します。

次に,A5セルに”おはよう”と入力したあとで”絶対コピー”のマクロを実行します。

D3セルに”おはよう”が貼り付けられたと思います。これが絶対参照です。

【相対参照】

A1セルをアクティブにします。

マクロ記録を開始します。マクロ名は”相対コピー”にします。

クリックせずにツールバーからコピーを選び,同様に右に3つ,下に2つ移動したD3セルに貼り付けます。

マクロ記録を終了するときに,終了ボタンの右横のボタンをクリックして終了します。これが相対参照でのマクロの合図になります。

A5セルに”おはよう”と入力したあとで”相対コピー”のマクロを実行します。

A5セルから右に3つ,下に2つ移動したD7セルに”おはよう”が貼り付けられたと思います。これが相対参照です。

問題2) ボタンに絶対コピーと相対コピーのマクロを登録して見ましょう。ボタン名は,マクロの名前をそのまま使います。
      好きなセルに好きなデータを入力して,絶対参照と相対参照のマクロを実行して見よう。

第3回

VBエディタの基本

自動記録機能で作成されたマクロの実態は,VBAという言語で記述されています。

ExcelにはVBエディタと呼ばれるツールがあって,直接VBAを記述することも可能です。

VBAを記述するということは,イコールプログラミングに他なりません。

[ツール]→[マクロ]→[Visual Basic Editor]をクリックするとVBエディターが起動されます。

画面構成は,大きく3つに分けられます。【プロジェクトエクスプローラ】と【プロパティウインドウ】と【コードウインドウ】になります。

プロジェクトエクスプローラの「標準モジュール」をダブルクリックし、その中の「Module1」をダブルクリックすれば、コードウインドウが開かれます。

モジュールとはプログラムを保存するための入れ物のことです。モジュールには次の3種類があります。

標準モジュール

汎用的なプローシージャを作成する場合に利用します。

ユーザーフォーム

ユーザー独自のダイアログボックスを作成する場合に利用します。

クラスモジュール

特定のオブジェクトに対して処理を行うプログラムを作成する場合に利用します。たとえばフォーム上にコマンドボタンを作成し、そのボタンをクリックしたときに実行するプロシージャを作成し、そのボタンをクリックしたときに実行するプログラムを作成するときに使います。

 

コードウインドウには、マクロで記録したプログラムが記述されています。

VBAの中ではマクロというプログラムのことをプロシージャと呼びます。プロシージャの中では、主に2種類のプログラムがあります。

Subプロシージャ

 単独で実行するプログラムを作成するためのプログラムです。マクロの記録機能によって作成されたプログラムはSubプロシージャになります。

Functionプロシージャ

「引数として受け取った値をもとに計算を行い、その結果を返す」というような「ユーザー定義関数」を作成する際に利用します。

 

マクロの修正

作成した「書式の変更」マクロの内容を変更してみましょう。

まずは、簡単なマクロの修正をします。値を変えるだけでマクロの内容が変わります。

書式を次のように変更します。

《フォントサイズを200pt》
.Size = 200

修正したらマクロを実行します。

このように、値を変えることによりマクロの内容を変えることができます。

問題3) 書式を次のように変更しましょう。

《カラーを青(5)》
.ColorIndex = 5

 

第4回

マクロ記録機能で作成したプログラムには、基本的な処理の部分以外のものも記述されるので、その部分を省略、修正することにより、プログラムを効率的に実行させることができます。

選択されたセルの書式を次のように変えるには、どのようにすればよいか。

オブジェクト プロパティ
Selection Font.Name HG創英角ポップ体
  Font.Size 100
  Font.ColorIndex 緑(10)

  (オブジェクト).(プロパティ)=(値)

 オブジェクト : 操作の対象になるもののこと (例)ワークシート、セル、列など

 プロパティ : オブジェクトが持つ性質や特徴 (例)サイズ、色など

このような形で記述すれば、そのオブジェクトが持つ特徴を変更することができます。

Withステートメント (ステートメント:プログラム内に記述されている1つの完結した命令のことです。)

 1つのオブジェクトに対する複数のプロパティの値を設定する場合、そのつどオブジェクト名を記述するのは、かなり面倒なことです。

Withステートメントを使わない場合

Selection.Font.Name = "・・・"
Selection.Font.Size = ・・・
Selection.Font.ColorIndex = ・・・

Withステートメントを使った場合

With Selection.Font
     .Name = "・・・"
     .Size = ・・・
     .ColorIndex = ・・・
End With

「書式の変更」マクロのプログラムをWithステートメントを使って簡略化してみましょう。

まず、上のプログラム(フォント名、サイズ、色)は残して不要な行を選択し、[Delete]キーで削除します。

書式を次のように変更します。

《フォント名をHG創英角ポップ体、フォントサイズを100pt、カラーを緑(10)

修正したらマクロを実行します。

このようにプログラムを変更することにより、どこを変更したのかが見やすくなるし、実行したときにプログラムの処理が速く行われます。

問題4) 「書式を戻す」マクロのプログラムもWithステートメントを使って簡略化してみましょう。

 

(注) 
 プログラムを実行するときにエラーが発生するときがあります。その後、プログラムを修正するだけでは実行されません。プログラムの実行が中断されている状態にあるので、それを終了させなければなりません。プログラムの実行を終了させるには、[リセット]ボタンをクリックします。

 

第5回

マクロの作成

実際にマクロを作成してみましょう。

あるセルに文字や数を入力させるにはどのようにすればよいでしょうか。

オブジェクト メソッド
Range("f5") select

  (オブジェクト).(メソッド)

 メソッド : オブジェクトを動作させる方法 (例)選択する、削除するなど

このような記述をすれば、そのオブジェクトを動作させることができます。

オブジェクトには主に次のようなものがあります。

Selection→選択されているセル

Range("A1")→A1のセル

Cells(2,3)→行番号、列番号(Cの列)のセル

メソッドにselect、activateがあります。この2つとも、セルを選択する言語です。

selectは主にセルの範囲を選択するときに使います。

activateは選択するセルの範囲の中の一つだけアクティブにするときに使います。

一見同じように見えますが、動作結果が異なります。

 

新しいモジュールにF 5のセルに「VBA」と表示させてみましょう。

新しいモジュールを作成するには、Visual Basic Editorの[挿入]→[標準モジュール]をクリックする。

出てきたコードウインドウに以下のプログラムを記述する。

Sub 文字表示()
Range("f5").Select
Selection.Value="VBA"
End Sub

Valueは、指定したセルに値を設定するときに使います。

問題5) E3〜G7までのセルに「VBA」と表示させてみましょう。

上のプログラムのSelectionをActiveCellに変えてみたり、Selectとactivateを変えてみたりすればSelectとactivateの違いがわかりやすくなります。

問題6) 次のような文字や数を削除するマクロを作成してみましょう。

 

第6回

条件分岐If

ある条件を設定し、その条件によって実行するときの処理を変えたいときには、「Ifステートメント」を使います。

次のファイルをダウンロードしましょう。sample

まずは、表にある得点が70点以上の人は、評価の列に「合格」と表示させます。

IF・・・thenステートメント

IF 条件式 Then
  処理
End If

「もし、この〔条件式〕が成り立つならば、このような〔処理〕を行う。」

もし、70点以上なら、「合格」と表示させます。

IF Activecell.value>=70 Then
  Activecell.Offset(0,1).Value="合格"
End If

比較演算子

= 等しい
< より小さい
> より大きい
<= 以下
>= 以上
<> 等しくない

offset(2,3)→基準となるセルに対して下に、右に移動したセル 

さらに、70点未満なら、「不合格」と表示させます。

IF・・・then・・・Elseステートメント

IF 条件式 Then
  処理1
Else
  処理2
End If

「もし、この〔条件式〕が成り立つならば、〔処理1〕を行う。そうでなければ〔処理2〕を行う。」

もし、70点以上なら「合格」、そうでなければ(70点未満)「不合格」と表示させます。

Else
  Activecell.Offset(0,1).Value="不合格"

さらに条件を分けたいときには、ElseIf を使います。

IF 条件式1 Then
  処理1
ElseIf 条件式2 Then
  処理2
   ・・・
   ・・・
   ・・・
Else
  処理n
End If

このファイルをダウンロードしてIfステートメントの問題を解いてみましょう。

第7回

繰り返し処理

処理を複数回繰り返させる場合は、「For・・・nextステートメント」や「Do・・・Loopステートメント」を使います。

For・・・nextステートメント

For カウンタ変数 = 初期値 To 終了値 (step カウンタの増減値)
     ・・・
   実行する処理
     ・・・
Next (カウンタ変数)

〔初期値〕から〔終了値〕までの回数分、〔実行する処理〕を繰り返します。

カウンタ変数は一般的に「 i 」や「 j 」を使います。

カウンタ変数が増えていく状況を見てみましょう。次のようなプログラムに記述するとシートにはどのように表示されるでしょうか。

For i = 1 To 10
Cells(i, 1) = i
Next

まず、Cells(1, 1) のセルに1と表示します。次に、Cells(2, 1) のセルに2と表示します。3、4と同じように繰り返し、10までこの処理が行われます。

問題7) A1からI9までの間のセルに九九算を表示させてみましょう。

     ヒント : Cells(i, j)にして「 j 」についても繰り返し処理をする

では、前回つくった「合格」、「不合格」を表示させるマクロを繰り返し処理することにより、一度に全員分を表示させるマクロに書き換えましょう。

For i = 1 To 10
IF Activecell.value>=70 Then
  Activecell.Offset(0,1).Value="合格"
Else
  Activecell.Offset(0,1).Value="不合格"
End If

'「合格」、「不合格」を判定した後アクティブセルを一つしたのセルにします。
ActiveCell.Offset(1, 0).Select    
Next

 

第8回

「For・・・nextステートメント」だと指定した回数だけ処理を繰り返します。しかし、回数がいつも一定でない場合には、都合が悪いです。そのような場合は、「Do・・・Loopステートメント」を使います。

Do Until・・・Loopステートメント

Do Until 条件式
     ・・・
   実行する処理
     ・・・
Loop

与えられた〔条件式〕が満たされるまで〔実行する処理〕が繰り返します。

'アクティブセルが「""(空欄)」になるまで繰り返し実行されます。
Do Until ActiveCell.Value = ""
IF Activecell.value>=70 Then
  Activecell.Offset(0,1).Value="合格"
Else
  Activecell.Offset(0,1).Value="不合格"
End If

ActiveCell.Offset(1, 0).Activate
Loop

 

Do While・・・Loopステートメント

Do While 条件式
     ・・・
   実行する処理
     ・・・
Loop

与えられた〔条件式〕が満たしている場合は〔実行する処理〕を行い、満たされていない場合は、処理を実行せずに終了します。

Do While ActiveCell.Value <> ""
IF Activecell.value>=70 Then
  Activecell.Offset(0,1).Value="合格"
Else
  Activecell.Offset(0,1).Value="不合格"
End If

ActiveCell.Offset(1, 0).Activate
Loop

まとめ) このファイルをダウンロードして問題を解いてみましょう。

 

第9回

条件判断

前に紹介したIFステートメントを使うと条件によって処理を分岐することができます。ただし、条件が多くなるとプログラムに構造が理解しにくくなる欠点があります。そこで選択肢が多い場合はSelect Caseステートメントのほうが簡潔に記述できます。

Select Caseステートメント

Select Case 条件式
    Case 条件1
      処理1
    Case 条件2
      処理2
     ・・・
     ・・・
    Case Else
       いずれも成り立たなかったときに行う処理
End Select

与えられた〔条件式〕〔条件1〕となる場合、〔処理1〕をします。〔条件式〕〔条件2〕となる場合、〔処理2〕をします。いずれの条件にもならない場合には、そのときの処理をします。このようにSelectCaseステートメントでは条件を上から順に評価し、条件が成り立ったところで終了です。

「exe_if.xls」のファイルを例にすると次のようになります。

Ifステートメントの場合

If ActiveCell.Value = "りんご" Then
ActiveCell.Offset(0, 2) = "好き"
ElseIf ActiveCell.Value = "みかん" Then
ActiveCell.Offset(0, 2) = "嫌い"
Else
ActiveCell.Offset(0, 2) = "普通"
End If

Select Caseステートメントの場合

Select Case ActiveCell.Value
Case "りんご"
ActiveCell.Offset(0, 2) = "好き"
Case "みかん"
ActiveCell.Offset(0, 2) = "嫌い"
Case Else
ActiveCell.Offset(0, 2) = "普通"
End Select

指定した条件式(ActiveCell.Value)について、「りんご」になった場合、「みかん」になった場合の処理がその下に記述されます。

 〔値の指定のしかた〕

   ○特定の値を条件とする場合

'条件式が100となる場合
case 100
'条件式が100以上となる場合
case Is >=100

比較演算子を使う場合には、CaseのあとにIsをつけて記述します。

   ○連続した範囲の値を条件とする場合

'条件式が100から300の場合
case 100 to 300

この場合は、「to」を使って範囲を記述します。

   ○不連続の値を条件とする場合

'条件式が100か150か280となる場合
case 100,150,280

この場合は、「,(カンマ)」を使って記述します。

次の問題をダウンロードしてマクロを作成してみましょう。

 

第10回

フォームの作成

ユーザーフォーム

VBAには、独自のダイアログボックス(ユーザーフォーム)を作成する機能があります。ダイアログボックスを利用することにより、データの入力作業やデータ集計を効率よく行うことができます。

まずはデータを入力するためのフォームを作成しましょう。今回は、「生徒リスト」にデータを入力するためのフォームを作成します。新規ファイルを開き、次の表を入力してください。

■生徒リスト

■作成するフォーム

VBエディタを起動します。(〔ツール〕→〔マクロ〕→〔Visual Basic Editer〕)

〔挿入〕メニューからユーザーフォームをクリックすると新規のユーザーフォームが表示されます。

ユーザーフォームを挿入すると、自動的に「ツールボックス」ツールバーが表示されます。

○ラベルの作成

まずはフォーム上にラベルを配置します。〔ツールボックス〕の〔ラベル〕をクリックし、フォーム上の場所でドラッグして大きさを決め、ラベルが作成されます。「生徒情報入力」、「学年」、「組」、「氏名」、「性別」の5つのラベルを作成しましょう。

文字を表示させるには、変更したいラベルの枠をクリックして選択し、プロパティウインドウのCaptionをクリックし、その右側に表示する文字を入力します。

文字の書式を変更するには、同じくラベルを選択し、プロパティウインドウのFontをクリックし、右側のをクリックし、サイズを「16」にします。

○テキストボックスの作成

〔ツールボックス〕の〔テキストボックス〕をクリックし、テキストボックスが作成します。「氏名」の右横に1つ作成しましょう。

名前を入力してファイルに出力するため、オブジェクト名をつけておくとわかりやすくなります。プロパティウインドウの(オブジェクト名)をクリックし、その右側にオブジェクト名として、「name」と入力します。

○オプションボタンの作成

〔ツールボックス〕の〔オプションボタン〕をクリックし、オプションボタンが作成します。「性別」の右横に2つ作成します。

ラベルのときと同じように、オプションボタンのプロパティウインドウのCaptionをクリックし、その右側に「男」、「女」とそれぞれのオプションボタンに入力します。

上と同じように、プロパティウインドウの(オブジェクト名)をクリックし、その右側にオブジェクト名として、「男」のボタンに「male」と、「男」のボタンに「female」入力します。

あらかじめどちらかに選択したい場合は、プロパティウインドウのValueをクリックし、その値を「true」にします。今回は「男」を自動的に選択しておきましょう。

○コンボボックスの作成

〔ツールボックス〕の〔コンボボックス〕をクリックし、コンボボックスが作成します。「学年」と「組」の右横にそれぞれ作成します。

コンボボックスにデータの一覧を表示させるには、プロパティウインドウのRowSourceをクリックし、そのデータを「学年」のコンボボックスには「F5:F7と、「組」のコンボボックスには「G5:G11」と入力します。

上と同じように、プロパティウインドウの(オブジェクト名)をクリックし、その右側にオブジェクト名として、「学年」のボタンに「gakunen」と、「組」のボタンに「kumi」入力します。

 

第11回

 

間隔を均等にするには

 

○オブジェクト名(モジュール名)

○タイトル

○文字の書式変更

○自動的に日本語入力をオンにする

○オプションボタンをあらかじめ選択

○コソボボックスに一覧を表示

○カーソルの移動順

 

 

実行ボタンをクリックすれば確認できる。