PowerShellからVBAのプロシージャを呼ぶ

はじめに

VBA-PowerShell間でオブジェクトをやりとりしたい場合の一つの手段として、
Application.Run経由でVBAのプロシージャを呼ぶPowerShellの関数を作ってみた。

VBA側から「PublicNotCreatable」を設定した自作クラスなどを返してあげれば、VBAとPowerShell間で同じオブジェクトを参照させることが可能となる。

多用するものでは無いが、VBAだけで完結できない場合の一手段として。

コード

Function Invoke-VBAProc
{
    <#
    .SYNOPSIS
    指定されたVBAのプロシージャを実行し、その実行結果を返します。
    #>

    [CmdletBinding()]
    Param(

        <#
        実行したいプロシージャを含むOfficeファイルのフルパスを指定します。
        未保存のファイルでも動作することがあります。
        #>
        [parameter(Mandatory=$true)]
        [String]$Path,

        <#
        実行したいプロシージャを含む標準モジュールの名前を指定します。
        #>
        [parameter(Mandatory=$true)]
        [String]$ModuleName,

        <#
        実行したいプロシージャの名前を指定します。
        #>
        [parameter(Mandatory=$true)]
        [String]$ProcName
    )

    # 変数の宣言を強制
    Set-StrictMode -Version 1

    # Tryブロックに入る前に変数を用意しておく
    [__ComObject]$docOffice = $null
    [__ComObject]$appOffice = $null

    Try
    {
        # Office取得Try
        Try
        {
            $docOffice = [Runtime.InteropServices.Marshal]::BindToMoniker($Path)
            $appOffice = $docOffice.Application
        }
        Catch
        {
            Write-Error -Message Officeへの接続に失敗しました。
            Throw
        }

        # Application.Run Try
        Try
        {
            <#
            Application.Runで指定するプロシージャ名文字列。
            同名プロシージャがあっても特定できるように、「'ファイル名'!モジュール名.プロシージャ名」の形式にする。
            #>
            $appRunProc = 
                "'{0}'!{1}.{2}" -f 
                    ([IO.Path]::GetFileName($Path)),
                    $ModuleName,
                    $ProcName

            Write-Verbose -Message ('Exec:{0}' -f $appRunProc)

            Return $appOffice.Run($appRunProc)
        }
        Catch
        {
            Write-Error -Message Application.Runに失敗しました。
            Throw
        }
    }
    Finally
    {
        # COM後始末。
        If ($null -Ne $docOffice)
        {
            [Runtime.InteropServices.Marshal]::FinalReleaseComObject($docOffice) > $null
            $docOffice = $null
        }

        If ($null -Ne $appOffice)
        {
            [Runtime.InteropServices.Marshal]::FinalReleaseComObject($appOffice) > $null
            $appOffice = $null
        }
    }
}

サンプル

VBA

新規ブックを作成し、以下のコードを貼り付ける。
ブックは未保存で名前はBook1であること。

標準モジュール

Module1

Option Explicit

Private shareClass As Class1

Function CallByPowerShell() As Class1
    Set shareClass = New Class1
    shareClass.Msg = "VBA"
    shareClass.Col.Add "XYZ"
    Set CallByPowerShell = shareClass
End Function

クラスモジュール

名前はそのまま（Class1）で、プロパティを2 - PublicNotCreatableに設定しておく。

Class1

Option Explicit

Public Msg As String
Public Col As VBA.Collection

Private Sub Class_Initialize()
    Set Col = New VBA.Collection
End Sub

PowerShell

上記のInvoke-VBAProc関数を定義後以下を実行する。

Sample.ps1

[Object]$retVal = Invoke-VBAProc -Path Book1 -ModuleName Module1 -ProcName CallByPowerShell

Write-Host 最初のMsg
$retVal.Msg
Write-Host 最初のCol
$retVal.Col

# 変更
$retVal.Msg = 'PowerShell'
$retVal.Col.Add("ABC")

Write-Host 変更後Msg
$retVal.Msg
Write-Host 変更後Col
$retVal.Col

# 後始末
If ([Runtime.InteropServices.Marshal]::IsComObject($retVal))
{
    Write-Host ReleaseCOM
    [Runtime.InteropServices.Marshal]::FinalReleaseComObject($retVal) > $null
}
$retVal = $null

結果

PowerShellコンソール上には以下のように表示される。

最初のMsg
VBA
最初のCol
XYZ
変更後Msg
PowerShell
変更後Col
XYZ
ABC
ReleaseCOM

またVBAのオブジェクトも変更されているはずである。