「ADO + VBA」でSQLを実行するときのVBAサンプル

公開日：2025-10-28　最終更新日：2025-11-03

「ADO + VBA」でSQLを実行するときのVBAサンプル

ExcelからVBA（ADO）を使ってRDBMS（またはデータベース）にSQLコマンドを送って結果を受け取る為のVBAサンプルです。
SQL問題の作成に当たって、より簡単にSQLを実行するための方法として、「ユーザー定義関数」を作成しました。

ADOとユーザー定義関数について

ADO(ActiveX Data Objects)の使い方の要点｜VBA技術解説

・データベースの種類・SQL（SQL:Structured Query Language）・ADOを使う準備・ADOでのDB接続方法・ADODB.Recordsetの取得方法・ADODBのレコードセットの扱い方・ADODBのトランザクション処理・ADODB.Commandの使い方・VBA100本ノックでの実践例・最後に注意点等

VBAでのSQLの基礎（SQL:Structured Query Language）｜VBA技術解説

・SQL文・SELECT文・SQLの学習について・実践例

ユーザー定義関数の作り方｜VBA解説

・簡単な例でユーザー定義関数を作ってみましょう・この関数の使い方・ユーザー定義関数の実践使用例

ADO + Excel


  
    
      Function QUERY(ByVal sSql As String, Optional ByVal NotFound As String,
      Optional ByVal Header As Boolean)

　　On Error Resume Next

　　Application.Volatile

　　With CreateObject("ADODB.Connection")

　　　　.Provider = "Microsoft.ACE.OLEDB.12.0"

　　　　.Properties("Extended Properties") = "Excel 12.0;HDR=YES"

　　　　.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name

      　　　　QUERY = TransposeArray(.Execute(CleanSql(sSql)), Header)

　　　　.Close

　　End With

　　Select Case Err.Number

　　　　Case 0: Exit Function

　　　　Case 3021: QUERY = IIf(IsMissing(NotFound), Err.Description, NotFound)

　　　　Case Else: QUERY = Err.Description

　　End Select

End Function


Private Function TransposeArray(ByVal rs As ADODB.Recordset, ByVal Header As Boolean) As Variant

　　Dim vIn As Variant: vIn = rs.GetRows

　　Dim iHead As Long: iHead = Abs(Header)

　　If Not IsArray(vIn) Then: TransposeArray = vIn: Exit Function

　　Dim r, c, rMax, cMax

　　rMax = UBound(vIn, 1) - LBound(vIn, 1) + 1

　　cMax = UBound(vIn, 2) - LBound(vIn, 2) + 1

　　ReDim vOut(1 To cMax + iHead, 1 To rMax)

　　If iHead > 0 Then

　　　　For r = 1 To rs.Fields.Count

　　　　　　vOut(1, r) = rs.Fields(r - 1).Name

　　　　Next

　　End If

　　For r = 1 To UBound(vOut, 2)

　　　　For c = 1 To UBound(vOut, 1) - iHead

　　　　　　If IsNull(vIn(r - 1, c - 1)) Then

　　　　　　　　vOut(c + iHead, r) = ""

　　　　　　Else

　　　　　　　　vOut(c + iHead, r) = vIn(r - 1, c - 1)

　　　　　　End If

　　　　Next

　　Next

　　TransposeArray = vOut

      End Function


Function CleanSql(ByVal sSql As String) As String

　　If InStr(sSql, "--") = 0 Then CleanSql = sSql: Exit Function

　　Dim ary As Variant, i As Long, j As Long

　　ary = Split(sSql, vbLf)

　　For i = LBound(ary) To UBound(ary)

　　　　j = InStr(ary(i), "--")

　　　　If j > 0 Then ary(i) = Left(ary(i), j - 1)

　　Next

　　CleanSql = Join(ary, vbLf)

End Function

【概要】

この QUERY 関数は、ExcelのシートをデータベースのようにSQLで検索する関数です。
ADOでブックを開き、SELECT結果を整形して返します。
=QUERY("SELECT * FROM [Sheet1$] WHERE 数量>100")
このように使います。
テーブルとして扱う範囲の指定は、
[シート名$セル範囲アドレス]

引数

引数名	型	説明
sSql	String	必須。実行したい SQLクエリ文字列。例: "SELECT A, B FROM [Sheet1$] WHERE C > 10"
NotFound	Optional String	オプション。レコードが見つからなかった（エラーコード 3021）場合の代替値。指定がなければエラーメッセージが出力されます。
Header	Optional Boolean	オプション。結果にフィールド名（ヘッダー行）を含めるかどうか（Trueで含める）。省略時は False。

Application.Volatile

このステートメントは、そのユーザー定義関数を揮発性 (Volatile) 関数にします。
揮発性 (Volatile) とは？

