【ACCESS VBA】ACCESSのVBAでExcelの関数を利用する
ACCESSが苦手な人は、Excelの方が使いやすいと思っているからです。
Excelは、すでに市民権を得ていますが、ACCESSはそうでもありません。
もし、Excelが、ACCESSに限りなく近ければどうでしょうか？
目次
ACCESSのVBAでExcelの関数を利用する
こんにちは。伊川(@naonaoke)です。
前回の記事に続きまして、今回もACCESSで平均偏差を求めたいと思います。
下記が前回の記事です。
前回メインは、更新クエリを作成して、ACCESSのデータを書き換えました。
しかし、Excelに慣れている人ならば、なんか面倒と思うでしょう。
その通りです。
今回は、限りなく、Excelに近づけて説明をしたいと思います。
このブログはこんな人にお勧め
- ACCESSの勉強をしている人
- ACCESSを勉強しているが、スキル向上の実感がない人
- ExcelVBAのスキルをACCESSで活かしたいと思っている人
このブログを、読み終わるころには・・・・
参照設定を変更するだけです。
あまり、お勧めはしませんが、どうしても時間の無いときなどは、有効な技になるでしょう。
Excelの関数をACCESSで利用するためには
VBEの画面から参照設定を変更します。
Microsoft Excel 16.0 Object Libraryにチェックを入れます。
これだけです。
この情報は、NETで検索しても、あまりHITしない情報です。
しかし、ExcelとACCESSの開発者は、本当に見事です。
このようなことも想定して、Excel、ACCESSを開発したのであれば、本当にノーベル賞を受賞してもおかしくないと思います。
これだけで、ExcelのVBAで利用できる関数が、ACCESで利用できるようになります。
だからと言って、VLOOKUP関数を利用しても意味ないです。
ACCESSには、オートルックアップクエリがあります。
Excelの関数をACCESSで利用 作業手順 その1 AVEDEV関数をExcelVBAで実行
空欄の平均偏差を求めるならば、下記のコードになります。
関数でも、VBAでも同じ結果が出力されました。
Excelの関数をACCESSで利用 作業手順 作業手順 その2 ACCESSで、AVEDEV関数を実行
このようなデータベースがあります。
同じく空欄の、平均偏差を、VBAで算出します。
このコードを標準モジュールに記載します。
なんとなく、ExcelのVBAに似ていますよね。
Excelの関数をACCESSで利用 作業手順 その3 ExcelVBAとACCESSVBAの違い
1番異なるのは、ループですね。
Excel⇒ For～Next
ACCESS⇒ Do～Loop
ACCESSではDAOを利用しています。
頑張れば、For～Nextは使えそうですが、DAOとDo～Loopは相性がいいですよね。
配列に、Do～Loopを利用するよりも、For～Nextを利用したほうが便利だからです。
ここでも、ADOですね。
なんと言っても、ACCESS専用のJETエンジンです。
高速の処理ができますよね。
これで完成です。
Excelの関数をACCESSで利用 作業手順 その4 余談
平均偏差を求めるに当たり、もちろん、クエリで、平均偏差を求める数式を入力すれば、算出されます。
しかし、私の数式の組み方が悪いのか、それとも、平均偏差を求める公式が複雑なのか、ACCESSが重くなり、何が原因か、わかりませんでした。
平均偏差を求めるAVEDEV関数は、多分、ACCESSには搭載されていません。
だから、更新クエリをExcelで作成して、ACCESSのデータを更新しようと思いました。
前回も言いましたが、それは本末転倒で、私がやりたいことは、平均偏差を求めるということで、更新クエリを作成することではありません。
VBAで苦戦している人は、この辺を考えたほうがいいです。
本来、やりたいことを難しく考えていないでしょうか？
この、Microsoft Excel 16.0 Object Libraryは、2008年発行の書籍の情報です。
今から、13年前の書籍です。
この書籍の素晴らしいところは、連動のコンボボックスの内容が記載されていました。
なので、思わず、購入したのですが、6年くらい放置していました。
ACCESSをある程度、操作できるようになり、7回くらい読み返しています。
13年前の書籍ですが、現在でも利用できる情報が満載で、多分、著者の方は、きっと実務で苦労されたと思います。
著者は、きたみあきこ さんと言います。
ACCESSで書籍の購入に迷ったら、きたみあきこ さんが著者が確認してみるといいと思います。
そして、価格が、2,000円以内で、かつ、入門書であれば、まず、失敗はしません。
私が、今、持っている書籍を紹介したいのですが、もう絶版になっています。
ここがポイント
2回にわたって、平均偏差を求める事を紹介しました。今回のことから察するに、ACCESSには、Excelほどの統計関数は搭載されていないでしょう。
その時に、Microsoft Excel 16.0 Object Libraryが役に立つと思います。
もともと、そのような計算は、Excelに任せるのが1番です。
しかし、今回のように、どうしても、ACCESSで平均偏差を求めるようなシーンがあれば、
Microsoft Excel 16.0 Object Libraryを利用したほうがいいと思います。
しかし、WorksheetFunctionは、VBAのみに許された反則技なので、利用しすぎると、プログラムスキルが向上しないと思うので、注意しましょう。
まとめ
競馬ネタでしたが、Microsoft Excel 16.0 Object Libraryに関しては、今後も利用したいと思っています。
競馬に関して、いい情報があれば、すぐに、データベースに反映させたいからです。
理屈に関しては、後から、ゆっくり考えます。
この楽しみがあるから、データベースの虜になっています。
今回のサンプルファイルは、No242です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
