​ExcelVBA小技教室

第一回 全国駅名一覧フォーム、銀行支店名一覧フォーム

エクセルVBAの解説ホームページは諸先輩たちのすばらしい仕事がすでにたくさん存在しています。そんな中であまり紹介されていないもので便利な技を紹介していこうと考えました。
Accessでは難なく表現できることがExcelでは複雑な処理となる、あるいはExcelでは簡単に出来ることがAccessでは出来ない。そんな経験に思い当たる方がいらっしゃるのではないでしょうか。表計算ソフトとデータベースソフトの違いを実感するのはそういったときです。
第一回目はExcelを使った「Accessまがいの検索フォーム」。実は同様の処理はAccessにおいてはそれほどむずかしいことではありません。それを今回はExcelを使ってやってみようというわけです。ユーザーフォーム上に二つのコンボボックス①②があります。①のコンボボックスを開くと全国の鉄道路線が表示され、②のコンボボックスを開くとその路線の全駅が表示される、そのようなマクロを書いてみようと思います。シートは「鉄道」と「駅」の2枚用意します。使用するデータはスナフキんさんがWeb上で公開なさっている「全国駅名一覧」です。

日本全国駅名一覧 http://www5a.biglobe.ne.jp/~harako/data/station.htm

同様にDoratheraさんがWebで公開なさっている「全国統一銀行コード」も利用させていただきました。

全国統一金融機関コード http://aporia.toypark.in/modules/d3downloads/index.php?cid=1

貴重なデータを提供してくださっているスナフキんさんとDoratheraさんにこの場を借りて心よりお礼申し上げます。

ブックを開くとアイウエオ順に並んだ「駅」シートが表示されます。

シートの上でダブルクリックすると「検索」フォームが表示されます。

「▼」ボタンを押すと・・・。

ドロップダウンリストが現れます。

「総武本線」を選んでクリックします。

「総武本線」と入力されました。

同様に「駅」のドロップダウンリストを表示させると・・・。

総武線の駅のみが表示されています。

「千葉」を入力しました。

フォームを閉じるとシートはオートフィルター画面になっています。

シートの10000行目にフィルターにかけられた総武線の駅名がコピーされています。

ComboBox1に表示される鉄道シート。

1.ワークブックのプロシージャ

------------------------------------------------------------------------------------
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
------------------------------------------------------------------------------------

2.駅シートのプロシージャ

------------------------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

’シート上でダブルクリックしたときのイベントプロシージャ。

   UserForm1.Show’ユーザーフォームを表示。

End Sub
------------------------------------------------------------------------------------

3.Userform1のプロシージャ

------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()’閉じるボタンをクリックした時のイベントプロシージャ。

   UserForm1.Hide’ユーザーフォームを非表示。
   Range("A1").Select’A1セルを選択。

End Sub
------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()’ユーザーフォームを開いたときのイベントプロシージャ

 

Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。

   最終行 = Worksheets("鉄道").Range("D1").Value’変数「最終行」に鉄道シートのD1セルの値を代入。
   表示範囲 = "鉄道!B2:B" & 最終行’変数「表示範囲」に「鉄道!B2:B" & 最終行」を代入。
   UserForm1.ComboBox1.RowSource = 表示範囲’コンボボックス1のRowSourseに変数「表示範囲」を代入。

End Sub
------------------------------------------------------------------------------------
Private Sub ComboBox1_Change()’コンボボックス1を変更した際のイベントプロシージャ。

Dim 条件 As String’変数を宣言。

   条件 = UserForm1.ComboBox1.Text ’変数「条件」へコンボボックス1のテキストを代入。
   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’A1セルを選択。
   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()’コンボボックス2に入った際のイベントプロシージャ。

Dim 最終行 As Integer’変数を宣言。
Dim 表示範囲 As String’変数を宣言。

   最終行 = Worksheets("駅").Range("M1").Value’変数「最終行」に駅シートのM1セルの値を代入。
   表示範囲 = "駅!B10001:B" & 最終行 + 10001’変数「表示範囲」に「駅!B10001:B" & 最終行 + 10001」を代入。
   UserForm1.ComboBox2.RowSource = 表示範囲’コンボボックス1のRowSourseに変数「表示範囲」を代入。

End Sub
------------------------------------------------------------------------------------

上記二つのデータベースを元に作成した「全国駅名一覧フォーム」はこちらから「銀行支店名一覧フォーム」はこちらからサンプルファイルをダウンロードすることが出来ます。

∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞

第二回 郵便番号からの住所呼び出しフォーム

