【AccessとExcel】VBAでサブルーチンを使う方法とコツ

一連の処理をひとまとめにするサブルーチンを使いこなせば、書く内容がスマートになるし修正も入れやすいです。

しかし、どの部分をサブルーチンにすれば良いか判断がつかないことも多いはず。

今回は、コードをスマートにしたい人のためにサブルーチンを使う方法とコツを紹介します。

まずはサブルーチンについて「おさらい」

プログラムにおけるサブルーチンは、同じコードを書くのを避けるために使われます。

それだけでなく、プロシージャ名の付け方によっては後々改修する際に見つけやすいメリットがあり、コードの組み立て方としても理にかなっていると言えるでしょう。

少し例を紹介します。

Praivate Sub outputXl()

    If Dcount("*","クエリ1") = 0 Then
        MsgBox "データはありません。"
        Else
            DoCmd.TransferSpreadsheet acExport, 10, "クエリ1", "C:¥test.xlsx", True
            MsgBox "出力完了"
    End If
    
    If Dcount("*","クエリ2") = 0 Then
        MsgBox "データはありません。"
        Else
            DoCmd.TransferSpreadsheet acExport, 10, "クエリ2", "C:¥test.xlsx", True
            MsgBox "出力完了"
    End If
    
    If Dcount("*","クエリ3") = 0 Then
        MsgBox "データはありません。"
        Else
            DoCmd.TransferSpreadsheet acExport, 10, "クエリ3", "C:¥test.xlsx", True
            MsgBox "出力完了"    
    End If
    
    If Dcount("*","クエリ4") = 0 Then
        MsgBox "データはありません。"
        Else
            DoCmd.TransferSpreadsheet acExport, 10, "クエリ4", "C:¥test.xlsx", True
            MsgBox "出力完了"
    End If

    If Dcount("*","クエリ5") = 0 Then
        MsgBox "データはありません。"
        Else
            DoCmd.TransferSpreadsheet acExport, 10, "クエリ1", "C:¥test.xlsx", True
            MsgBox "出力完了"
    End If

End Sub

これをサブルーチン化すると以下のとおり

Private Sub outputXl()

    subRoutineOutputXl "クエリ1"
    subRoutineOutputXl "クエリ2"
    subRoutineOutputXl "クエリ3"
    subRoutineOutputXl "クエリ4"
    subRoutineOutputXl "クエリ5"        

End Sub

Private Sub subRoutineOutputXl(strQueryName As String)
    
    If Dcount("*",strQueryName) = 0 Then
        MsgBox "データはありません。"
        Else
            DoCmd.TransferSpreadsheet acExport, 10, strQueryName, "C:¥test.xlsx", True
            MsgBox "出力完了"
    End If
    
End Sub

単純なプログラムですが、これだけでもスッキリするしバグ対応もしやすいですよね。

これがシンプルなサブルーチンの例になります。

処理のレイヤーをイメージする

分かりやすい例で説明しましたが、実際VBAを組んでみると何をサブルーチン化すれば良いのか分からないことが多いです。

そこでイメージして欲しいのが処理のレイヤー。

処理の流れをイメージして、従軸になる部分と機能的な部分を切り離すことが重要になります。

例えば、あるテーブルのデータをExcelへ出力するケースで考えてみましょう。

出力したい元データはテーブルに格納されているので、主軸はテーブルオブジェクトもしくはレコードセットになるはずです。

一方でExcelへ書き出すのは、あくまで「機能」になるのでサブルーチン化の対象になります。

機能の部分を把握すれば、複数のテーブルからでも簡単に呼び出すことが可能です。

この考えで開発していくと、単純な親と子だけでなく更に単純な機能をサブルーチン化したプロシージャまで出来上がってきます。

これをクラスモジュールでパブリックにすれば、汎用性の高いプロシージャとして使えるでしょう。

渡す引数や戻り値の使い方にセンスを光らせる

Accessマクロ&VBA 開発工房 Office365/2019/2016/2013対応

サブルーチン化させるレイヤーと同時に考えたいのが、プロシージャに渡す引数や戻り値です。

前述の例だとクエリ名を引数として渡していますが、クエリによっては違う処理をさせたいケースもあるでしょう。