非揮発性の関数（通常）: セルの値が変更されたなど、関数が参照しているセルや関連する計算に変更があったときにのみ再計算されます。ほとんどの組み込み関数（例: SUM, VLOOKUP）は非揮発性です。
揮発性の関数（Application.Volatile を使用）: 関数が参照しているセルに関係なく、Excelのワークシートが再計算されるたびに（またはワークシート上のデータが変更されるたびに）強制的に再計算されます。組み込み関数では、RAND(), NOW(), TODAY() などが揮発性関数です。

【動きの流れ】

ADO接続を作成
　Microsoft.ACE.OLEDB.12.0 を使って、現在のブックをデータベースとして開く。
SQL文を実行
　.Execute(sSql) で結果を取得し、配列として受け取る。
行列を入れ替え（TransposeArray）
　ADOの結果は「列×行」順なので、Excelに出すため「行×列」に変換。
エラー処理
　通常：結果を返す
　データなし（3021）：NotFound で指定値、またはエラー文
　その他：エラー内容を返す

【TransposeArray の役割】

TransposeArrayは、WorksheetFunction.Transpose の制限（約65,000件まで）を回避し、さらに Null を空文字に置き換えるための自作関数です。

VBAでシート関数使用時の配列要素数制限｜VBA技術解説

・FILTER関数・SORT関数、SORTBY関数・UNIQUE関数・XLOOKUP関数、HLOOKUP関数、VLOOKUP関数・XMATCH関数、MATCH関数・TRANSPOSE関数・VBAでシート関数使用時の配列要素数制限まとめ

ADOの .GetRows は

「列×行」順の配列を返します。
Null をそのまま含む

そのため、そのままではシートに展開できのません。
TransposeArrayは、これを

「行×列」順に入れ替え
Null → "" に変換

することで、Excel関数で安全に扱える形に整えます。

【CleanSql の役割】

ADO+VBAでは、改行処理の問題でコメントを入れるとエラーになってしまいます。
そこで、SQL文の末尾にある--コメントを除去します。

引数 sSql として、コメントが含まれている可能性のあるSQL文字列を受け取ります。
SQL文字列全体に--が含まれていないかを確認します。含まれていなければ、そのまま元のSQLを返して終了します。
SQL文字列を改行コード（vbLf）で分割し、行の配列（ary）を作成します。
各行に対してループ処理を行い、-- が含まれているかをチェックします。
-- が見つかった場合、その位置（j）から左側（手前）の部分のみを新しい行の内容として採用します。
コメントを除去したすべての行を、再び改行コード（vbLf）で結合し、クリーンになったSQL文字列を関数（CleanSql）の戻り値として返します。

※「/* … */」のコメントは、改行が影響しないので、この関数なしでも使えます。

【ポイント】

ExcelのSQL実行機能（SELECT専用）
A1セルからの表を「テーブル」として扱える
結果はそのままワークシートに展開できる

ADO + SQLite3

SQLiteの接続には、VBAのクラスを使用します。
使用するExcelのVBEで以下のクラスモジュールを追加してください。

VBAクラスの全コード：トランザクション処理

SQL入門の「トランザクション処理」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コードクラスモジュール：clsSQLite SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説。


  
    
      Function QUERY3(ByVal sSql As String, Optional ByVal NotFound As String, Optional ByVal Header As Boolean)

　　On Error Resume Next

　　Dim clsDB As New clsSQLite

　　clsDB.DataBase = "C:\SQLite3\sample.db"

　　

　　Dim rs As New ADODB.Recordset

      　　If clsDB.ExecuteRecordset(CleanSql(sSql), rs) Then

　　　　QUERY3 = TransposeArray(rs, Header)

　　　　If Err Then

　　　　　　QUERY3 = IIf(IsMissing(NotFound), clsDB.ErrMsg, NotFound)

　　　　End If

　　Else

　　　　QUERY3 = clsDB.ErrNum & ":" & clsDB.ErrMsg

　　End If

　　

　　Set clsDB = Nothing

End Function


Private Function TransposeArray(ByVal rs As ADODB.Recordset, ByVal Header As Boolean) As Variant

　　Dim vIn As Variant: vIn = rs.GetRows

　　Dim iHead As Long: iHead = Abs(Header)

　　If Not IsArray(vIn) Then: TransposeArray = vIn: Exit Function

　　Dim r, c, rMax, cMax

　　rMax = UBound(vIn, 1) - LBound(vIn, 1) + 1

　　cMax = UBound(vIn, 2) - LBound(vIn, 2) + 1

　　ReDim vOut(1 To cMax + iHead, 1 To rMax)

　　If iHead > 0 Then

　　　　For r = 1 To rs.Fields.Count

　　　　　　vOut(1, r) = rs.Fields(r - 1).Name

　　　　Next

　　End If

　　For r = 1 To UBound(vOut, 2)

　　　　For c = 1 To UBound(vOut, 1) - iHead

　　　　　　If IsNull(vIn(r - 1, c - 1)) Then

　　　　　　　　vOut(c + iHead, r) = ""

　　　　　　Else

　　　　　　　　vOut(c + iHead, r) = vIn(r - 1, c - 1)

　　　　　　End If

　　　　Next

