エクセルに入れたデータをPythonに渡す→Pythonで処理→結果をエクセルに返す、という連携をやってみたくて調べた備忘録です。

具体的にやりたかったのは以下のような感じ。

前提

• pythonの環境変数PATHは設定済み
• 上記画像のvba.xlsmとtest.pyは同じフォルダに配置されている
• 処理は以下の通り
  • エクセルのB2セル・C2セルの中身をPythonファイルに渡す
  • Pythonでは処理 main() を実行する
  • main() の結果を標準出力としてVBAに返す
  • VBAでは結果をD2セルに記入する
• pythonファイル側で何かエラーがあったら知りたい

結論

下記コードでうまくいきました。コピペして使ってもらっても大丈夫です。

■ VBA（vba.xlsmの標準モジュール）

Option Explicit

Sub Button()
    ' === 変数宣言 ===
    Dim sh As Object
    Dim cmd As String
    Dim proc As Object
    Dim scriptPath As String
    Dim wb As Workbook
    Dim b2 As String, c2 As String


    ' === 設定 ==　※ファイル名は適宜変更してください※
    scriptPath = ThisWorkbook.Path & "\test.py"

    ' B2セル、C2セルの値を取得
    Set wb = ThisWorkbook
    b2 = wb.Worksheets(1).Range("B2").Value
    c2 = wb.Worksheets(1).Range("C2").Value

    ' pythonを実行するコマンド
    Set sh = CreateObject("WScript.Shell")
    cmd = "python """& scriptPath & """ """& b2 & """ """& c2 &""""
    Set proc = sh.Exec(cmd)

    ' 処理が完了するまで待機
    Do While proc.Status = 0
        DoEvents
    Loop

    If proc.ExitCode <> 0 Then
      Dim errOut As String
      On Error Resume Next
      errOut = proc.StdErr.ReadAll
      On Error GoTo 0
      MsgBox "PowerShell / Python 実行でエラーが発生しました。" & vbCrLf & errOut, vbCritical

    Else

   ' pythonの標準出力を受け取って書き込む
      Dim out As String
      out = proc.StdOut.ReadAll
      wb.Worksheets(1).Range("D2").Value = out
   EndIf

    MsgBox "処理完了"

End Sub

■ Python（test.py）

import sys

# 処理内容
def main(): 
	# VBAから渡される引数
	arg1 = sys.argv[1]
	arg2 = sys.argv[2]

	# 処理の例
	result = arg1 + arg2

	# 標準出力（VBAで受け取る）
	print(result)

if __name__ == "__main__":
    try:
        main()
        sys.exit(0)
	# 正常終了↑
    except Exception as e:
        print(f"エラーが発生しました:\n{e}", file=sys.stderr)
        sys.exit(1)
	# エラー終了↑

コメントを書いているのでなんとなく何をやってるか分かると思いますが、自分のふりかえり用に説明を書いておきます。

コードの説明

一番知りたい、VBAからPythonを実行してるのはVBAの以下の部分。

  ' pythonを実行するコマンド
    Set sh = CreateObject("WScript.Shell")
    cmd = "python """& scriptPath & """ """& b2 & """ """& c2 &""""
    Set proc = sh.Exec(cmd)

VBAのオブジェクトをよく理解できていないのでAIに教えてもらった比喩を下記に引用します。

• Set sh = CreateObject("WScript.Shell")
  • Windows に「コマンドを実行する役割の人」を1人呼んでいる
• Set proc = sh.Exec(cmd)
  • OSに「このコマンド（cmd）を実行して」と依頼
  • コマンドプロンプトで言うところのエンターキー（実行）はExec
  • 標準入出力パイプ付きで起動（←これのおかげでプロセス制御ができる）

プロセス制御とは以下のようなことらしいです。

• Pythonの標準出力を取得
• Pythonの標準エラーを取得
• ExitCode を取得
• 実行中かどうか監視
• 同期処理ができる

Pythonにデータを投げっぱなしの場合（処理結果を別ファイルに出力するなどVBAに戻る必要がない場合）はプロセス制御する必要がないのですが、実行結果を受け取る必要がある場合は、Pythonとのパイプを持っておく必要がある……というようなことらしいです。

なお上記で実行しているコマンドは　python scriptPath b2 c2 です。scriptpathはVBAの「== 設定 ==」部分で定義した通りなので実際は　python C:\xxxxx\xxxxx\test.py b2 c2 のようなコマンドが実行されているイメージです（b2、c2は引数）

処理が完了するまでは待機

Pythonの実行中はVBAのコードが先に進まないように待機する必要があります。そのためのVBAコードが以下。

    ' 処理が完了するまで待機
    Do While proc.Status = 0
        DoEvents
    Loop

proc.Statusの数字は「0」＝「procを実行中」、「1」＝「procを終了済み」という意味。procが実行中の場合は上記のループをぐるぐるまわります。

DoEventsはマクロが実行中に画面がフリーズするのを防止してくれるとのこと、外部プロセスを待機するときによく使うらしいです（詳細を知りたい場合は他のサイトを参照下さい）

結果を受け取る

Pythonの処理が完了したらproc.Status = 1になるため、上記のループを抜けて、下記コードを実行します。

    If proc.ExitCode <> 0 Then
      Dim errOut As String
      On Error Resume Next
      errOut = proc.StdErr.ReadAll
      On Error GoTo 0
      MsgBox "PowerShell / Python 実行でエラーが発生しました。" & vbCrLf & errOut, vbCritical

    Else

   ' pythonの標準出力を受け取って書き込む
      Dim out As String
      out = proc.StdOut.ReadAll
      wb.Worksheets(1).Range("D2").Value = out
   EndIf

Elseの上がエラーを受け取った場合の処理、Elseの下が正常に処理完了した場合の処理です。

Pythonコードの最後に出力している sys.exit(n)　がすなわちExitCode＝nです。具体的にはPythonの以下の部分ですね。

if __name__ == "__main__":
    try:
        main()
        sys.exit(0)
	# 正常終了↑
    except Exception as e:
        print(f"エラーが発生しました:\n{e}", file=sys.stderr)
        sys.exit(1)
	# エラー終了↑

上記から受けとったExitCode（0または1）に応じて下記のように処理します。

• Pythonの処理が失敗したとき
  • 失敗理由：StdErr（標準エラー）に出力
  • StdErrの全文をerrOutに格納してエラーメッセージとして表示しています。
  • ただし、エラーメッセージを格納する段階でVBAエラーが発生する場合がある（らしいです）。そのため、On Error Resume Next　でVBAエラーが出た場合でも処理を止めずに強引に先に進めています（エラーを無視）
  • errOutにエラー内容を格納後、　On Error GoTo 0　で上記のエラー無視モードを解除しています
  • 最後にメッセージボックスでエラー内容を表示しています。
• Pythonの処理が成功したとき
  • 出力結果：StdOut（標準出力）
  • out に結果を出力して、D2セルに記入しています。

Shell関数ではだめなのか

おまけ。VBAからPythonファイルを実行する場合の方法としてShell関数もあります。

具体的には以下のような1行をVBAファイルに書くことでPythonファイルを実行できます。

Shell "python C:\xxxxx\xxxx\test.py arg1 arg2"

こちらの方がスマートに見えますが、Shell関数はファイルを起動することができても、標準出力（標準エラー）や終了コードを受け取れない（＝プロセス制御ではない）という違いがあります。

Python自体はファイルを開けば実行できるので「VBAからPythonを実行する」という用途には合っていますがこのあたりは目的によりけりですね。Shell関数以外にもファイルを開く方法としては他にいくつかあるようです。

参考：VBAでファイルを既定のアプリで開く方法

今回のようにVBAとPythonを連携したい場合、すなわちPythonから処理結果を受け取ってVBAやExcelで処理したい場合はShell関数は向かないようです。

そういうわけで今回はVBAから下記コードでPythonを実行し、処理中は待機、完了後に結果を受け取るスタイルを選びました。

  ' pythonを実行するコマンド
    Set sh = CreateObject("WScript.Shell")
    cmd = "python """& scriptPath & """ """& b2 & """ """& c2 &""""
    Set proc = sh.Exec(cmd)

もっと短いコードで連携できるかと思いきや、プロセス制御やエラー処理を考えるといろいろ手間があるのですね……。

今回のコードの大半はAIを用いて生成してもらったものの意味が分からないと実行するのは怖い、ということで勉強のためのメモでした。何かの参考になったら幸いです。

おわり。