また処理が正しく実行されたか結果をFunctionで返すように作るのも、プログラマーがセンスを光らせる部分になります。

では、これらを踏まえて前述したコードを例に書いてみましょう。

Private Sub outputXl()
    
    Dim qd As DAO.QueryDef
    Dim sQueryName As String
    Dim bool As Boolean
  
        For Each qd In CurrentDB.QueryDefs
        
            sQueryName = qd.Name
            If sQueryName Like "クエリ*" Then '名前に「クエリ」が含まれていたら'
                bool = subRoutineOutputXl(sQueryName)
                Else '含まれていなければ(追加改修)'
                    bool = subRoutineOutputXl(sQueryName, "special")   
            End If
            
            Select Case bool
                Case True
                    MsgBox "出力完了"
                Case False
                    MsgBox "データはありません。"
            End Select
            
        Next qd

End Sub
    
'サブルーチンのファンクションプロシージャ'
Private Function subRoutineOutputXl(strQueryName As String, Optional strCategory As String = "normal") As Boolean
    
    Dim boo As Boolean
    Dim xlApp As Object, xlBook As Object, xlSheet As Object
    Dim qd As DAO.QueryDef, rs As DAO.Recordset
    Dim iRow, iCol
    Dim iField As Integer
    
        Select Case strCategory
        
            Case "normal"
    
                If Dcount("*",strQueryName) = 0 Then
                    boo = False '失敗'
                    Else
                        DoCmd.TransferSpreadsheet acExport, 10, strQueryName, "C:¥test.xlsx", True
                        boo = True '成功'
                End If
                
            Case "special"
            
                Set qd = CurrentDB.QueryDefs(strQueryName)
                Set rs = qd.OpenRecordset
                
                iRow = 1
                With rs
                
                    If .EOF Then
                        boo = False '失敗'
                        Else
                            Set xlApp = CreateObject("Excel.Application")
                                xlApp.Visible = True
                            Set xlBook = xlApp.Workbooks.Open("C:¥test.xlsx")
                            Set xlSheet = xlBook.Worksheets(1)
                            iField = .Fields.Count
                            Do Until .EOF
                                For iCol = 1 To iField
                                    xlSheet(iRow, iCol).Value = .Fields(iCol - 1).Value
                                Next iCol
                                .MoveNext
                                iRow = iRow + 1
                            Loop
                            boo = True '成功'
                            
                            xlBook.SaveAs "C:¥" & strQueryName & ".xlsx" '名前をつけて保存'
                            With xlApp
                                .WorkBooks(strQueryName & ".xlsx").Close
                                .Visible = False
                            End With
                            
                            Set xlSheet = Nothing
                            Set xlBook = Nothing
                            Set xlApp = Nothing                       
                    End If
                    
                    .Close    
                    
                End With
                
                Set qd = Nothing
                Set rs = Nothing
                
        End Select
        
        subRoutineOutputXl = boo '結果を返す'
    
End Function

少し長くなりましたが、処理をnormalとspecialでカテゴリ分したバージョンを書きました。

サブルーチンからの戻り値によって、メッセージボックスの内容を変更する仕組みです。

命名規則を作っておけばFor〜Eachを使ってオブジェクト名を検索して実行できるので、いちいちVBの中で名前を指定する必要がありません。

またspecialのカテゴリが追加改修されたものであれば、Optionalを積極的に使っていくのも一つの技法です。

頑張って美しいコードを目指そう！

今回紹介したのは、本当に一部分だけのコードですが僕が仕事で実際に書いているパターンになります。

重要なのはデータの流れをイメージして、根幹と枝葉の部分を把握すること。

それだけでもサブルーチン化する箇所が見えてくるし、途中で混乱してきても理にかなっていれば組み立てやすくなります。

仕事では直接的な評価をされにくい場所ではありますが、頑張って美しいコードを目指していきましょう。

DeepBlueでは、VBAに関する質問や要件定義の相談を受け付けています。

今回、この記事を読んで登録してくれた方には初回のみ無料でVBAマクロに関する相談を承りますよ。

興味ある方は、是非チェックしてみてくださいね。