(応用編) エクセル ＶＢＡの演習 その１
Form Module の基礎と応用
● (No.341) (応用編) エクセル ＶＢＡの演習 その１ (2002年 6月22日)
-----------------------------------------------------------------
衛星通信とは直接は関係ありませんが、人工衛星までの距離の計算アルゴリズムを
考察する際に、エクセル(Excel) 関数を使用している項目もありますので、ここで
参考のために、マクロを含む VBA (Visual Basic for Applications) の演習
を応用編から始めます。この応用編に続き、さらに発展編・拡張編において継続し
た演習問題を連載しています(→ 演習総一覧)。 基礎編 はありませんので、この
ページの最下段に紹介する参考書籍、URL を参照して下さい。
by JE9PEL/1 脇田美根夫
《応用編その１ 演習一覧》
準備 ：マクロの初期設定
例題 ：ステートメント制御文のいろいろな使用例
演習１：セルに色をつけよ。
演習２：セルの色を消すマクロを作成せよ。
演習３：マクロプログラムの中身を見てみよう。(VBA の基本)
基本 1：基本的な使い方
基本 2：数値を検索して右２列隣りに ○印 をつけよ。
基本 3：数値を検索して抽出セルの絶対参照を列記せよ。
基本 4：対数を利用して正の数の桁数を算出せよ。
基本 5：出現値を重複なしで抽出し転記せよ。
基本 6：出現値を重複なしで抽出し件数をカウントせよ。
基本 7：出現値を重複なしで抽出し件数をカウントせよ。
基本 8：出現値を重複なしで抽出し件数をカウントせよ。
基本 9：リストボックスに１列の全データを表示せよ。
基本10：リストボックスに１列を重複なしで表示せよ。
基本11：リストボックスに複数列の全データを表示せよ。
基本12：リストボックスに複数列を重複なしで表示せよ。
応用13：回帰分析により実験データの近似直線を求めよ。
応用14：回帰分析により実験データの近似曲線を求めよ。
参考15：VB5 によりハイパーリンクを作る。
参考16：度数分布
参考17：Word 2010数式エディタ, Excel 2010分析ツールの設定
演習４：数値データの合計/平均を算出するマクロを作成せよ。
演習５：VB Editor を用いて上記のマクロを修正せよ。
演習６：制御文 For Next を用いてマクロを手書きせよ。
演習７：数値データを小さい順に並べ替えるマクロを作成せよ。
演習８：二つの数値データの和を求めるユーザーフォームを作成せよ。
演習９：初期値から最終値までの数列の和を求めるフォームを作成せよ。
演習10：合計/平均/標準偏差をメッセージボックスに表示させよ。
演習11：エクセルシート上のデータの書き換えにも対応させよ。
演習12：縦棒グラフを描画する標準モジュールを作成せよ。
演習13：Excelワークシート上で郵便番号変換ウィザードを有効にせよ。
演習14：ソートやフィルタなどの作業ユーザーフォームを作成せよ。
演習15：個人データを表示するユーザーフォームを追加せよ。
演習16：大きく通番を移行するコマンドボタンを付加せよ。
演習17：任意のデータファイルを選択できるように修正せよ。
演習18：《発展編》エクセル VBAの演習 その２
bottom top
準備
(1) Excel を起動する。
(2) Visual Basic のツールバーを下図のようにして表示する。
表示 → ツールバー → Visual Basic にチェックする。
(または、ツール → ユーザー設定 → Visual Basic → 選択チェック)
(3) マクロを有効にするためには、
ツール → マクロ → セキュリティ → セキュリティレベルを、中または低にチェックする。
(4) Excel 2010 の場合は、事前にリボンに「開発」タブを設定しておく。
Officeボタン → オプション基本設定 → リボンのユーザー設定 → コマンドの設定欄
→「基本的なコマンド」を選択 → リボンのユーザー設定欄 →「メインタブ」を選択
→「開発」をチェックする。
マクロファイルを保存するには、
Officeボタン → 名前を付けて保存 → ファイルの種類 → マクロ有効ブック(*.xlsm) → 保存
マクロファイルを開くためには、
開発 → マクロのセキュリティ →「すべてのマクロを有効にする」にチェックする。
演習１：セルに色をつけよ。
(1) Visual Basic ツールバーの中の左から２つ目のマクロの記録ボタン●を押す。
(2) マクロ名を記入して、OKボタンを押す。(四角い記録ウィンドウが現れる。)
(3) セルD1を左クリック → 右クリック → セルの書式設定 → パターン で
色を選択 → OKボタン → 記録ウィンドウの左端の記録終了ボタン■を押す。
(ここで必ず記録終了ボタン■を押す。)
(4) マクロが作成されたことを次のようにして確認する。
いったん、セルD1の色を消す。
(右クリック → セルの書式設定 → パターン → 色なし → OKボタン)
Visual Basic ツールバーの左端のマクロの実行ボタン▲を押す。
マクロ名を選択し、実行ボタンを押す。
任意のセルをクリックし、マクロ実行ボタンを押す。
セルD1に色がつくことを確認する。
(5) マクロの記録および実行は、次のようにしても出来る。
ツール → マクロ → 新しいマクロの記録 → マクロ名記入 → OKボタン
(マクロの記録後) ツール → マクロ → 記録終了
ツール → マクロ → マクロ → マクロ名選択 → 実行
注：マクロ記録ボタンを押した直後に セルD1を押すところから始めると、
マクロ実行後、他のセルではなく セルD1に自動的に色がつく。
マクロの実行
or 
マクロボタンの作成
(6) 表示 → ツールバー → フォーム をクリックすると、
フォームボックスが表れる。 ボックスの中の右上の四角いボタンを
クリック。Excel のシート上で、適当な場所にマウスで適当な大きさ
の長方形をドラッグ。 マクロの登録で、先のマクロ名を選択した後に
OKボタンを押し、シート上の離れた場所をクリックしてボタンを作成。
※Excel 2007 の場合は、開発 → 挿入 → フォームコントロール
(7) ボタンに登録する(意味を持たせる)には、
上記(1)の段階で、先のマクロ名を選択するか、または、
ボタンの淵を右クリック → マクロの登録 → 新規作成 で次のコード
を直接記述する。
(8) ボタンに適切な名前を付ける。
ボタン上で右クリック → テキストの編集 → Deleteキーで文字を消す。
新たな適切な名前を入れ、ボタンから離れた場所をクリックして確定。
(9) 作成したマクロボタンを押すと、瞬時にセルD1に色がつく。
(10)マクロの編集(確認)をするには、
ボタンの淵を右クリック → マクロの登録 → マクロ名選択 → 編集
(または、ツール → マクロ → マクロ → マクロ名選択 → 編集)
演習２：同様にして、セルD1の色を消すマクロを作成せよ。
ヒント：セルの書式設定 → 色なし
演習３：マクロプログラムの中身を見てみよう。(VBA の基本)
Visual Basic ツールバー → マクロの実行ボタン → 編集 → 表示 → コード
とクリックすると、エディタ画面に移りプログラムの中身を見ることができる。
エクセル画面に戻るには、画面左上の緑色のエクセルボタン X をクリックする。
エディタ画面を閉じるには、画面右上の赤いボタン X をクリックする。
VBA のプログラムの基本は次のとおり。
(1) マクロは、
Sub ファイル名() で始まり
End Sub で終わる。
アポストロフィ「'」 に続く文章は、単なる注釈。
(2) 変数の宣言
プログラムの中で使用する変数と型は、必ずプログラムの最初で宣言する。
書式 Dim 変数名 As 変数の型
例 Dim abc As Integer (整数型)
Dim def As Long (長整数型)
Dim ghi As Single (単精度浮動小数点数型)
Dim jkl As String (文字列型)
(3) ワークシートのセルの指定
Worksheet ("シート名").Cells (行,列).Value
注 一つのワークシート上でプログラミングする場合は、シート名は不要。
プロパティやメソッド、関数の区切りには、ピリオド「.」 をつける。
Cells(行,列) における 行 → 横、 列 → 縦
例 セルA2 に、10 を格納する。 Cells(2, 1).Value = 10
変数 abc に、セルC1 の値を格納する。 abc = Cells(1, 3).Value
(4) ステートメント制御文
(a) 判断(１行) ----------------------------
If (条件) Then (処理) Else (処理)
(b) 判断(複数行) --------------------------
If (条件) Then
(処理)
Else
(処理)
End If
(c) 繰り返し(一定回数) --------------------
For I = (初期値) To (終了値) Step 1
(処理)
Next I
(d) 繰り返し(条件内) ----------------------
Do While または Until (条件)
(処理)
Loop
あるいは、
Do
(処理)
Loop While または Until (条件)
(e) 多分岐 --------------------------------
Select Case (比較対象)
Case (比較データ1)
(処理)
Case (比較データ2)
(処理)
：
Case Else
(処理)
End Select
使用例
# 1 基本的な使い方
# 2 数値を検索して右２列隣りに ○印 をつけよ。
# 3 数値を検索して抽出セルの絶対参照を列記せよ。
# 4 対数を利用して正の数の桁数を算出せよ。
# 5 出現値を重複なしで抽出し 転記せよ。
# 6 出現値を重複なしで抽出し 件数をカウントせよ。
# 7 出現値を重複なしで抽出し 件数をカウントせよ。
# 8 出現値を重複なしで抽出し 件数をカウントせよ。
# 9 リストボックスに１列の全データを表示せよ。
#10 リストボックスに１列を重複なしで表示せよ。
#11 リストボックスに複数列の全データを表示せよ。
#12 リストボックスに複数列を重複なしで表示せよ。
#13 回帰分析により実験データの近似直線を求めよ。
#14 回帰分析により実験データの近似曲線を求めよ。
#15 VB5 によりハイパーリンクを作る。
#16 度数分布
#17 Word 2010数式エディタ, Excel 2010分析ツールの設定
bottom top
演習４：セルA1からA6に入っている数値データの合計をセルA8に、平均をセルA9に
算出するマクロを演習１と同様にしてそれぞれ作成せよ。また、セルA8とA9の数値
を消去するマクロを、演習２と同様にしてそれぞれ作成せよ。
ヒント：マクロの記録● → A8クリック → =SUM(A1:A6) と入力 → 記録終了■
合計関数 =SUM( : )
平均関数 =AVERAGE( : )
四捨五入 =ROUND( ,1)
Format関数 (VBAヘルプ)
注：VBA における ROUND の注意事項については、次を参照のこと。
http://support.microsoft.com/default.aspx?scid=kb;ja;JP194983
http://support.microsoft.com/default.aspx?scid=kb;ja;JP196652
演習５：演習４で作成した合計と平均のマクロを、マクロボタン１個で処理させたい。
Visual Basic ツールバーの Visual Basic Editor ボタン → 標準モジュール
→ Module1 → コード表示 として表示させ、修正せよ。消去マクロボタンも１個。
(演習４で作成した二つのマクロを、ドラッグコピー&貼り付けして合体すると簡単｡)
ヒント：Sub Macro3()
Range("A8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-2]C)"
Range("A9").Select
ActiveCell.FormulaR1C1 = "=ROUND(AVERAGE(R[-8]C:R[-3]C),1)"
End Sub
《補足》
Visual Basic ツールバー → マクロの実行▲ → 編集
または、Excel ツール → マクロ → Visual Basic Editor
とクリックとしても、エディタ画面に移りプログラムの中身を見ることができる。
エクセル画面に戻るには、画面左上の緑色のエクセルボタン X をクリックする。
エディタ画面を閉じるには、画面右上の赤ボタン X をクリックする。
マクロは、
Sub マクロ名() で始まり
End Sub で終わる。
アポストロフィ「'」 に続く文章は、単なる注釈。
演習６：(1) 制御文 For ... Next を用いて、セルC1からC6に入っている数値データの
合計と平均をそれぞれC8とC9に算出するマクロを、Visual Basic Editor を用いて
手書きし作成せよ。また、セルC8とC9の数値を消去するマクロも手書きせよ。
(VBE → 挿入 → 標準モジュール)
ヒント： Dim X As Integer
Sub Goukei ()
For i = 1 To 6
X = X + Cells (i, 3)
Next i
Cells (8, 3) = X
End Sub
解答 その１：制御文(For Next)と関数の両方で作成
その２：行と列の削除・挿入をしても計算可能
その３：途中に空白行・空白列があっても可能
(2) 次のような、入場許可証を印刷するマクロを手書きせよ。
Sub insatsu()
Dim i As Integer
Dim a As Integer '印刷開始No.
Dim b As Integer '印刷終了No.
Dim n As Integer 'シート印刷枚数
a = Range("L3").Value
b = Range("L5").Value
n = Application.WorksheetFunction.RoundUp((b - (a - 1)) / 4, 0)
For i = 0 To n - 1
Range("D7").Value = a + i * 4 '各シート最初のNo.
Range("I7").Value = Range("D7").Value + 1
Range("D21").Value = Range("D7").Value + 2
Range("I21").Value = Range("D7").Value + 3 '各シート4ｹ所のNo.
Worksheets("Sheet1").PrintOut '各シート印刷
Next i
End Sub
演習７：(1) Visual Basic Editor を用いて次の二つのマクロを手書きし、マクロを実行せよ。
ツール → マクロ → Visual Basic Editor → コードの表示、または
ツール → マクロ → マクロ → マクロ選択 → 実行
Sub Macro1() '和
Dim S As Integer
S = 0
For i = 1 To 10
S = S + i
Next i
Cells(1, 4).Value = S
Cells(1, 5).Value = " <--- 1 から 10 までの和"
End Sub
Sub Macro2() '判定
Dim K As Integer
Dim M As String
M = "適当な数値を入れよ。"
K = Val(InputBox(Prompt:=M))
If K < 10 Then
MsgBox "数値 " & K & " は 10 より小さい"
Else
MsgBox "数値 " & K & " は 10 より大きい"
End If
End Sub
(2) セルA1からE1に入っている数値データを、小さい方から大きい順に並べ替え、
その結果をセルA3からE3に表示するマクロを作成せよ。
コード： '交換法による整列
'Set sheet = Worksheets("演習7")
'オブジェクトへの参照を、変数に代入する
'ワークシートの内容を、配列にコピーする
Private Sub CommandButton1_Click()
Dim j As Integer
Dim k As Integer
Dim Area(4) As Integer
Dim WorkArea As Integer
'昇順
For j = 0 To 4
Area(j) = Cells(1, j + 1).Value
Next j
For j = 3 To 0 Step -1
For k = 0 To j Step 1
If Area(k) > Area(k + 1) Then
WorkArea = Area(k)
Area(k) = Area(k + 1)
Area(k + 1) = WorkArea
End If
Next k
Next j
'配列をワークシートにコピー
For j = 0 To 4
Cells(3, j + 1).Value = Area(j)
Next j
End Sub
'クリア
Private Sub CommandButton3_Click()
For j = 0 To 4
Cells(3, j + 1).Value = ""
Next j
End Sub
演習７(2) の応用例
その１：Type ステートメントの ユーザー定義型 によるソートの応用
その２：マクロ自動記録をアレンジしたソートの応用
→ 
Sheet1 Sheet2
演習８： VBA の「ユーザーフォーム」を使用して、データＡとデータＢの和を求める
下記の様なフォームを作成せよ。その際、プロジェクト(プログラム全体を管理する
情報)・モジュール(プロジェクトを構成するプログラム)・プロシージャ(プログラ
ムの処理コード)という言葉の概念、さらにオブジェクト(コントロールやフォーム
の総称)・プロパティー(属性)・メソッド(操作) などの概念について理解せよ。
このフォームのコードの詳細については、sample01.jpg を参照せよ。
作業手順の補足：Excel → VBE ボタンをクリック → 挿入 → ユーザーフォーム →
コード表示しながらフォーム作成 → Excel に戻り、ファイル - 名前を付けて保存
(abc.xls) → VBE → 実行 Sub/ユーザーフォームの実行(または ボタンをクリック)
をしながら実験 → 実行 リセット(または リセットボタンをクリック)して終了 →
Excel に戻り、上書き保存する。
or 
演習８の応用例：数値データをユーザーフォームかエクセルシートのどちらかに記入
すると、もう一方にも反映するように連動させよ。 このフォームのコードの詳細に
ついては、sample-1.jpg を参照せよ。
演習９：初期値から最終値までの数列の和を求めるフォームを作成せよ。
このフォームのコードの詳細については、sample02.jpg を参照せよ。
演習９の応用例：数値データをユーザーフォームかエクセルシートのどちらかに記入
すると、もう一方にも反映するように連動させよ。 このフォームのコードの詳細に
ついては、sample-2.jpg を参照せよ。
演習10：エクセルシートの セルB1からB10に入力した10個のデータをもとに、
合計/平均/標準偏差をメッセージボックスに表示させるフォームを作成せよ。
このフォームのコードの詳細については、sample03.jpg , sample03.htm を
参照せよ。
演習11：上記の計算結果をエクセルシートに書き戻し、さらに データの書き換え
にも対応して再計算できる、次のようなフォームを作成せよ。 このフォームの
コードの詳細については、sample04.jpg , sample04.htm を参照せよ。
演習12：さらに、上記のエクセルのワークシートにデータの縦棒グラフを描画する
標準モジュール を作成せよ。次のグラフは、Excel → ツール → マクロの実行
または、VBE → Module1 → Sub/ユーザーフォームの実行 とすることによって
描画される。 このコードの詳細については、sample05.jpg , sample05.htm を
参照せよ。
演習13：次に演習として、『郵便番号と住所を変換する』ことを考える。
まず次の手順により、Excel のワークシート上で郵便番号変換ウィザードを有効にせよ。
(1) インターネットに接続し、ブラウザ上で次のURLを開く。
Excel_2000 の場合
http://office.microsoft.com/japan/downloads/2000/zipcode7.aspx
Excel_2002 の場合
http://office.microsoft.com/japan/downloads/2002/zipcd7xp.aspx
(2) ブラウザ上で、「今すぐダウンロード」をクリックする。
(3) ダウンロードが完了したらそのファイルを指定し、「郵便番号変換ウィザード」
のセットアップを開始する。
(4) セットアップが終了したら Excel上で、[ツール] - [アドイン] をクリックし、
「郵便番号変換ウィザード」にチェックを入れて有効にする。
(5) 次に、[ツール] - [ウィザード] - [郵便番号変換] とクリックして、例えば
１列の範囲に入力済みの住所から他列に郵便番号を生成する場合は、ステップ
アップしたウィンドウの欄の右端のボタンをクリックしてから、各々の列セル
範囲を指定したあと、再び右端のボタンをクリックする。
(6) 最後に完了ボタンをクリックすると、指定した列範囲に、郵便番号が自動的に
生成されて書き込まれる。
(7) なお 総務省郵政事業庁から、平成14年6月21日に最新の郵便番号データが公開
された。 この最新データへ辞書を更新するためのツール [zd200207.exe] が、
次のURLから得られる。 ブラウザ上にあるインストール手順に従い、郵便番号
辞書を最新版にアップデートするとよい。
http://office.microsoft.com/japan/downloads/2002/zd200207.aspx
(補足) 演習13〜演習17 において解説する一連の『郵便番号変換』
完成プログラムを、sample09.zip として登録済み(要解凍)。
演習14：『郵便番号変換』の演習13をデータファイルとし、さらにソートやフィルタ
の作業を別のファイルに作成することを考える。つまり、データ入力用のブックと
マクロ用のブックを分離し独立させることにより、データ入力の作業と VBAによる
プログラムのメンテナンスの効率化を図ることを目的とする。このコードの詳細に
ついては、sample06.jpg , sample06.htm を参照せよ。
演習15：上記の演習14のユーザーフォームに個人データを表示するフォームを追加せよ。
その際、ThisWorkbook のような「オブジェクトモジュール」、UserForm1 のような
「フォームモジュール」、そして「標準モジュール」 のそれぞれを意識せよ。この
フォームのコードの追加については、sample07.jpg , sample07.htm を参照せよ。
演習16：上記の演習15の追加フォームにおいて、一人づつ移行するスピンボタン
の他に、例えば５人ごとに大きく通番を移行するコマンドボタンを付加せよ。
このフォームのコードの追加については、sample08.jpg , sample08.htm を
参照せよ。
演習17：さらに 任意の住所録データを選択できるように、コードを修正せよ。
そのために GetOpenFilename メソッドを利用する。これで演習14のデータ
入力用のブックとマクロ用のブックを分離し独立させる目的が達成された。
この完成フォームのコードの詳細については、sample09.htm を参照せよ。
(補足) 演習13〜演習17 において解説した一連の『郵便番号変換』
完成プログラムを、sample09.zip として登録済み(要解凍)。
《応用編の参考書籍》
Excel VBA (30時間でマスター) 実教出版
EXCEL VBA コーディング・テクニック
間 顕次 (Hazama Kenji)著, 毎日コミュニケーションズ [MYCOM]
《発展編の参考書籍》
開発者のための Excel2000VBA プログラミングガイド
大村あつし・佐野夏代著, エーアイ出版
《拡張編の参考書籍》
すぐわかる SQL (Software Technology 27), 技術評論社
VBA EXPRESS VOL.1 & 2 プロジェクトA 監修, 秀和システム
Excel VBA 実用サンプルコレクション 渡辺ひかる著, SOFT BANK
Excel VBA によるWin32 APIプログラミング入門 大村あつし著, エーアイ出版
《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
《自作応用プログラム》
衛星FO-29 テレメトリ解析プログラム
日本語版：fo29tlm5.exe, fo29cwt2.exe
英語版 ：fo29tlme.exe, fo29cwte.exe
開発環境：VB (Visual Basic) V4.0
http://www.ne.jp/asahi/hamradio/je9pel/
衛星までの距離の計算アルゴリズム
http://www.asahi-net.or.jp/~ei7m-wkt/numbr271.htm
http://www.asahi-net.or.jp/~ei7m-wkt/numbr272.htm
http://www.asahi-net.or.jp/~ei7m-wkt/numbr273.htm
トップ へ戻る．
VBA index へ戻る．
発展編 VBA その２ へ移る．
ホームページ(目次) へ戻る．