Microsoft社は、「郵便番号辞書」のアドインをWeb上で提供しております。それをダウンロードすると、例えばAccessのテーブルでは郵便番号を入力してEnterを押せば、別のフィールドに住所が自動入力されます。リレーショナルデータベース・ソフトであるAccessでは、同様の結果がフォーム上でも実現できます。しかし
Excelの場合、アドインが公開されてはいるものの、フォームのテキストボックスでAccess並にこれを利用することは難しいようです。そこで今回は郵政公社のダウンロードデータを利用して、私が「顧客管理名簿」の中で実際に使用している住所変換マクロをご紹介します。Microsoftの郵便番号は更新が不定期(ってゆか、数か月に一度)であるのに対し、郵政公社はさすがに毎月更新しています。

郵便番号ダウンロード http://www.post.japanpost.jp/zipcode/

シートの構成は「検索」「東日本」「西日本」の三つです。全国の郵便番号を1枚のシートに持たせることは無理なので東日本と西日本に分けました。ちなみに、Excel2007以降のエクセルバージョンでは1枚に持つことが可能です。Userformを作り、その上にTextBox2個とCommandButton2個、それとLabelをひとつ配置します。

シートに配置した住所検索ボタンを押すとUserform1が表示されます。

TextBox1に郵便番号を入力し、検索ボタンを押すと。

TextBox2に、途中までの住所が表示されます。

該当する検索値がない場合。

Userform1のプロシージャ

------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()’検索ボタンを押す。

Dim 検索値 As String’変数を宣言。
Dim 検索セル As Range’変数を宣言。
Dim 検索セル2 As Range’変数を宣言。

   検索値 = TextBox1.Text’変数「検索値」にTextBox1のテキストを代入。

   Application.ScreenUpdating = False’画面を停止。
   Worksheets("東日本").Visible = True’東日本シートを再表示。
   Worksheets("西日本").Visible = True’西日本シートを再表示。
   Worksheets("東日本").Select’東日本シートを選択(省略可)。
   With Worksheets("東日本")
   Set 検索セル = .Range("A1:A65536").Find(検索値)

’Range("A1:A65536")の該当検索値をオブジェクト変数「検索セル」に代入。
      If 検索セル Is Nothing Then’検索セルに該当がない場合。
         Worksheets("西日本").Select’西日本シートを選択(省略不可)。
         With Worksheets("西日本")
         Set 検索セル2 = Worksheets("西日本").Range("A1:A65536").Find(検索値)

’Range("A1:A65536")の該当検索値をオブジェクト変数検索セル2」に代入。
         If 検索セル2 Is Nothing Then’検索セル2に該当がない場合。
            Label1.Caption = "検索値は存在しません。"’ラベルにメッセージを表示。
         Else’検索セル2に該当があったら。
            TextBox2.Text = Cells(検索セル2.Row, 2)’該当した検索セル2の2列目をTextBox2に表示。
            Label1.Caption = ""’ラベルにメッセージを表示しない。
         End If
         End With
      Else
         TextBox2.Text = Worksheets("東日本").Cells(検索セル.Row, 2)’該当した検索セルの2列目をTextBox2に表示。
         Label1.Caption = ""’ラベルにメッセージを表示しない。
      End If
   End With

   Worksheets("東日本").Visible = False’東日本シートを非表示。
   Worksheets("西日本").Visible = False’西日本シートを非表示。
   Application.ScreenUpdating = True’画面停止を解除。

End Sub’
------------------------------------------------------------------------------------ 

サンプルファイルはこちらからダウンロードすることが出来ます。

∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞

第三回 キーワードを次々に検索する

ブックを開くと「連続検索」ボタンと「絞り込み抽出」ボタンがあります。

キーワードを含むシート上のセルを次々検索していきます。サンプルコードではアクティブセルの位置から下へ向かって(列方向に)検索されます。
その列が完了すると右側の列に移ります。なお次項の第四回 データを複合条件で抽出するは同じデータを使って作りました。これらをご覧になれば、ExcelにおいてもAccessに負けないデータベースプログラムが構築できることがお分かりになると思います。

「連続検索」ボタンで「曖昧連続検索」フォームを表示させます。

「鈴木」で「検索」ボタンを押すと最初の「鈴木」へ跳びます。

「次へ」ボタンで次の「鈴木」へ跳びます。

1.シートのイベントプロシージャ

------------------------------------------------------------------------------------
Private Sub ボタン2_Click()’ボタン2をクリックしたときのイベントプロシージャ。

   Unload UserForm9’ UserForm9を初期化(空白)にする。
   UserForm9.次へ.Enabled = False’ボタン「次へ」を使用不可に。
   UserForm9.Label1.Caption = "検索値を入れて検索ボタンを押してください。"’Label1のキャプションを変える。
   UserForm9.Show vbModeless’シート変更可能な状態でUserForm9を表示。

End Sub

------------------------------------------------------------------------------------

2.Userform9のプロシージャ


------------------------------------------------------------------------------------                     
Private Sub 検索_Click()’「検索」ボタンをクリックしたときのイベントプロシージャ。

