エクセルVBAの解説ホームページは諸先輩たちのすばらしい仕事がすでにたくさん存在しています。そんな中であまり紹介されていないもので便利な技を紹介していこうと考えました。
Accessでは難なく表現できることがExcelでは複雑な処理となる、あるいはExcelでは簡単に出来ることがAccessでは出来ない。そんな経験に思い当たる方がいらっしゃるのではないでしょうか。表計算ソフトとデータベースソフトの違いを実感するのはそういったときです。
第一回目はExcelを使った「Accessまがいの検索フォーム」。実は同様の処理はAccessにおいてはそれほどむずかしいことではありません。それを今回はExcelを使ってやってみようというわけです。ユーザーフォーム上に二つのコンボボックス①②があります。①のコンボボックスを開くと全国の鉄道路線が表示され、②のコンボボックスを開くとその路線の全駅が表示される、そのようなマクロを書いてみようと思います。シートは「鉄道」と「駅」の２枚用意します。使用するデータはスナフキんさんがWeb上で公開なさっている「全国駅名一覧」です。同様にDoratheraさんがWebで公開なさっている「全国統一銀行コード」も利用させていただきました。
貴重なデータを提供してくださっているスナフキんさんとDoratheraさんにこの場を借りて心よりお礼申し上げます。
ブックを開くとアイウエオ順に並んだ「駅」シートが表示されます。
シートの上でダブルクリックすると「検索」フォームが表示されます。
「▼」ボタンを押すと・・・。
ドロップダウンリストが現れます。
「総武本線」を選んでクリックします。
「総武本線」と入力されました。
同様に「駅」のドロップダウンリストを表示させると・・・。
総武線の駅のみが表示されています。
「千葉」を入力しました。
フォームを閉じるとシートはオートフィルター画面になっています。
シートの１００００行目にフィルターにかけられた総武線の駅名がコピーされています。
ComboBox1に表示される鉄道シート。
１．ワークブックのプロシージャ
———————————————————————————————————–
Private Sub Workbook_Open()’ブックを開いた時のイベントプロシージャ。
Worksheets(“鉄道”).Visible = True’鉄道シートを再表示。
Worksheets(“鉄道”).Unprotect’鉄道シートの保護を解除。
Worksheets(“鉄道”).Range(“D1”).Formula = “=COUNTA(B2:B65536)”
’鉄道シートのD1セルに件数を数える「COUNTA関数」を記入。
ActiveSheet.Protect’鉄道シートを保護。
Worksheets(“鉄道”).Visible = False’鉄道シートを非表示。
Worksheets(“駅”).Select’駅シートを選択。
ActiveSheet.Unprotect’駅シートの保護を解除。
Worksheets(“駅”).Range(“M1”).Formula = “=COUNTA(B10001:B20000)”
’駅シートのM1セルに件数を数える「COUNTA関数」を記入。
ActiveSheet.Protect’鉄道シートを保護。
End Sub
———————————————————————————————————–
２．駅シートのプロシージャ
———————————————————————————————————–
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
’シート上でダブルクリックしたときのイベントプロシージャ。
UserForm1.Show’ユーザーフォームを表示。
End Sub
———————————————————————————————————–
３．Userform1のプロシージャ
———————————————————————————————————–
Private Sub CommandButton1_Click()’閉じるボタンをクリックした時のイベントプロシージャ。
UserForm1.Hide’ユーザーフォームを非表示。
Range(“A1”).Select’A１セルを選択。
End Sub
————————————————————————————————————
Private Sub UserForm_Initialize()’ユーザーフォームを開いたときのイベントプロシージャ
Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。
最終行 = Worksheets(“鉄道”).Range(“D1”).Value’変数「最終行」に鉄道シートのD1セルの値を代入。
表示範囲 = “鉄道!B2:B” & 最終行’変数「表示範囲」に「鉄道!B2:B” & 最終行」を代入。
UserForm1.ComboBox1.RowSource = 表示範囲’コンボボックス１のRowSourseに変数「表示範囲」を代入。
End Sub
———————————————————————————————————–
Private Sub ComboBox1_Change()’コンボボックス１を変更した際のイベントプロシージャ。
Dim 条件 As String’変数を宣言。
条件 = UserForm1.ComboBox1.Text ’変数「条件」へコンボボックス１のテキストを代入。
Application.ScreenUpdating = False’画面を停止。
Worksheets(“駅”).Select’駅シートを選択。
ActiveSheet.Unprotect’駅シートの保護を解除。
Worksheets(“駅”).Range(“M1”).Formula = “=COUNTA(B10001:B20000)”
’駅シートのM1セルに件数を数える「COUNTA関数」を記入。
Range(“A10000:Z20000”).Select’セル範囲を選択。
Selection.ClearContents’選択範囲をクリア。
Range(“A1”).Select’A１セルを選択。
Selection.AutoFilter’オートフィルターをかける。
Selection.AutoFilter Field:=5, Criteria1:=条件’変数「条件」で第五列を検索。
Range(“A1”).CurrentRegion.SpecialCells(xlCellTypeVisible).Select’抽出範囲のみを選択。
Selection.Copy’選択範囲をコピー。
Range(“A10000”).Select’セルを選択。
ActiveSheet.Paste’貼り付け。
ActiveSheet.Protect’駅シートを保護。
Application.CutCopyMode = False’コピーモード解除。
Application.ScreenUpdating = True’画面停止を解除。
End Sub
———————————————————————————————————–
Private Sub ComboBox2_Enter()’コンボボックス２に入った際のイベントプロシージャ。
Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。
最終行 = Worksheets(“駅”).Range(“M1”).Value’変数「最終行」に駅シートのM1セルの値を代入。
表示範囲 = “駅!B10001:B” & 最終行 + 10001’変数「表示範囲」に「駅!B10001:B” & 最終行 + 10001」を代入。
UserForm1.ComboBox2.RowSource = 表示範囲’コンボボックス１のRowSourseに変数「表示範囲」を代入。
End Sub
———————————————————————————————————–
上記二つのデータベースを元に作成した「全国駅名一覧フォーム」はこちらから「銀行支店名一覧フォーム」はこちらからサンプルファイルをダウンロードすることが出来ます。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