VBAでPython3

まずはpythonの用意

純正でも、コンダでも、embedでも
pythonの実行環境を作る

次に、VBAの準備
標準モジュールに

python実行するモジュール

Option Explicit

' シェルの実行
Public Function run(ByVal cmd As String) As Variant
    Dim WSH, wExec As Object
    Set WSH = CreateObject("WScript.Shell")

    '実行
    'Set wExec = WSh.Exec("%ComSpec% /c " & cmd)
    Set wExec = WSH.exec(cmd)

    '実行結果
    Dim Result, Err
    wExec.StdIn.WriteLine "echo 処理中"
    Result = wExec.StdOut.ReadAll
    Err = wExec.StdErr.ReadAll
    If Err <> "" Then
        Debug.Print "Error:" & Err
    End If

    '解放
    Set wExec = Nothing
    Set WSH = Nothing

    run = shape(Result)
End Function

' レスポンスの整形
Public Function shape(ByVal v) As Variant
    If v = "" Then End

    '整形
    Dim str_lines As Variant


    '引数を行毎見ていって、空白以外を再編集する
    If UBound(Split(v, vbLf)) > 0 Then
        str_lines = Split(v, vbLf)
    ElseIf UBound(Split(v, vbCr)) > 0 Then
        str_lines = Split(v, vbCr)
    ElseIf UBound(Split(v, vbCrLf)) > 0 Then
        str_lines = Split(v, vbCrLf)
    End If


    ' 返却用に空白なしリストにする
    Dim res_list() As Variant
    Dim i, res_cnt As Long
    res_cnt = 0

    For i = 0 To UBound(str_lines)
        If str_lines(i) <> "" Then
            ' 行末の改行コードを飛ばしつつリストに追加
            ReDim Preserve res_list(res_cnt)
            res_list(res_cnt) = Left(str_lines(i), Len(str_lines(i)) - 1)
            res_cnt = res_cnt + 1
        End If
    Next i

    shape = res_list
End Function

' pythonの実行
Public Function run(ByVal interpreter_path As String, ByVal source_path As String, Optional ByVal args As String) As Variant
    If IsMissing(args) Then
        run = shell.run(interpreter_path & " " & source_path)
    Else
        run = shell.run(interpreter_path & " " & source_path & " " & args)
    End If
End Function

pythonを呼び出す（今回はボタン）

Private Sub CommandButton1_Click()
    Dim py, src, arg
'    py = com.desktop_path & "\..\emb-python\python-3.9.4-embed-win32\pythonw"
    py = com.desktop_path & "\..\miniconda3\pythonw"
    src = "-c"
    arg = """exec(""""" _
        & "from datetime import datetime" & "\n" _
        & "import time" & "\n" _
        & "for i in range(5):" & "\n" _
        & "\t" & "print(datetime.now())" & "\n" _
        & "\t" & "time.sleep(1)" & "\n" _
        & """"")"""

    Dim res
    res = python.run(py, src, arg)

    Dim i
    For i = 0 To UBound(res)
        Debug.Print res(i)
    Next i
End Sub

py：pythonの実行ファイル（pythonだとコンソールが表示される）
src：pyファイル
arg：コマンドライン引数など

上のはｃコマンドを使っての実行