ご飯

すっきりしたコードを書きたい

RangeやCellsを使わずにセルのデータを取り出す

Range や Cells を使わずに Sheet1 から idごはん という列のデータを取り出し、コンボボックスに要素を追加するというプログラムを ADODB を使って作ってみます。

 

f:id:uhoo:20181031132909p:plain

Sheet1

Module1 

Option Explicit

Dim con As New UConnection

Sub Uhoo(種類$)

    Dim q As New UQuery, i%

    q.SetCon con
    q.Sql.Add "SELECT id, ごはん FROM [Sheet1$]"
    q.Sql.Add "WHERE 種類 = ?"
    q.AddParam "種類", 種類$
    q.OpenRecordset

    i = 0
    ComboBox1.clear

    Do Until q.hasNext
        ComboBox1.AddItem
        ComboBox1.List(i, 1) = q.Fields("id")
        ComboBox1.List(i, 0) = q.Fields("ごはん")
        i = i + 1
        q.MoveNext
    Loop

    q.CloseRecordset

End Sub 

たったこれだけなのです。それに、ADODB のあのごちゃごちゃした記述が一切見当たりません。その理由は UConnection, UQuery にあります(後述)。

解説

  1. 標準モジュールの先頭で UConnection のインスタンスを作成
  2. UQuery のインスタンスを作成し q 変数に代入
  3. Sql.Add メソッドでSQL文をセット
  4. AddParam メソッドでパラメータを設定
  5. OpenRecordset メソッドでクエリをオープン
    • フィールドの内容を参照するには、q.Fields("ごはん") などとします
    • MoveNext メソッドで次のレコードに移動します
    • レコードが1件もなくなれば hasNext メソッドが False を返しループ文を抜けます
  6. CloseRecordset メソッドでクエリを閉じます

 

留意事項

  • Sheet1 にはテーブルを挿入しておいてください
  • 参照設定をお忘れなく Microsoft Visual Basic での ADO の使用
  • FROM句のあとのテーブル名は最後に$マークをつけてカッコで囲ってください
  • SQL文の中の ? の数と、AddParam の回数は一致させてください

 

準備

次の3つのクラスモジュールをブックに追加するだけです。

  1. UConnection
  2. UQuery
  3. UStringList

 

UConnection

UConnection は ADODB.Connection オブジェクトのぐじゃぐじゃした記述を隠すためのクラスです。
くじゃくじゃした記述とは....

  • ADODBプロバイダに接続するときのパラメータや💢💢
  • いちいちコネクションを切断したり Nothing を代入したり

UConnection は、こんなことをしてくれます。

  • インスタンスを作るだけで接続
  • プロバイダのパラメータを隠してくれる
  • 要らなくなればコネクションを切断 Nothing も代入
Option Explicit

Private con As ADODB.Connection Public Sub OpenConnection() Set con = New ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0;" .Properties("Extended Properties") = "Excel 12.0 xml;" .Open ThisWorkbook.FullName End With End Sub Public Sub CloseConnection() If con.State <> adStateClosed Then con.Close End If Set con = Nothing End Sub Public Function GetCon() As ADODB.Connection Set GetCon = con End Function Private Sub Class_Initialize() OpenConnection End Sub Private Sub Class_Terminate() CloseConnection End Sub

 

UQuery

UQuery は、ADODB.Recordset オブジェクトのぐじゃぐじゃした記述を隠すためのクラスです。
ぐじゃぐじゃした記述とは....

  • SQL文を組み立てるために長々と文字列式をつなげて書くこと
  • ADODB.Recordset.Fields("カラム名") でなぜか値を取り出せない💢💢💢 *1
  • パラメータが多いと CreateParameter のくだりだけでコードが肥大化

UQuery は、こんなことをしてくれます。

  • SQL文を貯めこんでおいてくれる (Sql.Addメソッド)
  • パラメータも貯めこんでおいてくれる (AddParamメソッド)
  • カラム名で値を取り出せる (Fields メソッド)
Option Explicit

Private con As UConnection, rs As New ADODB.Recordset, cmd As New ADODB.Command
Public Sql As New UStringList

Public Sub SetCon(con_ As UConnection)

    Set con = con_

End Sub

Public Sub AddParam(key$, val$)

    Dim prm As ADODB.Parameter
    Set prm = cmd.CreateParameter(key, adVarChar, adParamInput, 100, val)
    cmd.Parameters.Append prm

End Sub

Public Sub OpenRecordset()

    Set cmd.ActiveConnection = con.GetCon

    cmd.CommandText = Sql.GetStr()

    Set rs = cmd.execute

End Sub

Public Sub MoveNext()

    rs.MoveNext

End Sub

Public Function hasNext() As Boolean

    hasNext = rs.EOF

End Function

Public Function Fields(name$) As String

    Dim i%

    For i = 0 To rs.Fields.Count - 1

        If name = rs.Fields(i).name Then
            Fields = IIf(IsNull(rs.Fields(i).Value), "", rs.Fields(i).Value)
            Exit For
        End If

    Next i

End Function

Public Sub CloseRecordset()

    If rs.State <> adStateClosed Then
        rs.Close
    End If

    Set rs = Nothing

End Sub

Public Sub execute()

    OpenRecordset

End Sub

Private Sub Class_Terminate()

    CloseRecordset

End Sub

 

UStringList

UStringList は文字列の配列を格納するクラスです。SQL文を組み立てるときに使います。 GetStrは、配列の要素をつなげて文字列として返してくれます。

Option Explicit

Private str$(), idx%

Public Sub Add(s$)

    idx = idx + 1
    ReDim Preserve str(idx)
    str(idx - 1) = s

End Sub

Public Function GetStr() As String

    Dim i%, s

    For i = 0 To idx - 1
        s = s & IIf(i > 0, " ", "") & str(i)
    Next

    GetStr = s

End Function

Public Sub clear()

    idx = 0

    Erase str()

End Sub
Private Sub Class_Initialize() clear End Sub

 

たったこれだけの工夫で、あのごちゃごちゃした記述を見なくて済みます。ご飯はわがままなのです。

 

*1:プロバイダ Excel 以外はOKでした