【VBA×SQL】メリットや使い方、環境構築の方法を画像で解説

VBAとSQLは一緒に使うことができる

SQLに関する質問をいただきました。

  • SQLとは?
  • SQLServerとは?
  • Excelと一緒に使う事はできるのか?

この3つが上位を占めている事が多いです。

質問も増えてきたので記事にする事にしました。

EnjoyExcel
EnjoyExcel

SQLは言語名です。大きく分けるとプログラミング言語です。

ExcelやAccessと合わせて使う事ができますよ。

まずは質問に答えていきます。

続いて記事の真ん中あたりで開発環境の事例を提示します。

最後にExcel×SQLで環境構築する際のメリットを紹介します。

この本で勉強しました

ExcelとSQLを一緒に使うという事例を紹介した書籍はかなり少ないです。

SQLについてこの本ではSQLiteというアプリケーションを紹介しています。

今回私が紹介するアプリケーションではないのですが考え方は本で勉強しました。

\おすすめの書籍です/

関連記事

SQLをVBAと一緒に使う事ができれば副業も可能になります。

ただし自分で副業先を見つけるのは大変です。無料でエージェントさんに依頼してみませんか

質問の回答

冒頭で挙げた3つの質問に回答していきます。

SQLとは

SQLというのは言語の名前です。

データベース言語など色々な言い方がありますが基本はプログラミング言語です。

以下引用文を参照ください。

関係データベース管理システム (RDBMS) において、データの操作や定義を行うためのデータベース言語問い合わせ言語)、ドメイン固有言語である。プログラミングにおいてデータベースへのアクセスのために、他のプログラミング言語と併用される。

Wikipedia

RDBMSなど聞きなれないキーワードもありこれから勉強する人にとっては分かりにくいかもしれません。

最初は「データベースと情報をやり取りする為の言語」という理解で十分です。

SQLServerとは

SQLServerというのはMicrosoftが用意しているアプリケーションの名称です。

このアプリケーションは情報を蓄積する為のサーバーの様に使える機能を持っています。

加えてデータベースの機能も持ち合わせたアプリケーションです。

SQL・・・という名前なので同じくくりで考えてしまうのですが別モノです。ご注意ください。

カテゴリー分けするとSQLServerはRDBMSの中の1つです。RDBMSについてはこのあと説明します。

SQLを使うには情報をストックする場所が必要

SQLSeverはSQLという言語とは別とは言ったものの少し矛盾が起きています。

SQLはデータベースと情報をやり取りする為の言語」なのでデータベースがないと仕事になりません。

やはりなんらかの箱を用意しないと情報がストックできないです。開発環境をイメージできません。

最終的にはSQLを使う為にはデータベースやデータサーバーの事も一緒に考えることになります。

そういった需要に応えるために世の中にはいくつかのアプリケーションが用意されています。

SQLの定義のところで出てきたRDBMSというのがこのアプリケーションになります。

RDBMS

リレーショナルデータベースマネジメントシステムの略です。

リレーショナルデータベース(RDB)はExcelやAccessの様に表形式の構造でデータを格納する方式です。

データは行や列の単位で作成、変更、抽出、削除が行われます。

RDBをシステムの情報の受け皿として使う為に色々な機能を持たせているのがRDBMSです。

Excelと一緒に使う事はできるのか?

可能です。

具体的に書くとVBAのコードの中にSQL文を書く事でRDBMSから情報を出し入れする事ができます。

このあと実際にどうやって環境を用意するのか説明していきます。

Excel×SQLでシステムを構築してみる

まずは結論です。

フロントエンドをExcelやAccess等のアプリケーションで準備します。

バックエンドにRDBMSを用意します。

フロントエンドとバックエンドの説明はこのあと用意した画像で確認してください。

互いのアプリケーション間をVBAとSQL文を使って情報のやり取りをするという建付けです。

よって実際はExcel×RDBMSでの運用という事になります。その中でSQLを使う事になります。

