● (発展編) エクセル VBAの演習 その2 (2002年 10月26日) --------------------------------------------------------- VBA のプロシージャの表記には、 応用編 で演習した Sub の他に Function がある。また 演習15で記述したモジュールの他に、「クラスモジュール」が ある。今までの演習よりも難解なこの発展的な概念を、下記URL を参考にしな がら解説する。ここの 発展編 における演習問題の一覧は次のとおりである。 《発展編その2 演習一覧》 演習18:Function を用いて二つのデータAとBの和を求めよ。 演習19:クラスモジュールを用いて二つのデータAとBの和を求めよ。 演習20:サブルーチンとクラスモジュールを用いて加算処理をせよ。 演習21:アプリケーションレベルのイベント制御を用いた処理をせよ。 演習22:処理中を表すインジケーターを作成せよ。 演習23:リストボックスを利用して選択科目を集計し検索せよ。 演習24:シートとフォームが連動した成績一覧表を作成せよ。(印刷可) 演習25:Dictionaryオブジェクト(連想配列) を利用して集計をせよ。 演習26:《拡張編》エクセル VBAの演習 その3 演習18:『Function (関数)』 を用いて、二つのデータAとBの和を求める 標準モジュールを作成せよ。
(解説) Sub は戻り値がなく、Function は戻り値がある。 従って、マクロの多くは Sub を使って書かれる場合が多いが、ユーザ−定義関数の Function は有用 である。次のコードを標準モジュールに作成して実行せよ。 [Module1] ----- Public A As Integer Public B As Integer Sub keisan() Dim S As Integer A = Cells(1, 2).Value B = Cells(2, 2).Value '関数の呼び出し S = Goukei(A, B) '関数の計算結果の表示 Cells(3, 2) = S End Sub '関数の定義 (合計計算) Function Goukei(u As Integer, v As Integer) As Integer Goukei = u + v End Function [module2] ----- Sub clear() For I = 1 To 3 Cells(I, 2).ClearContents Next I End Sub bottom top 演習19:『クラスモジュール』を用いて、二つのデータAとBの和を求めよ。
(解説) データを設定し取得する関数を 新たなプロパティ として作成し、そして アルゴリズムを表す関数を 新たなメソッド として作成する。それを元に クラスモジュール と呼ばれる 新たなオブジェクト(雛型) を作成する。 この クラスモジュール を参照するオブジェクト変数を標準モジュールに おいて 新たなインスタンス(例という意味) として定義し、実行する。 [クラスモジュール Class1] ----- Private X As Integer Private Y As Integer 'データAを取得する関数(プロパティ) Public Property Get DataA() As Integer DataA = X End Property 'データAを設定する関数(プロパティ) Public Property Let DataA(ByVal p As Integer) X = p End Property 'データBを取得する関数(プロパティ) Public Property Get DataB() As Integer DataB = Y End Property 'データBを設定する関数(プロパティ) Public Property Let DataB(ByVal q As Integer) Y = q End Property '合計し表示する関数(メソッド) Public Sub Goukei(u As Integer, v As Integer) Dim S As Integer S = u + v '結果表示 Cells(3, 2) = S End Sub [標準モジュール Module1] ----- Sub test() 'インスタンスの生成 Dim Keisan As New Class1 'データA,Bの設定(プロパティ) Keisan.DataA = Cells(1, 2).Value Keisan.DataB = Cells(2, 2).Value 'データA,Bの取得(プロパティ) MsgBox "データA:" & Keisan.DataA & Chr(13) & _ "データB:" & Keisan.DataB '合計計算(メソッド) Keisan.Goukei Keisan.DataA, Keisan.DataB End Sub [標準モジュール Module2] ----- Sub clear() For I = 1 To 3 Cells(I, 2).ClearContents Next I End Sub 上記のクラスモジュールで作成した オブジェクト,プロパティ,メソッド の 構造は次のとおりである。 オブジェクト | プロパティ | メソッド --------------+--------------+---------- Keisan | DataA, DataB | Goukei bottom top 演習20:(1)サブルーチン を用いて、複数の加算処理をせよ。 (2)クラスモジュール を用いて、複数の加算処理をせよ。
(解説) (1)変数とサブルーチンを、複数の処理の回数分だけ設定する必要がある。 ここの例題では、合計処理を3回行っている。 [標準モジュール Subroutine] ----- Dim SA As Integer Dim SB As Integer Dim SC As Integer Sub Main_S() Dim i As Integer SA = 0: SB = 0: SC = 0 'サブルーチンA の呼び出し For i = 1 To 10 GoukeiA i Next i 'サブルーチンB の呼び出し For i = 11 To 20 GoukeiB i Next i 'サブルーチンC の呼び出し For i = 21 To 30 GoukeiC i Next i '計算結果の表示 MsgBox "サブルーチン 1〜10 の合計: " & SA & Chr(13) & _ "サブルーチン 11〜20 の合計: " & SB & Chr(13) & _ "サブルーチン 21〜30 の合計: " & SC End Sub 'サブルーチンA Public Sub GoukeiA(a As Integer) SA = SA + a End Sub 'サブルーチンB Public Sub GoukeiB(b As Integer) SB = SB + b End Sub 'サブルーチンC Public Sub GoukeiC(c As Integer) SC = SC + c End Sub bottom top (2)変数とサブルーチンが一つだけで、(1)と同じ結果が得られる。 一つのクラスモジュールからインスタンスを3つ生成している。 [クラスモジュール Class1] ----- Public ClsG As Integer 'クラスモジュール Sub Goukei(n As Integer) ClsG = ClsG + n End Sub [標準モジュール Module1] ----- Sub Main() Dim i As Integer 'インスタンスを3つ生成 Dim CA As New Class1 Dim CB As New Class1 Dim CC As New Class1 'インスタンスCA の実行 For i = 1 To 10 CA.Goukei i Next i 'インスタンスCB の実行 For i = 11 To 20 CB.Goukei i Next i 'インスタンスCC の実行 For i = 21 To 30 CC.Goukei i Next i '計算結果の表示 MsgBox "インスタンス 1〜10 の合計: " & CA.ClsG & Chr(13) & _ "インスタンス 11〜20 の合計: " & CB.ClsG & Chr(13) & _ "インスタンス 21〜30 の合計: " & CC.ClsG 'インスタンスのクリア Set CA = Nothing Set CB = Nothing Set CC = Nothing End Sub bottom top 演習21:クラスモジュールの『アプリケーションレベルのイベント制御』 を使用して、「ワークブックの新規作成をした時に MsgBox を表示する」 という処理をせよ。
(解説) 今までの演習は、特定の「ワークシート・ワークブックレベル」のイベント プロシージャを扱っていた。 演習19で解説した クラスモジュール を用い て、最上位のオブジェクトである Applicationオブジェクト により 全て のワークブックのイベントを制御することができる。 [クラスモジュール ClsAppLevel] ----- 'アプリケーションレベルのオブジェクトの定義 Public WithEvents AppObj As Application Private Sub AppObj_NewWorkbook(ByVal Wb As Workbook) '新規ワークブックを開いた時のイベント MsgBox "新規ワークブック Open !" End Sub Private Sub AppObj_WorkbookOpen(ByVal Wb As Workbook) 'このワークブックを開いた時のイベント MsgBox "Thisワークブック Open !" End Sub [ThisWorkbook] ----- 'クラスのインスタンスの生成 Dim ClsIns As New ClsAppLevel Private Sub Workbook_Open() 'Workbookが最初に開かれた時に自動実行 'アプリケーションへの関連付け Set ClsIns.AppObj = Application End Sub 演習21のコードの補足 ClsAppLevel ... クラスモジュールのオブジェクト名として名前付け WithEvents ... イベントに対応するオブジェクトであることを示す AppObj ... Applicationオブジェクトを表す変数 ClsIns ... クラスモジュールを参照するオブジェクト変数 bottom top 演習22:処理中を表すインジケーターを作成せよ。
(解説) 上のユーザーフォームには、ラベルを4枚使っている。 [ユーザーフォーム hatten06] ----- Private Sub UserForm_Initialize() Indicator.Width = 0 End Sub [標準モジュール Module1] ----- Private Sub Userform_Activate() Call Calculator End Sub Private Sub Calculator() Dim RowMax As Integer Dim ClmMax As Integer Dim r As Integer Dim c As Integer Dim p As Integer '行列の数を定義 Worksheets("Sheet2").Activate RowMax = 300 ClmMax = 20 '乱数を発生 For r = 1 To RowMax For c = 1 To ClmMax Cells(r, c).Value = Int(Rnd * 100) Next c 'インジケータを表示 With hatten06 p = Round(r * (100 / RowMax), 0) .Percent.Caption = p & " %" .Indicator.Width = (r / RowMax) * .Frame.Width End With 'プログラム制御を Windows に渡す DoEvents Next r 'クリア Range("A1").CurrentRegion.ClearContents Range("A1").Activate Unload hatten06 Worksheets("Sheet1").Activate '再実験か否か If MsgBox("再実験をしますか?", vbYesNo) = _ vbYes Then hatten06.Show Else End End If End Sub bottom top 演習23:リストボックスを利用し、選択科目を集計し検索する次のような ユーザーフォームを作成せよ。 データの行および列の表は可変で、削除 挿入をしても自由に集計検索が可能であるものとする。 コードは長文な ので、hatten07.htm を参照せよ。また、この xls ファイルを、新たに hatten07.zip として登録済み(要解凍)。
bottom top 演習24:シートとフォームが連動した、次のような成績一覧表を作成せよ。 スピンボタンとスクロールバーにより、テキストボックスの リンク先の セルが移動する。 シートとフォームのどちらからでも、データの入力が できる。(レコード数は任意に追加可能。) さらに、シートとフォームの 両方の印刷可能。コードの詳細については、hatten08.htm を参照せよ。 これをさらに改良した 改良版 の xlsファイルを新たに kairyo04.zip として登録済み(要解凍)。 10科目対応・入力用フォーム付き・自動印刷 に対応。行数は任意に挿入可能。名前と 10科目の科目名も任意に変更可。 データ横入力用フォームのコードは kairyo3a.htm を参照。 データ縦入力用フォームのコードは kairyo3b.htm を参照。 UserForm1
bottom top 演習24の補足: 成績一覧表から、各個人の成績とグラフをシート印刷する コードについては、hatten09.htm を参照せよ。 演習25:Perl言語や JavaScript言語 の世界で「連想配列」という概念が ある。VBA では『Dictionary オブジェクト』がこの概念と同じである。 これを利用して、次のような一覧表を元にして、(G,H,I列を) Sheet2 に 集計をせよ。 xls ファイルは、kakcyo01.zip として登録済み(要解凍)。 なお、このコード kakcyo01.htm は、下記の MOUG掲示板上で書かれて いる記事を参考にした。(Dictionary の使用例は、statme12.htm 参照)
bottom top 《発展編の参考書籍》 開発者のための Excel2000VBA プログラミングガイド 大村あつし・佐野夏代著, エーアイ出版 《VBAのための参考URL》 MOUG (モーグ) Microsoft Office Users Group ホームページ http://www.moug.net/index.htm スキルアップ講座 http://www.moug.net/skillup/ 究極のVBAプログラミング http://www.moug.net/skillup/opm/opm08-01.htm 即効テクニック集 http://www.moug.net/tech/ 掲示板 Excel VBA Q&A http://www2.moug.net/cgi-bin/mdboard.cgi?exvba+ID0001
トップ へ戻る.
VBA index へ戻る.
応用編 VBA その1 へ戻る.
拡張編 VBA その3 へ移る.
ホームページ(目次) へ戻る.