えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU
トップ > Excel > Excelブック単体でExcelVBAを疑似マルチスレッド化してみる

Excelブック単体でExcelVBAを疑似マルチスレッド化してみる

Excel VBA VBA実験

f:id:Kotori-ChunChun:20200224214828p:plain

今回はExcelブック単体でVBAを疑似マルチスレッド化できるか実験を行い、見事成功したので紹介します。

きっかけ

最近（私の中で）話題な「Excel Piano」開発者のA氏がこんなツイートをしていました。


彼の言うとおり VBScript を使うことで、並列処理をすることは可能ですが、いろいろと問題があります。

  • VBSで実現できる並列処理は、疑似マルチスレッド（実際にはマルチプロセス）である。

  • VBSはExcelのVBEの中でデバッグができない。

  • Win32APIが使えないので処理をExcelVBAに戻さなければならない。

  • その他諸々のVBSの制限。

いつもなら「そうだねぇ」で終わりなんですが・・・


  • VBSでスレッドが分離するなら、VBSから別プロセスのExcel起動すれば良くね？

  • 同じブックを複数開くことになるなら、Readonlyにしたほうが良いな。

  • Readonlyにするなら、大本のxlsmを開いても問題ないな。これでコードが1つのブックに収まって良いな！

  • VBAから別プロセスを起動して、非同期に出来るとVBSいらなくなるんやけどな・・・

  • それなら Application.OnTime 使えばええやん。


そんなこんなで、今日は月に1回あるかないかの天才的なひらめきをしたので、早速かたちにしてみることにしました。


コード

Option Explicit

'親部分
Sub Main()
    Const MAX_PROCESS = 10

    Dim Apps As Collection:    Set Apps = New Collection
    Dim i As Long
    
    '下準備
    Dim App As Excel.Application
    Dim Wb As Workbook
    
    For i = 1 To MAX_PROCESS
        '別インスタンスのExcelを起動
        Set App = New Application
        Apps.Add App
        
        '自分自身を別のインスタンスでも読み取り専用で開かせる
        Set Wb = App.Workbooks.Open(ThisWorkbook.FullName, _
                                    UpdateLinks:=False, _
                                    ReadOnly:=True)
        
        '子プロセスに司令を出す。
            '※この時呼ばれるプロシージャにはOnTimeのみを
            '記述し直ちに応答を返さなければならない。
        App.Run "'" & Wb.Name & "'!ExecSubMacro", i
        
        DoEvents
    Next
    
    Set App = Nothing
    Set Wb = Nothing
    
    '子プロセスの終了待ち : とりあえずWorkbookの数で判断する。
    For i = 1 To Apps.Count
ContinueFor:
        If Apps(i).Workbooks.Count > 0 Then
            Application.Wait [Now() + "00:00:00.2"]
            DoEvents
            'Debug.Print "Not Closed : "; i
            GoTo ContinueFor
        End If
    Next
    
    '子Excelのインスタンスの破棄
    'これをサボるとEXCEL.EXEがゾンビ化するかもしれない。
    On Error Resume Next
    For i = 1 To Apps.Count
        Apps(1).Quit
        Apps.Remove 1
    Next
    On Error GoTo 0
    
    MsgBox "完了！"
End Sub

