最近覚えたExcel VBA の内容とサンプル

Excel VBAを使った業務アプリケーション開発中に覚えた内容をメモします。
備忘録として残します。

キーに割り当てたマクロを実行する

MacroOptions メソッド又はOnkey メソッドを使う事で実現できます。

• Application.MacroOptions メソッド (Excel)
• Application.OnKey メソッド (Excel)

下記ブログ記事において丁寧に説明されています。

• Excel VBA - Application#OnKeyメソッドの調査 - System Engineerの虚言@iSon
• マクロにショートカットキーを設定する - Moug

サンプル

[Ctrl]キーと組み合わせる場合

Application.MacroOptions Macro:="<プロシージャ―名>", ShortcutKey:="<小文字>"

[Ctrl]+[Shift]キーと組み合わせる場合

Application.MacroOptions Macro:="<プロシージャ―名>", ShortcutKey:="<大文字>"

Onkey イベントを使う場合

Application.OnKey "{キー コード}", "<プロシージャ―名>"

標準モジュールを外部で管理、起動時にインポートする

他Workbookにおいて共通化された標準モジュールを利用したい場合は、外部化します。
起動時にインポート（ThisWorkbook.VBProject.VBComponents.Import)、終了前に解放（ThisWorkbook.VBProject.VBComponents.Remove)する事で、常に最新の標準モジュールが利用出来ます。

• Excel VBAのマクロを，複数のブックから利用する方法 （標準モジュールをブックの外部で管理して，共通ライブラリとして読み込み） - 主に言語とシステム開発に関して

尚、標準モジュールをインポートする関数と同じイベント、又は関数内で標準モジュールに記述した何らかの関数を呼び出すとエラーになります。

その場合は実行用関数を別途用意すると回避出来ます。
同処理は、遅延処理（Application.OnTime メソッド）を利用する事で標準モジュールの関数を呼び出す事が出来ました。

Private Sub Workbook_Open()
    
    '外部保存した共通ライブラリをロード
    Dim intRet As Integer
    Dim strConfigFile As String
    
    intRet = fncCallExternalModule("設定ファイル パス")
    
    If intRet = -1 Then
        MsgBox "外部モジュールの読込に失敗しました。", vbOKOnly + vbCritical, "エラー"
        Application.EnableEvents = False
        Exit Sub
    End If
    
    '標準モジュールをインポート後に実行
    Call fncWbOpenExecFunction
    Application.OnTime Now + TimeSerial(0, 0, <秒数>), "<標準モジュール内の関数>"
    
End Sub

'##########################################################
'
'　　共通モジュール ロード後に実行する関数
'
'##########################################################
Private Sub fncWbOpenExecFunction()

    On Error GoTo EXEC_ERROR

        '初期化
        Call fncInitialize
        
        Exit Sub

EXEC_ERROR:

    MsgBox "起動処理に失敗しました" & vbCrLf & "エラー：" & Err.description, _
            vbOKOnly + vbCritical, "エラー"
    
End Sub

2015.04.08 加筆
実際に使い続けてみた限り、終了時のモジュール削除処理が非常に大切だと思っています。
終了時に全てのモジュールを削除できない状態で次回起動すると、

• Module1
• Xxxxxx1
• モジュールが重複する

のように現象が発生します。
特に、クラス モジュールの末尾に数字が付くとエラーになった＝致命的なため、モジュールのインポート・削除昨日を導入する際は（他開発同様)しっかりテストしたいですね。

VBAからPDFを結合する

VBAでは複数のPDFファイルを結合する関数等がないため、CLIに対応している外部プログラムを利用し結合しています。

現在はPDFtk Serverを使用中です（以前はConcatPDFでした)

尚、PDFtk Serverのライセンス形態はGPL Licenseです。
※人力検索はてなで質問されている方がいらっしゃいました。確かに不安になりますよね・・・

サンプル

PDFtk.exeはWScript.ShellをRun メソッドから実行します。
※外部プログラムを実行する時によく使います。
スクリプトで注意したい箇所は、PDFtk.exeのパスを囲むダブルコーテーションです。
今回はExecPathの前後にChr(34)を挟み、対応しています。

Public Sub fncMergeFile(ByVal FolderPath As String, ByVal FileName As String, ByVal OutputFolderPath As String)

    Dim WSH As Object, cmd As String, ret
    
    Const ExecPath = "C:\Program Files (x86)\PDFtk Server\bin\pdftk.exe"

    Set WSH = CreateObject("WScript.Shell")

    cmd = Chr(34) & ExecPath & Chr(34) & _
            FolderPath & "\*.pdf " & _
            "cat " & _
            "output " & OutputFolderPath & "\" & FileName & ".pdf"

    ret = WSH.Run(cmd, 0, True)

    Set WSH = Nothing

    If ret <> 0 Then
        GoTo FSO_ERR
    End If

    Exit Sub