　　Next

　　TransposeArray = vOut

End Function


Sub SelectSQLite3()

　　Dim clsDB As New clsSQLite

　　clsDB.DataBase = "C:\SQLite3\sample.db"

　　

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　

　　Dim sSql As String

　　sSql = ""

　　sSql = sSql & " SELECT"

'　　sSql = sSql & " SUBSTR(ID, 1, LENGTH(ID) - LENGTH(SUBSTR(ID, INSTR(SUBSTR(ID, INSTR(ID, '-') + 1), '-') + INSTR(ID, '-')))) AS prefix,"

　　sSql = sSql & " SUBSTR(ID, 1, INSTR(SUBSTR(ID, INSTR(ID, '-') + 1), '-') + INSTR(ID, '-') - 1) AS prefix,"

　　sSql = sSql & " COUNT(*) AS Count"

　　sSql = sSql & " FROM TBL1"

　　sSql = sSql & " GROUP BY prefix"

　　sSql = sSql & " ORDER BY prefix;"

　

　　If Not clsDB.SheetFromRecordset(sSql, ws.Range("A2"), enmClear.Clear) Then

　　　　MsgBox clsDB.ErrMsg

　　　　Exit Sub

　　End If

　

　　Set clsDB = Nothing

      End Sub


Function CleanSql(ByVal sSql As String) As String

　　If InStr(sSql, "--") = 0 Then CleanSql = sSql: Exit Function

　　Dim ary As Variant, i As Long, j As Long

　　ary = Split(sSql, vbLf)

　　For i = LBound(ary) To UBound(ary)

　　　　j = InStr(ary(i), "--")

　　　　If j > 0 Then ary(i) = Left(ary(i), j - 1)

　　Next

　　CleanSql = Join(ary, vbLf)

End Function

全体の概要

QUERY3 は、SQLite データベースに対して SQL を実行し、結果を Excel シート上で使える形に返す関数です。

データ取得には clsSQLite クラス（外部クラス）を使用
結果は 2次元配列として返す
エラー時は内容を文字列で返す

引数

引数名	型	説明
sSql	String	必須。実行したい SQLクエリ文字列。例: "SELECT A, B FROM [Sheet1$] WHERE C > 10"
NotFound	Optional String	オプション。レコードが見つからなかった（エラーコード 3021）場合の代替値。指定がなければエラーメッセージが出力されます。
Header	Optional Boolean	オプション。結果にフィールド名（ヘッダー行）を含めるかどうか（Trueで含める）。省略時は False。

処理の流れ

① エラーを無視して続行

On Error Resume Next
→ 途中でエラーが出ても止まらず、あとで Err をチェック。

② SQLiteクラスの準備

Dim clsDB As New clsSQLite
clsDB.DataBase = "C:\SQLite3\sample.db"
clsSQLite は、SQLiteと通信する自作クラス（別途定義が必要）
.DataBase に、接続するDBファイルを指定

③ SQL実行結果を格納するRecordsetを用意

Dim rs As New ADODB.Recordset
→ SQL結果を表形式で受け取るためのオブジェクト。

④ SQLを実行して結果を取得

If clsDB.ExecuteRecordset(sSql, rs) Then
clsDB.ExecuteRecordset は SQL文を実行し、結果を rs に格納
戻り値が True の場合＝成功

⑤ 結果を配列に変換

QUERY3 = TransposeArray(rs.GetRows)
rs.GetRows：結果を2次元配列に変換（列×行の順）
TransposeArray：これを行×列に直す（Excelで扱いやすくする）

⑥ エラー時の処理

If Err Then
　　QUERY3 = IIf(IsMissing(NotFound), clsDB.ErrMsg, NotFound)
End If
Err が残っていたらエラー発生
NotFound が指定されていればその値を返す
なければ clsDB.ErrMsg（クラスのエラーメッセージ）を返す

⑦ 実行失敗時

Else
　　QUERY3 = clsDB.ErrNum & ":" & clsDB.ErrMsg
End If
→ SQL実行に失敗した場合は、エラー番号と内容を返す。

⑧ クリーンアップ

Set clsDB = Nothing
→ オブジェクトを解放して終了。

「TransposeArray」「CleanSql」の役割

「ADO + Excel」と同一です。

同じテーマ「SQL入門」の記事

WITH句（共通テーブル式）
取得行数を限定するLIMIT句
 分析関数（OVER句,WINDOW句）
「ADO + VBA」でSQLを実行するときのVBAサンプル
SQL基礎問題1：最大在庫数を持つ製品の在庫金額
 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出
 SQL基礎問題3：文字列の一部をキーにして集計
 SQL基礎問題4：2つのテーブルの不一致を抽出
 SQL基礎問題5：複数のマスタテーブルの結合
 SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出
 SQL基礎問題7：成績表から各教科の最高点と最低点を抽出

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
SQL入門
「ADO + VBA」でSQLを実行するときのVBAサンプル

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
「ADO + VBA」でSQLを実行するときのVBAサンプル