'SubMacroを別のスレッドで実行させる。
'本プロシージャは呼び出し元に直ちに制御を返さなければならない。
Sub ExecSubMacro(n As Long)
    'OnTimeはThisWorkbookプロセスのスレッドでの呼び出しになる。
    Application.OnTime [Now() + "00:00:00.2"], "'SubMacro """ & n & """'"
End Sub

'時間のかかる処理
Sub SubMacro(n As Long)
    
    '適当に重い処理をする。：WaitはCPUをバカ食いするので採用
    '本当に実行されたことを確認するため、同フォルダにテキストファイルを出力する。
    Dim fso As FileSystemObject: Set fso = New FileSystemObject
    Dim ts As TextStream: Set ts = fso.CreateTextFile(ThisWorkbook.Path & "\" & n & ".txt")
    Dim i As Long
    For i = 1 To 10
        ts.WriteLine Format(Now(), "yyyy/mm/dd hh:mm:ss") & " " & i
        Application.Wait [Now() + "00:00:01"]
    Next
    ts.Close
    Set ts = Nothing
    
    'ブックを閉じることが処理終了の合図とする。
    Application.DisplayAlerts = False
    ThisWorkbook.Close False
    
    '親VBAが捕まえているため、このQuitは無視される気配
    Application.Quit
End Sub


結果

全てのコア（スレッド）の使用率が軒並み上昇しているので、負荷の分散には成功していると思われます。

ちゃんとした処理を書けばマルチコアCPUを使っているPCでは大幅に高速化できるようになるハズです。

f:id:Kotori-ChunChun:20190327005110p:plain

f:id:Kotori-ChunChun:20190327005105p:plain


ちょっとだけ解説

今回のキモはApplication.OnTimeです。

Application.OnTimeは「指定した時刻になったら任意のプロシージャを実行する」という機能です。

「ExecSubMacro」プロシージャはメインスレッドからApplication.Runで呼び出しているので、処理が終わるまでメインスレッド（親プロセス）の実行が止まりますが、OnTimeを実行してすぐに制御を返しているため殆どロスがありません。

対して「SubMacro」はOnTimeの予約から実行されるため、子プロセスのExcelが自分で考えてVBAが動き出します。つまり、ここで親Excelと子Excelが非同期となるのです。

あとはExcelをドンドン起動させて行くだけで、マルチコア対応VBAの完成です。

（マルチスレッド化の避けられない宿命ですが、高負荷処理を分割する方法と、実行結果を統合する処理を書くのが大変かもしれませんが）


メインスレッド上でロスの大きいNew Applicationを行わなければならないので、分割数を多くするほど下準備に時間がかかります。

マルチスレッドをしたい時は、膨大な時間のかかる処理のはずなので、無視出来る程度の時間だと思いますが、一瞬で終わる処理を分割すると逆に遅くなることになるので、そのあたりは事前にうまく計算しましょう。


子プロセスのExcelはウィンドウが非表示なので見えませんが、実際には親と同じようなUIがちゃんとあります。

VBEも同じように完全に別物として立ち上げられます。

つまり、どういうことかというと、子プロセスは親プロセスのVBEで指定したブレークポイントで止められません。

10個に分散させた場合、10個共で別々にエラーを吐きます。

まずは単一プロセスで完全にデバッグを済ませてから、分散に挑戦するようにしてください。


分散処理が一度動き出したら止めることができません。

一般的なマルチスレッドと違い、親プロセスからの介入の余地はありません。（外部ファイル等で中断用の仕組みを実装すれば或いは…）

ウィンドウを表示にしておけば、ちゅんちゅん奥義で止めることは出来るかもしれませんが、何個もExcelには立ち上がってほしくないので普通は非表示にしておくと思いますし無理でしょう。

つまり、タスクマネージャー（Ctrl+Shift+ESC）よりExcelを強制終了する必要があります。

www.excel-chunchun.com


Application.OnTime について

今回使用したApplication.OnTimeですが、この独特な性質から通常の方法ではどうしようもない時の秘密兵器として使用できます。

例えば、

  • 溜まってるイベントが全て処理されてから開放を行いたい時

  • エラー等でプログラムが止まってしまった状況からの自動復帰

  • 監視用の無限ループ

などなど。興味のある人は調べてみてください。

今の所記事は書いてませんが、筆者のツイートを漁ると出てくるかもしれません。


本記事を超有名サイトで引用して頂きました。

本件について丁寧に解説してくれているので、気になる人ははこちらもご覧ください。(2019/4/2)

クラスとイベントとマルチプロセス並列処理 - エクセルの神髄


まとめ

  • 本来のマルチスレッドは出来ない！

  • VBSを使えば疑似マルチスレッドなら出来る！

  • Excelブック一つでも擬似マルチスレッドは出来る！


このアイディアを浮かぶためのキッカケを作って下さったA氏には心からの感謝を。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

プライバシーポリシー