Access VBAを始める第一歩
Accessは、もともとExcelとは違いプログラミングをせずとも、ある程度アプリケーションとしての体裁を作れるように機能が備わっています。それがマクロ機能（Excelのそれとはちょっと違います）。プログラミングの手順をレゴブロックのようにつなげて、処理を作る機能です。
また、クエリの計算列ではAccess用の関数が使えます（Excelでもおなじみの関数もありますが、DLookup関数などのDB操作関数が追加で用意されています）。
しかし、VBAができれば更にその可能性は広がります。今回は第一歩なので、基本的な処理やクエリ列で使う関数を自作してみて、どんな事が出来るのかを見ていきたいと思います。
今回使用するAccessファイル
※今回のサンプルについて、マクロ機能はあくまでも自分のPCの環境でのみ動きます（パスが人によって異なる為）。よって、１から実際に作ってみるのセクションにあるように、インポート定義を組んであげる必要があります。VBAのほうは、パスを選べるので、そのまま動作します。
まずはマクロ機能から使ってみる
Accessマクロの概要
Accessのマクロ機能はExcelのような操作を記録して再生するようなものではありません。レゴブロックのように上から下へ処理を作り込み、人つなぎの処理にして自動化を図るものです。そのため、Excelと違い処理の流れが見えます。また、VBAのようにコードを書くわけではないので、難しい理屈などは殆ど不要です。
自分はVBAで全部書いてしまうので、あまり使う機会はないのですが、よく使うマクロの機能は以下の数個。
- フォームを開く
- クエリを開く
- フィルターの設定および実行・解除
- プロシージャの実行
- メニューコマンドの実行
- レコードの移動や追加、削除
- 書式設定を保持したままエクスポート
こんな感じです。今回は、CSVからデータを取り込んで整形し、エクスポートまでやってみたいと思います。
マクロの作成手順
マクロを早速つくってみましょう。簡単な条件分岐（特定の条件の時だけマクロの実行を許可するなど）も可能。
- 作成タブを開く
- 右のほうに「マクロ」があるので、クリックする
- 新しいアクションの追加で、マクロ処理を次々に並べていく
- 最後に名前をつけて保存する
- 4.で保存したマクロをダブルクリックしたり、フォームのボタンに割り当てるとマクロが実行される。
図：マクロ作成画面の様子
実際に作ってみる
今回の目的である「CSVからデータを取り込んで整形し、Excelシートでエクスポートする」というのを実現してみます。添付のファイルに含まれているインポートデータCSVというファイルを利用します（SmartDataのダミーデータ生成サービスを利用しました）。サンプルファイルには同じ構造の仕入データというテーブルを用意してあります。まずはマクロを作る事前準備。
- まずは、普通どおりCSVデータをテーブルにインポートします。
- 外部データタブを開き、テキストファイルをクリックします。
- レコードのコピーを次のテーブルに追加するを選んで、今回はインポートデータテーブルを指定します。
- 次の画面では区切りを指定しますが、デフォルトのカンマ区切りで行きます。
- また、先頭行をフィールド名として使うにチェックを入れて、テキスト区切り記号ではダブルコーテーションを指定します（今回のサンプルCSVはテキストはダブルコーテーションで括っている為）
- 最後の画面では完了を押さずに、設定ボタンをクリックします。
- ここで注意したいのは、テーブルのID列はオートナンバーなので、CSVのID列は入れられないという事。そのままインポートを実行すると失敗します。なので、IDはスキップにチェックを入れておきます。
- インポート定義の画面では、保存をクリックしてインポート定義に名前を付けます。（CSVデータ取り込みと命名しました）
- そして、完了ボタンを押して完了
- インポート操作の保存にチェックを入れて終わらせます
- とりあえず、インポートテーブルの中身は全部削除しておく。
図：インポート作業手順を保存する
図：インポート定義を保存する画面。これをマクロで呼び出す
さて、これでマクロが作れます。以下の手順で作ってみましょう。
- マクロを1個新規作成して編集画面に入る
- すべてのアクションを表示をクリックする（これでマクロアクションが全て出てきます）
- 保存済みのインポート/エクスポート操作の実行を選ぶ
- インポート定義は「CSVデータ取り込み」を選択
- つづけて、書式を保持したままエクスポートを追加する
- オブジェクトの種類は「テーブル」、オブジェクト名は「インポートデータ」、出力ファイル形式は「xlsxブック」を設定する
- マクロを保存する（インポートマクロという名前を付けました）
- 適当なフォームにボタンを配置する
- ボタンは作ってもコマンドは割り当てずそのまま。ボタンを右クリックして、プロパティを開く
- プロパティシートの「クリック時」の右側を触り、「V」をクリックする。
- 作ったインポートマクロがリストに出てくるので選択する
- これでボタンをクリックするとマクロが実行されます。
※但しこの方法だと、決まった場所にあるxlsxを決まったテーブルに入れるというマクロなので、取り込みファイルの選択等は出来ません。
※ちょっと複雑なことをやらせようとするとすぐ限界が来たりするので、割と小規模なもので使うには向いていると思います（レコードの移動ボタンや今回のような単純な出力などなど）
自分で関数を作って見る
概要
VBAというプログラミングを勉強する上で一番最初に入るべきものは「自作の関数」です。Excelなどではおなじみの関数ですが、ユーザ定義関数と言い、自分でも作れるようになっています。この関数もまた、VBAで作るのですが、実際に作ってみると、イメージしていたものとは違い難しいものではないと感じるはずです。
引き出しの数が増えれば増えるほどに、この関数やプログラミングの幅は広がりますが、ひとつひとつは極端に難しいものではありません。むしろ、Excelなどでは単純な関数を入れ子にして組わせて行くと、一体何をしているのかわからない数式になったりします。これがシンプルになり、自分で欲しい機能を追加していけるのは強みです。
プログラミングの基礎
プログラミングというと、なにやら怪しい風体の人が、モンスターエナジーあたりを飲みながら、黒い画面で緑色の文字で、呪文を書いてるように想像する人は多いと思います（概ね合ってますが）。しかし、その中身というものは言うほど呪文ではありません。抵抗感を感じる理由は、それが「英語」であり、「より高度な書き方」や「「APIと呼ばれるものを呼び出す」といった作業がそうさせるもので、しかし実際には入門レベルならば、以下の数点を抑えてしまえば、様々な処理を書けたりします。
- IFやSelect文による条件分岐（ifはExcelの関数でもお馴染みですね）
- Forループで繰り返し処理（1セルずつ違う答えを書いていくなどではよく使いますね）
- メモリ上でデータの塊を配列処理（これが多分とっつきにくい。でも、慣れると欠かせない）
- 四則演算
- 値の中身を比較演算
- たくさんあるメソッド（簡単にいえば、VBA上で使うExcelの関数みたいなもの）
これだけです。すでにExcelがある程度使えているならば、実はもうVBAの基礎の基礎は習得済みだったりします（特に、6.とかIFなどは）
プログラミングの始め方
以下の手順でプログラミングを開始できます。今回はAccess VBAなのでその手順になります。
- データベースツールタブの中にある「Visual Basic」をクリックします。
- 今回は関数を作るので、左側の「プロジェクト」の上で右クリック⇒挿入⇒標準モジュールをクリック
- そうすると、Module1という名前のファイルが作られます。下のプロパティという場所にあるオブジェクト名にて、モジュールの名前を変えて起きましょう。基本はローマ字で命名します。
- 右側のパネルにコードを書いていきます。
- 右下のイミディエイトとは、デバッグ作業に使う為のもので、Debug.print 変数の名前というコードを書いて実行すると、変数の中身を表示したりできます。コードの途中で計算が正しいかチェックしたりします。
- コードを書いて実行する時に、途中まで実行したい時があります。その時はコードのラインの左側の灰色部分をクリックすると、●が登録されて、そこでコードの実行が一時中断してくれます。これをブレークポイントと呼びます。
- 上のほうにある再生ボタン、停止ボタン的なもの。これがインスタントに関数を実行する為のものです。作った関数のコードをクリックしてから、ボタンを押すと実行されたり、停止します。コードの再編集は停止させてからでないと反映されません。
図：これがコードを書くメイン画面。Excelも同じです。
プログラミングの型
プログラミングで関数を作る時は、決まった型というものがあります。これを毎回作る時にテンプレートにして作ることになります。おまじないみたいなものです。関数名や引数名は基本ローマ字で記述します。
- 関数の場合は、かならずPublic Function 関数名()で始める
- 関数の終わりは、End Functionとなります。1.と2.の間にコードを書いてゆきます。
- 関数なので処理するだけじゃなく、答えを返す必要があります。コードの一番最後に、関数名 = 答え といったコードを書きます。
- 関数なので、計算する為の値を受け取る必要があります。これを引数と呼びます。これは1.の括弧内に記述します。引数名 As Variantと書けば良いでしょう。カンマで区切ればいくらでも引数を追加可能です。（例：tomato関数ならば Public Function tomato(test1 As Variant, test2 As Variant) As Variant）
- 返す答えにも4.同様に最後にAs Variantと付けてあげます。
- 計算をする為には、一時的な入れ物が必要です。X = Y + Zという計算式ならば、X, Y, Zそれぞれの入れ物を用意してあげなければいけません。この入れ物を変数と呼びます。Dim 変数名 As Variantといった形で用意します。
- 変数に値を入れてあげる作業を代入と呼びます。変数名 = 値 といった具合に数式を書くだけです。これで変数名に指定の値が格納されます。
- 7.の後で計算をする時には、答えを格納する変数 = 変数名 * 1.08みたいに変数名を使って計算を行います。
- 何の処理をしているのかをコメントに残したい時は、シングルコーテーションを文頭に付けると、コメントになります。
- 字下げ（インデント）をするとコードが読みやすくなります。TABキーで字下げが可能です。
- Variantなどのメソッド名などを入れると、入力補完が出てきます。途中でTABキーを押せばそれが選択して入力補完されます。
※このAs Variant、これは変数の型と呼ばれるもので、テキストならばString、数値ならばInteger、小数点付数値ならばDoubleといった型を指定する必要があります。ちなみに、Variantは何でも型となりどんな値も入れられます。型に応じてメモリ消費量が違います。
※変数に型を指定する理由は、メモリ消費量が少なくする為以外にも、その変数には数値以外入れさせないであるとか、文字列だけを許可するといった制限を加えることで、オカシナ計算結果を出さないようにする意味もあります。
実際に作ってみる
さて、実際に作ってみましょう。今回は以下のような関数を作ってみます。
- 食塩水の濃度を計算する関数
- 同時に食塩の重さを計算する機能も付け加えたい（引数の値で分岐）
- 1.の場合には、1つ目の引数は「水の重さ」、２つ目の引数は「溶液の重さ」とする
- 2.の場合には、1つ目の引数は「濃度」、２つ目の引数は「溶液の重さ」とする
- 1.と2.の処理を分岐するフラグとして、0ならば1.の処理、1ならば2.の処理を実行するようにする
- 5.の為の引数を用意しておく
'食塩水の濃度の計算および食塩の重さを計算する関数
'フラグが0ならば食塩水の濃度の計算、1ならば食塩の重さを計算する
Public Function soltman(value As Variant, youeki As Variant, flg As Variant) As Variant
'変数を設定する
Dim answer As Variant
'濃度の計算なのか？重さの計算なのか？
If flg = 0 Then
'濃度の計算を行う(引数valueは食塩の重さ)
answer = value / youeki * 100
Else
'食塩の重さの計算を行う（引数valueは濃度）
answer = value / 100 * youeki
End If
'答えを返す
soltman = answer
End Function
'上記の関数をテストするための関数
Public Sub test()
Debug.Print soltman(30, 100, 0)
End Sub
１つの関数でフラグ分岐で2つの計算を実現する関数がこれで完成しました。Publicにしておくとどこからでも呼び出しが可能です。Privateにするとその関数を書いたモジュール内からだけ呼び出しが可能です。
関数で尚且つ引数を取っているので、そのままでは関数の実行テストが出来ません。そこで、testという下にある「sub」というもので、プログラムを組んでいます（これは関数ではなく、Subプロシージャと呼ばれるものです。本来プログラムはSubを使います）Debug.Printにてsoltmanという自分が作った関数が呼び出され、30と100、そして0という3つの引数を渡しています。計算結果は、イミディエイトの中に表示されます（答えは食塩水の濃度は、30(%)という答えが、イミディエイトに出てきます）
図：こんな感じで関数を作り
クエリで使ってみる
さて、今回作ったこの独自の関数、プログラム内でも呼び出せますが、実はクエリでも利用が出来ます。ID, 食塩の重さ, 溶液の重さという3つの列で出来たテーブルでクエリを作り、4つ目に濃度という列を設けます。この列は今回の関数で作ったクエリ上の列になります。
- クエリ編集画面にて、3つの列をまずは追加。4つ目の列のフィールドにて右クリック⇒ビルドをクリック
- ダイアログ内に列の名前と「:」を記述
- 続けて、数式として、soltman([食塩の重さ],[溶液の重さ],0)と記述する。[]も含めてフィールド名を記述する点に注意。これで列の値を引数に指定している事になります。
- ちなみに、複数のテーブルがクエリ上にある場合には、[テーブル名].[フィールド名]で指定してあげます。
- 食塩濃度計算という名前でクエリを保存する。
- 実際にテーブルに値を入れて、このクエリを開いてみると、きちんと食塩水の濃度が計算されています。
図：こんな感じで新しい列は数式で作る
図：無事に自作関数で濃度の計算が出来た
簡単な処理を書いてみる
概要
さて、ここまででAccessのマクロ機能と自作のユーザ定義関数およびそれを使ったクエリでの計算を実現しました。この手法だけでもAccessの利用範囲は格段に広がります。コードも十数行程度ですので、トレーニングにも持ってこいです。
今度は処理を書いてみます。ここからは本格的なプログラミングとなります。「○○をするためには？」「■■を実現する方法は？」といった課題に対して今後は、ひとつひとつ引き出しを増やしていく事になります。VBAはすでに20年以上利用され、たくさんの資料やコードがネット上に公開されているので、必ずしも1から作る必要はありません。
今回はその第一歩になります。
コードを書く為に処理を整理する
今回は最もよく使うシーンがあるであろうものの１つとして「ファイル選択ダイアログでxlsxファイルを選択し、リンクテーブルを貼って、中のデータをテーブルに取り込む」というものをやってみたいと思います。この処理は以下のような塊に分けられると思います。
- ファイル選択ダイアログでxlsxファイルを選択し、ファイルのパスを取得する
- リンクテーブルを貼る（xlsx自体を読み取り専用のテーブル化するもの）
- 2.を元にテーブルに対しての追加クエリを用意。これをVBAから実行する
- 追加されたら、リンクテーブルを解除する
あの処理だけでもこれだけのコードを記述しなければならないと思うと「大変そう」って思うかもしれませんが、こういうコードは外でもちょっと改変しただけで流用が出来るので、大変なのは最初の1回だけです（こういった流用出来るコードの塊を、コードスニペットと呼びます）。こういうコードの断片をいくつも用意しておくことで、回を重ねる毎にプログラム作成速度はどんどん向上していきます。
事前準備
参照設定を追加する
今回のコードでは、Access標準の機能ではなく、Office自体が持ってる機能を利用させてもらいます。なので、コード書く前に「参照設定」というものから、利用するモジュールを選択する必要があります。これをアーリーバインディングと呼びます。
- Visual Basicを開く
- メニューより「ツール」⇒「参照設定」を開きます。
- 自分の場合はMicrosoft Office 15.0 Object Libraryがそれになります。お使いの環境によって、15.0の部分が異なるので、それっぽいものを見つけます。
- それに対してチェックを入れて、OKを押します。
- これで完了
図：このライブラリは結構よく利用します。
クエリを用意しておく
今回のコードでは、インポートデータというxlsxファイルに対して、インポートデータというシート名のシートをtestxlsxという名前でリンクテーブルを貼ります。そのデータをインポートデータというテーブルに入れるので、コードを書く前に事前に以下の手順でxlsxファイルに対してリンクテーブルを貼ってクエリを作っておきましょう。
- Accessの外部データタブにあるインポートとリンクの「Excel」をクリック
- ダイアログが出てくるので、インポートデータという名前のxlsxファイルを選択する
- リンクテーブルを作成してソースデータにリンクするを選択して、OKを押す。
- 次の画面では、先頭行をフィールド名として利用するにチェックが自動で入ってると思うので、次へをクリック
- リンクテーブル名はtestxlsxとする
- testxlsxというテーブルが出現するので、これを使って、インポートデータテーブルにデータを追加する「追加クエリ」を作る。
- 追加クエリの名前はデータ追加クエリとし、IDの列だけは入れない。
- また、インポートデータテーブルのデータを削除する削除クエリも作っておく。毎回洗い替えで取り込む為（これがないと、どんどんテーブルにデータが追加されてしまう）。名前は、インポートデータ削除とする
- これで準備が完了したので、testxlsxを右クリックして削除し、リンクテーブルを解除する。
4.の画面で複数のシートがある場合シートを選択する項目があると思うので、それを選んでおく必要があります。そのまま進むと1つ目のシートにリンクテーブルが貼られてしまいます。
図：リンクテーブル作成画面
図：testxlsxというテーブルが出来る（読み取り専用）
図：ID列以外のデータを追加する追加クエリを作る
実際にコードを書いてみる
最初は何をしているのやらサッパリなコードであっても、それぞれをじっくり紐解いて行くと、だんだん何をしているのかがわかってきます。また、それを繰り返す事で自然とそれぞれのコードを書けるようになってきます。また、一度書いて忘れても、その塊が何をしているものなのかさえ理解出来ていれば、コピペでも十分プログラムは書けます。
'ファイル選択ダイアログを表示してファイルを選択する
Public Sub selectbox()
'変数を用意する
Dim strpath As String 'ファイルのパスを格納する
Dim ret As Integer '取り込み処理からの処理結果を受け取る
'ファイルを開くダイアログ用の変数
Dim dlg As Object
'ファイル選択ダイアログの準備
Set dlg = Application.FileDialog(msoFileDialogSaveAs)
'ファイル選択ダイアログをオープンする
With Application.FileDialog(msoFileDialogFilePicker)
'複数選択はできないように設定する
.AllowMultiSelect = False
'ファイル ダイアログ ボックスのタイトル設定
.title = "Excelファイルのインポート"
'表示される初期パスまたはファイル名を設定
.InitialFileName = CreateObject("WScript.Shell").SpecialFolders.Item("Desktop") & "\"
'初期ビューを設定(バージョンによって無視される)
.InitialView = msoFileDialogViewWebView
'xlsx形式だけを選択できるようにフィルタする
With .filters
.Clear
.Add "", "*.xlsx"
End With
'Showを宣言した所でダイアログが開かれる。戻り値は以下の通り
'ファイル、フォルダが選択 → True
'キャンセルを押された → False
If .Show = True Then
strpath = .SelectedItems(1)
Else
strpath = ""
MsgBox "キャンセルされました。"
Exit Sub
End If
End With
'実際に処理を担当する関数（importxlsx）に渡して処理結果を受け取る
ret = importxlsx(strpath)
'返ってきたのでコード別にメッセージを表示
If ret = 0 Then
'エラーが返ってきたので、エラーを表示
MsgBox "エラーみたいですよ。"
Else
'無事に処理が完了したので、処理完了メッセージを表示する
MsgBox "処理は無事に完了しました。"
End If
End Sub
'ファイルのパスをもとにリンクテーブルを貼り、インポート、そして解除する
Public Function importxlsx(path As Variant) As Variant
'エラーが発生したら捕捉する
On Error GoTo error_importxlsx
'testxlsxという名前でリンクテーブルを接続する
'xlsxのインポートデータというシートをリンクする
DoCmd.TransferSpreadsheet acLink, , "testxlsx", path, True, "インポートデータ!"
'追加クエリを実行する
DoCmd.SetWarnings False '警告ダイアログを一時的にOFF
DoCmd.OpenQuery "インポートデータ削除" 'データ削除クエリを実行してテーブルデータをクリアしておく
DoCmd.OpenQuery "データ追加クエリ" 'データ取り込みの追加クエリを実行する
DoCmd.SetWarnings True '警告ダイアログを一時的にONに戻す
'リンクテーブルを解除する
DoCmd.DeleteObject acTable, "testxlsx"
'無事に完了したので成功として呼び出し元へ返す
importxlsx = 1
Exit Function
error_importxlsx:
'エラーとして呼び出し元へ返す
importxlsx = 0
Exit Function
End Function
- selectboxというsubが今回呼び出す処理。これをボタンから呼び出せるようにボタンのクリック時イベントに記述が必要です。
- 通常はマクロの作成で「プロシージャの実行」⇒関数名と入れて、ファイルの取り込みという名前を付ける。その後ボタンにこのマクロを割当てて上げればOKです。但し今回のコレは「関数」ではなくSubなので、コードを直接ボタンに書きます。
- ボタンの「クリック時」という部分で「…」をクリックし、コードビルダーを選択します。
- Visual Basicの画面では1行だけ call selectbox とだけ記述すれば、Subを呼び出せます。
- メインの処理はファイルへのパスを受け取ったimportxlsxが引き継ぎます。リンクテーブルを貼り、データの削除、データの追加、リンクテーブルの解除を担当します。
- importxlsxから返ってきた値を元に元のselectboxではメッセージを表示します。エラー時とそうでない時を0と1で判定させています。
コードの解説
ファイルの選択ダイアログとそのオプション
selectboxのはじめのほうでは、何やら呪文のようなコードが書いてありますが、これは「こういうもの」とだけ覚えておけば良いと思います。いわゆる「おまじない」というもので、定型文です。これでファイルの選択ダイアログが呼び出せてしまいます。
次にWith Application.FileDialog(msoFileDialogFilePicker)以下ですが、ここは、ファイル選択ダイアログに対するオプション設定。今回は
- 複数ファイルの選択はオフにする
- ダイアログのタイトル名を設定する
- ダイアログが開く一番最初のフォルダをデスクトップにする（コード部分もこういうものだと思ってください）
- initialviewはファイル選択ダイアログという指定にしています（他にもフォルダ選択ダイアログというものもあるので）
- xlsxファイル以外は選択できないように拡張子でフィルタをしてしまいます（選択ダイアログには、xlsx以外出てきません）
- ファイルを選択すると、strpathにそのファイルへのパスが格納されます。
- これを次の処理であるimportxlsxの引数に渡して、返ってくるのを待ちます。
- importxlsxから処理結果が返ってきたら内容を元に判定してメッセージを表示します。
リンクテーブルを貼る
これはとても簡単。ファイルのパスはselectboxから送られてくるので、以下のような1行を書くだけでリンクテーブルが設定されます。但し同じ名前のリンクテーブルがある場合には、testxlsx1といったように数字がついて別のテーブル扱いになってしまうので、必ずtestxlsxが無い状態にしないと具合が悪いです。
DoCmd.TransferSpreadsheet acLink, , "testxlsx", path, True, "インポートデータ!"
この1行で、pathに入ってる「xlsxファイルへのパス」、インポートデータというシート名（最後に!を付けるのもお約束）、testxlsxという名前のリンクテーブルにする、という処理が一気に行われます。
クエリを実行する
VBAから作り置きのクエリを実行する事が可能です。但し、DoCmd.SetWarnings Falseを実行しておかないと毎回「追加しますか？」的なメッセージが出るので、自分の場合はDoCmd.SetWarnings Falseを付けています。
クエリの実行自体は、以下のような書き方で簡単に実行されます。
DoCmd.OpenQuery "実行するクエリ名"
そして実行後には、DoCmd.SetWarnings Trueとして設定を元に戻しています。
リンクテーブルを解除する
リンクテーブルの解除もしておきましょう。こうしないと、次回以降どんどんおかしなリンクテーブルがデータベースファイル内にできてしまいます。以下の1行を書くだけです。
DoCmd.DeleteObject acTable, "testxlsx"
これでtestxlsxというリンクテーブルが削除されます。尚、この処理は、エラーが発生した場合にそなえて、次項のエラーハンドリングというところにも記述しておくと尚良いでしょう。
selectboxへ値を返す
無事に最後まで処理が完了したら、importxlsx = 1として値を返しています。但し、このコードのどこかでエラーが発生すると、selectboxは値を受け取れずに終わってしまいます。そこで、エラーハンドリングと呼ばれるコードを追加します（但しこのコードは、コードを編集中はコメントアウトしておいたほうが良いです。理由はエラーで止まってくれないため、エラー内容がわからなくなるためです）。
今回のimportxlsxのコードでは、冒頭にOn Error GoTo error_importxlsxというコードが入っています。これが、エラーハンドリングのコードです。これは、エラーが起きたら、error_importxlsxという行までジャンプしろというコードです。ジャンプ先では、importxlsx = 0という形で値を変えさせているので、エラーで止まることなく、selectbox側で値を受け取って判定が可能です。