FSO_ERR:

    fncMsgbox (Err.description)

End Sub

一時フォルダーを作成、削除する

前述のPDFtkを使ったPDFファイルの結合処理の流れは下記のようにしています。

1. %UserProfile%\AppData\Local配下に一時フォルダーを作成
2. 1のフォルダーにPDFファイルを複数作成
3. PDFtkを使って1のフォルダー配下にあるPDFを結合する
4. 1のフォルダーを削除する

#1と#4の操作は、FileSystemObjectを使います。

サンプル

サンプルは、いつもお世話になっているOffice Tanakaさんの記事を参考に致しました。

• テンポラリフォルダに作業用ブックを保存する
• FileSystemObjectオブジェクト - DeleteFolderメソッド

'PDF用一時フォルダー 作成関数
' - PDFファイル群は%USERPROFILE%\AppData\Local配下に作成される
' - フォルダ名はRadXXXX
Public Function fncCreateTmpFolder()
    Dim FSO As Object, TempName As String
    
    On Error GoTo FSO_ERR
    
            Set FSO = CreateObject("Scripting.FileSystemObject")
            
            With FSO
                TempName = .GetSpecialFolder(2) & "\" & .GetBaseName(.GetTempName)
                FSO.CreateFolder (TempName)
            End With
            
            Set FSO = Nothing
            
            fncCreateTmpFolder = TempName
            
            Exit Function

FSO_ERR:

    Debug.Print Err.Description
    fncCreateTmpFolder = "-1"
    
End Function

'PDF用一時フォルダー 削除関数
Public Sub fncDeleteTmpFolder(ByVal FolderName As String)
    Dim FSO As Object
    
    On Error GoTo FSO_ERR
    
        Set FSO = CreateObject("Scripting.FileSystemObject")
        
        FSO.DeleteFolder (FolderName)
        
        Set FSO = Nothing
        
        Exit Sub

FSO_ERR:

    Debug.Print Err.Description

End Sub

プロシージャの処理時間を計測する

処理の前後にTimer 関数を実行し処理時間を計算します。
Timer 関数の戻り値の型はSingleになります。

サンプル

Private Sub Test()
    Dim t1, t2 As Single
    Dim i As Long

    t1 = Timer()

    '何らかの処理
    For i = 0 To 10000
        Debug.Print i * 1000
    Next i
    
    t2 = Timer()

    MsgBox "処理時間：" & Format(t2 - t1, "0.000") & "秒"
    
End Sub

VBA高速化

知らないと雲泥の差が出る、VBAを高速化するテクニック。
参考にした各サイトで述べられているベストプラクティスは確実に高速化できるので是非覚えておきたい内容です。
セルの書き込みは極力減らす、描写・イベント・再計算は一旦停止するだけで効果が得られるのでオススメします。

• VBA高速化テクニック - Office Tanaka
• マクロVBAの高速化・速度対策の具体的手順と検証 - エクセルの神髄
• 【excel VBA】スピードアップの簡単な7つの手法！ - みるくP ブログ
• （ⅴ）ＶＢＡ・マクロを簡単に高速化する方法その１ - パソコンお助け ワン！

ワークブックを保存後、変更されているかを確認する

Workbook.Saved プロパティは「ワークブックを保存後、変更されているかを確認」する事ができます。

If Not ThisWorkbook.Saved Then
        ThisWorkbook.Save
End If

自分自身のワークブックのみ終了する

'終了前の処理
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '自分自身のみ終了
    Dim wb As Workbook
    Dim flag As Boolean
    
    flag = True

    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            flag = False
        End If
    Next wb
    
    If flag Then
        With Application
            .DisplayAlerts = False
            .Quit
        End With
    End If
End Sub

InputBox関数とInputBoxメソッドの違い

InputBox 関数の戻り値をチェックするスクリプトを都度書いていましたが、Application.InputBox メソッドを使うと不要になります。
また、Cancelボタンの判定が出来るなど、場合によってはInputBox メソッドを採用した方が良いケースがありました。
InputBox 関数とInputBox メソッド、是非覚えておきたい内容です。

• 実は奥が深いInputBox - Office Tanaka
• InputBox関数でキャンセルされたときの処理－StrPtr関数 - インストラクターのネタ帳