環境構築

Excel×RDBMSで環境構築する際は以下のようなイメージで進めます。

今回はExcel×RDBMS(SQLServer)で環境を用意していきます。

作業の全体像をつかむ

用意した画像でイメージをもってください。

この段階ではボヤっとしていても良いのでシステムの全体像をイメージしてください。

そうしないと作業をしていても何をやっているのか分からなくなります。

おのずと知識の定着率が悪くなります。よって作業の前には全体像を把握する事が非常に重要です。

参考でAccessの画像も用意していますが今回はExcelで説明していきます。

フロントエンドとバックエンド

フロントエンドはシステムの使用者(一般ユーザー)が見えるところ全般を指します。

ここではExcelを指します。

バックエンドとはシステムの使用者(一般ユーザー)が見えない所全般を指します。

ここではRDBMSや情報をやり取りする為の言語です。

VBAはフロントエンドにもバックエンドにも関わっているのできっちり分けることは難しいです。

使用者(一般ユーザー)から見えないという観点ではバックエンドと言って良いでしょう。

仕事の流れを整理する

どうやって情報が動いていくのか簡単にまとめてみました。

1_○の情報がほしい

まずはExcelでRDBMSから取り出したい情報をしていします。

〇の情報が欲しい等RDBMSへのリクエストを作成します。

画像ではユーザーフォームを使った説明をしていますがフォームが無くても大丈夫です。

使う人の人数や用途によってインプット側の情報をコントロールしたい時はフォームをご検討ください。

2_SQL文生成、VBAでラッピング

1のリクエストをもとにSQL言語生成します。

その後SQL文をラッピングする様にVBAを用意します。

VBAを使ってSQLサーバー側にクエリ(問合せ)を投げかけて回答を用意してもらいます。

コードはシリーズ最後の記事で用意しています。

DBへの接続にはADOを使います

具体的な方法は別途紹介しますので簡単にやっている事を紹介しておきます。

DBへの接続はActiveX Data Objectsを使っています。

ここではADOやCOMの説明は省略します。詳細知りたい方は引用からリンクに飛んでください。

ActiveX Data Objects (ADO)はデータソースアクセスを目的としたCOMオブジェクトのセットである。

Wikipedia

VBEのツールタブから参照設定を選択すると以下のようなダイアログボックスが表示されます。

Microsoft ActiveX Data Objects 2.8 Libraryにレ点を付けましょう。

参照設定で選択できるライブラリについては別記事で解説しています。

リンク先のタイトルはライブラリとは関係無いように見えますが問題ないです。

記事内のライブラリを解説しているところにリンクを飛ばしています。

これでDBへの経路を作る事が出来る様になります。

3_サーバーから○の情報を取り出す

サーバーエンジン(SQLServerExpress)がサーバー(SQLManagementStudio)から情報を生成します。

4_○の情報を取り出す

用意された情報はレコードセットというかたちでフロント側に返ってきます。

フォームやセルに情報を展開(出力)します。

取り出した情報はレコード(1行)単位です。配列条件分岐、繰り返し処理を使って情報を取り出します。

これが一連の流れになります。

アプリケーションを用意する

上記環境を実現する為に実際にアプリケーションを用意しましょう。

まずはRDBMSをどうするのか考えます。有名なものを1つ挙げてみますとやはりOracleさんです。

しかし有償ですので「ちょっと使いたい」という方には不向きです。

よって今回は無料で使えて簡単に開発環境を用意できるアプリケーションを使っていきます。

MicrosoftもRDMBSを用意しています

MicrosoftからSQLServerという名前でRDBMSが用意されています。

冒頭の質問で出てきたSQLServerというのはこのアプリケーションを指していました。

SQLServerはサーバーとしての機能とエンジン、各種機能を繋ぐための言語を用意しています。