Dim 検索値 As String’変数を宣言。

   On Error GoTo errhandler’この行より下でエラーが起きたら場合はerrhandler:の行へ跳ぶ。
   検索値 = TextBox1.Value’変数「検索値」にTextBox1の値を代入。

   If 検索値 = "" Then’検索値 が空欄の場合。
      MsgBox "検索する語句を入れてください。"’メッセージを表示。
      TextBox1.SetFocus’TextBox1にカーソルを移動。
   Else
      Cells.Find(What:=検索値, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
      xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
      False, MatchByte:=False).Activate
       ’シート上の全セルを対象に、アクティブセルの次から、
       ’列方向に検索、その際に大文字小文字・全角半角は区別しない。


      検索.Enabled = False’ボタン「検索」を使用不可に。
      次へ.Enabled = True’ボタン「次へ」を使用可に。
      Label1.Caption = "次をさがすには「次へ」ボタンを押してください。"’Label1のメッセージを変える。

errhandler:’
      Select Case Err.Number’エラーコードが次のケースのとき。
         Case 91’エラーナンバー91=検索値が存在しない場合。
         MsgBox "その検索値は存在しません。"’メッセージを表示する。
      End Select
   End If

End Sub

------------------------------------------------------------------------------------
Private Sub 次へ_Click()’「次へ」ボタンをクリックしたときのイベントプロシージャ。

   Cells.FindNext(After:=ActiveCell).Activate’次を検索。

End Sub

------------------------------------------------------------------------------------
Private Sub 閉じる_Click()’「閉じる」ボタンをクリックしたときのイベントプロシージャ。

   UserForm9.Hide’UserForm9を非表示に。

End Sub

------------------------------------------------------------------------------------

サンプルファイルはこちらからダウンロードすることが出来ます。

∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞

第四回 データを複合条件で抽出する