それぞれの要素がどんな作業をしているのかを簡単にイメージできるように画像を用意しました。

  • イメージ:記事中段で仕事の流れを説明した際の画像に合わせて画像を用意
  • 名称:各要素の名前
  • 役割:役割を表示
  • リクエスト:リクエストというキーワードを使って後に続く文字を用意

リクエスト列はリクエストというキーワードを使って各要素の作業をイメージしやすいようにしています。

たとえば言語であるSQLではリクエストを「作る」という様に読みます。

SQLについては冒頭で説明しましたので以降の2つについて順番に説明していきます。

SQLServerExpress

サーバーのエンジンです。ここが外部からの情報を一旦受けてサーバーとやり取りしています。

フロント側に情報を返す作業もエンジンが担当しています。

このエンジンには色々なエディション(版)がありエディションによって機能や費用に差があります。

ひとまず2つだけ画像に情報を用意しておきました。

さらに詳しくしりたい方はMicrosoftさんの関連サイトのリンクを用意しておきます。

SQLServerManagementStudio

リレーショナルデータベースの形式を用いたサーバーです。

サーバーという物体が居るわけではなくサーバーの様に情報を蓄えることができるアプリケーションです。

ただ情報を蓄えるだけではなく管理者が情報を管理しやすい様に様々な機能が用意されています。

サーバーの画面は次回以降の記事で用意していきます。画面を見るとよりイメージできるようになります。

Excel×SQL(RDBMS)のメリット

ExcelにもセルがありRDBの形式で情報を持つことができます。

よってデータベースやサーバーの機能は擬似的にExcelで再現することも可能です。

RDBMSで情報を持つ方が大変です。環境構築やSQL文を勉強する必要がある為です。

デメリットの方が多く感じるのですがそれに勝るメリットがあります

情報を持つ箱のスケールが大きい

Excelは1,048,576行しか情報を持つことが出来ません。

今回使用するSQLServerはもっと沢山の情報を持つことができます。

加えて正規化でテーブルを分ける事で列方向に少なく情報を持つことができます。

正規化はネットで「正規化 データベース」などで検索していただくと解説が出てきます。

興味がある方は調べてみてください。

動作が軽い

SQLServerでは動作も重たくなりません。

Excelで104万行まで情報を持つと起動したりソートするのも一苦労です。

関数を打ち込むと結果が返ってくるまで一時かかります。

SQLserverはそんなことは起きません。スピーディーに情報にアクセスし情報を取得する事ができます。

信頼性が高い

RDBMSは複数人で1つの情報にアクセスしてもデータが破損しないような作りになっています

複数人でエクセルを使って作業をする事をイメージしてください。

ひとまず「共有」という機能で対応されることでしょう。

これで2~3人ぐらいで1つのデータを使うという環境であればデータ破損のリスクは低いです。

しかし数十人、数百人で1つのデータを使うとデータ破損のリスクが一気に高まります。

私はVBAでユーザーフォームを使う際はデータ更新がぶつからないような細工が出来ます。

よってデータ破損のリスクはあまり考えなくて良いのですが多くのユーザーさんはそうはいきません。

RDBMSを使っておけばデータ衝突の心配はほぼありません。利用人数が増えても心配なく利用できます。

デメリットもある

VBAでWebスクレイピングをしたり他の言語を使った事がある方は想像がつくでしょう。

環境構築がすごく大変です。覚えることが沢山あります。

挑戦した人の多くは環境構築で止めてしまう様です。

残念ですがコードを使うフェーズまで辿り着けていないという事ですね。

まとめ

SQLの疑問点について解説していきました。

後半ではExcelとRDBMSを使った開発環境の紹介をしています。

お金はかけられないけど複数人で使える様なシステムを作ってみたいという人にはハマる内容です。

該当する方はチャレンジしてみてください。

以降の記事ではSQLSever関連のアプリケーションをダウウンロード、インストールしていきます。

最終的にはExcelからRDBMSの情報を呼び出すというところまで紹介していく予定です。

タイトルとURLをコピーしました