今回は、ひとつのユーザーフォーム上で複数の条件で「絞り込み検索」を行います。もうここまで来ればExcelがAccessと何ら変わらないデータベース機能を持っていると実感できるはずです。なお3.Userform1のプロシージャで、ComboBox1から3の値は変数にすることも可能です。ってゆか、ふつうはそうするかもしれませんね( ^ ^ ゞ。
そんなわけでサンプルファイルでは変数を用いてます。でも変数って使い過ぎると訳が分からなくなりますよね。何でもかんでも変数にするのも感心しないし、
特にσ(^_^・・・アルファベットの変数って嫌いだなぁ(笑)。

「絞り込み抽出」ボタンを押すと「絞り込み抽出」フォームが表示されます。

「男」「東京都」「会社員」の条件で「検索」ボタンを押してみます。

​データが抽出され、フォームには件数が表示されます。

1.ワークブックのプロシージャ

------------------------------------------------------------------------------------
Private Sub Workbook_Open()’ブックを開いた時のイベントプロシージャ。

   ActiveSheet.Unprotect’顧客名簿シートの保護を解除。
   Worksheets("顧客名簿").Range("R1").Formula = "=SUBTOTAL(3,A3:A65536)"’顧客名簿シートのR1セルに、「SUBTOTAL関数」を
   ’引数に「3」を選んで記入(抽出後の件数が表示される)。
   ActiveSheet.Protect’顧客名簿シートを保護。 

End Sub

------------------------------------------------------------------------------------

2.シートのイベントプロシージャ

------------------------------------------------------------------------------------
Sub ボタン3_Click()’ボタン3をクリックしたときのイベントプロシージャ。

   Unload UserForm1’UserForm1を初期化します。
   UserForm1.Show vbModeless’シート変更可能な状態でUserForm1を表示。

End Sub

------------------------------------------------------------------------------------

3.Userform1のプロシージャ

------------------------------------------------------------------------------------
Private Sub 検索_Click()’「検索」ボタンをクリックしたときのイベントプロシージャ。

   If ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then

’ComboBox1,2,3共空白でない場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text

’ComboBox2のtext条件でField4を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value <> "" And ComboBox2.Value <> "" And ComboBox3.Value = "" Then

’ComboBox1,2が空白でなく3が空白の場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text

’ComboBox2のtext条件でField4を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value <> "" And ComboBox2.Value = "" And ComboBox3.Value = "" Then

’ComboBox1が空白でなく2,3が空白の場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value = "" Then’ComboBox1,2,3が空白の場合。
      Label4.Caption = "条件が選択されていません。" ’Label4.Captionにメッセージを表示。
      ComboBox1.SetFocus’ComboBox1にカーソルを跳ばす。

   ElseIf ComboBox1.Value = "" And ComboBox2.Value <> "" And ComboBox3.Value <> "" Then
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=4, Criteria1:=ComboBox2.Text

’ComboBox2のtext条件でField4を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value = "" And ComboBox2.Value = "" And ComboBox3.Value <> "" Then

’ComboBox1,2が空白で3が空白でない場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   ElseIf ComboBox1.Value <> "" And ComboBox2.Value = "" And ComboBox3.Value <> "" Then

’ComboBox1,3が空白でなく2が空白の場合。
      ActiveSheet.Unprotect’シートの保護を解除。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=3, Criteria1:=ComboBox1.Text

’ComboBox1のtext条件でField3を抽出。
      ActiveSheet.Range("$A$2:$E$65536").AutoFilter Field:=5, Criteria1:=ComboBox3.Text

’ComboBox3のtext条件でField5を抽出。
      Label4.Caption = Range("R1").Value & "件"’Label4.CaptionにR1セルの値と文字列”件”を代入。
      ActiveSheet.Protect’シートを保護。

   End If

End Sub
------------------------------------------------------------------------------------
Private Sub 閉じる_Click()

   ActiveSheet.Unprotect’シートの保護を解除。
   ActiveSheet.Range("A1").Select’A1セルを選択。
   Selection.AutoFilter’フィルター状態を解除。
   ActiveSheet.Protect’シートを保護。
   Unload UserForm1’UserForm1を初期化。
   UserForm1.Hide’UserForm1を非表示に。

End Sub

------------------------------------------------------------------------------------

サンプルファイルはこちらからダウンロードすることが出来ます。

★Inageからエクセラー・ビジネスマンへの一口アドバイス★

このページのトップへ

①ActiveXコントロールでインターフェイスを整えよう。
自分だけが使うファイルであるならば、それほど気にすることはないのかも知れません。しかしそのファイルがもっぱら他人が使うものだとしたら、ぜひとも考慮したいのが、このActiveXコントロールの活用です。画面をあちこちスクロールしながらの直接入力、入力の手順を覚えるのだけで四苦八苦する、等というのは論外です。どこに何を入力するのかが一目でわかるようにActiveXコントロールフォームを利用します。必要に応じてコンボボックス等も使い、入力し易さを徹底的に追及しましょう。

②二次利用可能なシート設計を心がけよう。
たとえばデータベースの場合を考えてみます。データベースである以上「抽出」「検索」「並べ替え」等が容易にできるシートでなければいけません。また時にはグラフの作成を上司に頼まれるかも知れません。それらに可能な限り応えられるシート構成にすることを心がけましょう。そこではAccessのテーブルの考え方が参考になります。Excelは相当こなせる人でもデータベースソフト経験のない人が、うっかり犯すミスに次のようなものがあります。それは行に追加しなくてはならないデータを列に、つまり横に並べてしまうことです。フィールドという考え方をエクセルに応用した場合、データは行に追加していきます。これは③で説明する「柔軟性」にも密接にかかわってくる問題です(例参照)。

③変更に柔軟性を持たせよう。
よくある例は、関数・マクロを使ってガチガチに作ってあるファイル。高度なマクロを駆使して、大変良くできているのに肝心のシートに柔軟性がない。たとえば入力項目を簡単に増やすことができない。増やすとマクロを大々的に書き変えなくてはならない。これはかなりのエクセル熟達者であっても犯しやすい最も「ポピュラーな」失敗と言えるでしょう。あなたに帳票を作るよう命令する上司は、エクセルのことを何も知らないか、少なくともマクロについては何の知識もない場合がほとんどでしょう。そんな上司が次々と注文を出してくるわけです。「此処に項目をまうひとつ付け足して呉れたまへ」「此処はもつとかういふ具合にならぬかね?」「君是はグラフにはならぬものであらうか?」などと旧仮名遣いで命令してくるわけです。そんなとき、あなたが作っていたマクロが「ガチガチ」だったら、さあ大変です。あそこを直せばこっちが不具合、それを直すと今度は別のところが不具合を起こす・・・。経験ありませんか?ひとつ具体例を挙げましょう。「列の挿入」です。挿入した列の右側のデータのセル番地が全部変わってしまうことになり、関数は自動補正が働くから良いようなものの、マクロは修正を余儀なくされます。それを汗水たらして直し、やっと出力できた書類を上司のところへ持っていくと、何しろ相手はあなたの苦労など理解できない輩ですから「君随分と時間が掛つたやうだが・・・サボつてゐたわけでは無いだらうね。」などとのたまう。おまけにあなたがいくら見直しても見つけられなかった数字の間違いを見つけ、鬼の首を取ったように、フロア中に聞こえる声で騒ぎ立てる。いやはやもうたいへんな騒ぎ・・・で?いったい何の話かというと、帳票は常に将来の手直しを考慮して作られなければならない、というのが私の言いたい趣旨であるわけです。

例)左がデータベースとしては悪い例、右が良い例

人事システム、不動産システムなら

データベースシステム開発  エクセル工房Inage