セル操作をするPowershellスクリプトを生成実行するVBAモジュールをPowershellで書き込む

きっかけ

もう VBA を書きたくない。Powershell が好きだから、Powershell で何とかしたい。

でもファイル増えるのはイヤ。ユーザーに配るのは Excel ファイル１つにしたい。

Powershell ファイル生成実行する VBA にしたら？

いや VBA 触りたくないんだよ。

それなら、Powershell で、そのVBAコードを書き込めれば、全部 Powershell として扱えるよね。

いまここ

VBAでVBAを書き込む

Powershell で動作させる前に、まずは VBA で同じことができるか試してみます。

プロジェクト構成

Module1にはCreateModuleサブプロシージャが書かれています。この、CreateModuleサブプロシージャを使って、MyModuleの中に、myfuncサブプロシージャを書き込んでみます。

Excel の マクロボタンから、CreateModuleを実行してみます。
実行後に、もう一度 Excel の マクロボタンを押してみましょう。

すると、一覧にmyfuncが増えています。成功ですね
そのまま、myfuncを実行してみましょう。

へい

コード

さて、肝心のコードです。

Module1

Sub CreateModule()
    Dim Code As String
    Dim ModuleName As String: ModuleName = "myModule"
    Dim existModuleName As Boolean: existModuleName = False
    Code = _
        "sub myfunc()" + vbNewLine + _
        vbTab + "msgbox ""Hey!""" + vbNewLine + _
        "end sub"
    Dim VBComponentItem As VBComponent
    With ThisWorkbook.VBProject
        For Each VBComponentItem In .VBComponents
            If ModuleName = VBComponentItem.Name Then existModuleName = True
        Next
        With .VBComponents
            If existModuleName Then .Remove .Item(ModuleName)
            With .Add(vbext_ct_StdModule)
                .Name = ModuleName
                .CodeModule.AddFromString Code
            End With
        End With
    End With
End Sub

上記のコードに含まれる、

    Code = _
        "sub myfunc()" + vbNewLine + _
        vbTab + "msgbox ""Hey!""" + vbNewLine + _
        "end sub"

という部分が、書き込む VBA コードです。

実際に書き込まれた後、VBA コードはこうなります。

myModule

Sub myfunc()
    MsgBox "Hey!"
End Sub

VBA を VBA で操作するには、VBProjectとかVBComponentあたりを使う必要があります。これを利用可能にしてくれる参照設定が必要です。

ちょっとめんどくさいので、RubberDuckを使うと簡単に追加することができます。

PowershellでVBAを書き込む

上記の VBA を Powershell に落とし込んでみました。

OfficeをCOM Object経由でPowershellから扱うときの面倒を少しマシにするを参考にしています。

write-vba.ps1

using namespace System.Management.Automation
using namespace System.Collections.Generic
using namespace System.Runtime.InteropServices
using namespace Microsoft.Office.Interop.Excel

# VBAのEnumを使うためにアセンブリをロード

# VBAのEnumを使うためにアセンブリをロード

@(
    "C:\Windows\assembly\GAC_MSIL\Microsoft.VisualBasic\*\Microsoft.VisualBasic.dll"
    "C:\Windows\assembly\GAC_MSIL\office\*\OFFICE.DLL"
    "C:\Windows\assembly\GAC_MSIL\Microsoft.Vbe.Interop\*\Microsoft.Vbe.Interop.dll"
    "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\*\Microsoft.Office.Interop.Excel.dll"
).ForEach{
    Add-Type -path $_
    Write-Host $_
}

# COMObjectに拡張メソッドを追加

Update-TypeData -TypeName System.__ComObject -MemberType ScriptMethod -MemberName Tee -Value {
    param(
        [Stack[WeakReference]]
        $stack
    )
    $stack.Push([WeakReference]::new($this))

    Write-Output $this
}

# Comを解放するために使う弱参照のスタック

$refs = [Stack[WeakReference]]::new()

function CreateModule {
    param(
        [string]$fileName
    )
    $filePath = Join-Path $pwd $fileName
    $excel = New-Object -ComObject Excel.Application
    [string]$ModuleName = "myModule"
    [bool]$existModuleName = $false
    [string]$Code = "sub myfunc()`n`tmsgbox`"Hey!`"`nend sub"

    [ExcelSecurityRegistry]$excelRegistry = [ExcelSecurityRegistry]::new()
    $excelRegistry.SetWritable()

    $workbook = $excel.Workbooks.Open($filePath)
    foreach ($item in $workbook.VBProject.VBComponents) {
        if ($item.Name -eq $ModuleName) {
            $existModuleName = $true
        }
    }
    if ($existModuleName) {
        $workbook.VBProject.VBComponents.Remove($workbook.VBProject.VBComponents.Item($ModuleName))
    }
    $VBComponent = $workbook.VBProject.VBComponents.Add([Microsoft.Vbe.Interop.vbext_ComponentType]::vbext_ct_StdModule)
    $VBComponent.Name = $ModuleName
    $VBComponent.CodeModule.AddFromString($Code)
    $workbook.Save()
    $excel.Quit()

    $excelRegistry.SetToBefore()
}

class ExcelSecurityRegistry {
    [int]$defaultAccessVBOM
    [int]$defaultVBAWarnings
    [string]$excelRegistryPath = "HKCU:\Software\Microsoft\Office\15.0\excel\Security"
    [void]SetWritable() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value 1 -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value 1 -Force | Out-Null
    }
    [void]SetToBefore() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value $this.defaultAccessVBOM -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value $this.defaultVBAWarnings -Force | Out-Null
    }
    ExcelSecurityRegistry() {
        $this.defaultAccessVBOM = (Get-ItemProperty -Path $this.excelRegistryPath).AccessVBOM
        $this.defaultVBAWarnings = (Get-ItemProperty -Path $this.excelRegistryPath).VBAWarnings
    }
}

CreateModule -fileName .\ExecutePwsh.xlsm

While ($refs.Count) {
    # スタックから弱参照を取得

    $comRef = $refs.Pop()

    # 解放するCOMを参照してる変数を全て取得

    $comVar = (Get-Variable).where{ [object]::ReferenceEquals($comRef.Target, $_.Value) }

    # Applicationオブジェクトであるかの判定

    $isApp = $comRef.Target -is [Microsoft.Office.Interop.Excel.Application]

    # アプリケーションの終了前にガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
        $comRef.Target.Quit()
    }

    # COMObjectの解放

    # COMObjectの解放

    while ([Marshal]::ReleaseComObject($comRef.Target)) { }
    $comRef.Target = $null

    # 変数を削除

    $comVar | Remove-Variable
    Remove-Variable comRef

    # Application オブジェクトのガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
    }
}

動作は、上記の「 VBA で VBA を書き込む」ものと同じです。しかし、急に長くなりましたね

それと、"HKCU:\Software\Microsoft\Office\15.0\excel\Security"という部分は、動作環境にある Excel のバージョンによって異なります。改善ポイントですね。

Add-Type -Path $item
`~~~~~~~~~~~~~~~~~~~~`
Assembly with same name is already loaded

Add-Typeにてエラーが出ます。しかし、ユーザー配布時には出ないエラーなので、とりあえず無視します。

VBAでPowershellを生成実行する

それでは次に、VBA で Powershellを書いて実行してみましょう。

まずは、Powershellを生成するCreatePayloadサブプロシージャを書きます。

Module1

Private Sub CreatePayload()
    Dim s
    Dim n
    s = Environ("TEMP") + "\temp.ps1"
    n = FreeFile
    Open s For Output As #n

    Print #n

    Print #n

    Print #n

    Print #n

    Print #n

    Close #n

End Sub

これが、$env:TEMPフォルダにtemp.ps1を生成してくれます。
中身はこんな感じになります。

%temp%/temp.ps1

Write-Host (Get-Location).Path
Write-Host this

次に、上記のtemp.ps1を起動するためのバッチファイルを生成する、CreateLauncherサブプロシージャを作成します。

Module1

Private Sub CreateLauncher()
    Dim s
    Dim n
    s = Environ("TEMP") + "\temp.cmd"
    n = FreeFile
    Open s For Output As #n

    Print #n

    Print #n

    Print #n

    Print #n

    Print #n

    Close #n

End Sub

これで、$env:TEMPフォルダにtemp.cmdが生成されます。

%temp%/temp.cmd

@echo off
cd %temp%
pwsh -NoProfile -ExecutionPolicy Unrestricted ./temp.ps1
pause > nul
exit

最後に、上記２つのサブプロシージャで生成したファイルを実行するStartModuleサブプロシージャを書きます。

Module1

Sub StartModule()
    Call CreateLauncher
    Call CreatePayload

    Dim WshObject As WshShell
    Dim sPath

    sPath = "%temp%/temp.cmd"

    Set WshObject = New WshShell

    Call WshObject.Run(sPath, 1, WaitOnReturn:=True)
End Sub

StartModuleを実行すると結果はこんな感じです。

ステキですね

かけ合わせる①

「 VBA で Powershell を生成実行」
「 Powershell で VBA を書く」

はできたので、

次は、「 Powershell で VBA （ VBA で Powershell を生成実行）を書く」ですね。

先ほどの Powershell の中で、

write-vba.ps1

[string]$Code = "sub myfunc()`n`tmsgbox`"Hey!`"`nend sub"

この部分を書き換えます。

write-vba.ps1

    [string]$Code = ""
    $codeList = @(
        "Sub CreatePayload()"
        "Dim s"
        "Dim n"
        "s = Environ(`"TEMP`") + `"\temp.ps1`""
        "n = FreeFile"
        "Open s For Output As #n"

        "Print #n"

        "Print #n"

        "Print #n"

        "Print #n"

        "Print #n"

        "Close #n"

        "End Sub"
    )
    foreach ($item in $codelist) {
        $Code += $item + "`n"
    }

Powershellを実行してみると、

myfuncが上書きされて、CreatePayloadサブプロシージャが書き込めていることが分かります。

いいですね

かけ合わせる②

「 VBA で Powershell を生成実行」
「 Powershell で VBA を書く」
「 Powershell で VBA （ VBA で Powershell を生成実行）を書く」

はできたので、

次は、「 Powershell で VBA （ VBA で Powershell （ Powershell で Excel を操作）を生成実行）を書く」ですね。

「 Powershell で Excel を操作」できる Powershell を書いて、
そのファイルを逐一 VBA に反映できるようにしていきましょう。

まず、先ほどのCreateModuleは目障りなので間違えて実行しないように、Privateを付与しておきます。

Module1

Private Sub CreateModule()

Module1のCreatePayloadもコメントアウトするか消しておきます。

Module1

'Private Sub CreatePayload()
'    Dim s
'    Dim n
'    s = Environ("TEMP") + "\temp.ps1"
'    n = FreeFile
'    Open s For Output As #n

'    Print #n

'    Print #n

'    Print #n

'    Print #n

'    Print #n

'    Close #n

'End Sub

ここまで来たら、write-vba.ps1を書き換えます。

write-vba.ps1

using namespace System.Management.Automation
using namespace System.Collections.Generic
using namespace System.Runtime.InteropServices
using namespace Microsoft.Office.Interop.Excel

# VBAのEnumを使うためにアセンブリをロード

# VBAのEnumを使うためにアセンブリをロード

foreach ($item in @(
        "C:\Windows\assembly\GAC_MSIL\Microsoft.VisualBasic\*\Microsoft.VisualBasic.dll"
        "C:\Windows\assembly\GAC_MSIL\office\*\OFFICE.DLL"
        "C:\Windows\assembly\GAC_MSIL\Microsoft.Vbe.Interop\*\Microsoft.Vbe.Interop.dll"
        "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\*\Microsoft.Office.Interop.Excel.dll"
    )) {
    Add-Type -Path $item
}

# COMObjectに拡張メソッドを追加

Update-TypeData -TypeName System.__ComObject -MemberType ScriptMethod -MemberName Tee -Value {
    param(
        [Stack[WeakReference]]
        $stack
    )
    $stack.Push([WeakReference]::new($this))

    Write-Output $this
}

# Comを解放するために使う弱参照のスタック

$refs = [Stack[WeakReference]]::new()

function CreateModule {
    param(
        [string]$fileName
    )
    $filePath = Join-Path $pwd $fileName
    $excel = New-Object -ComObject Excel.Application
    [string]$ModuleName = "PayloadCreater"
    [bool]$existModuleName = $false
    [string[]]$codeList = Get-Content "./main.ps1"
    [string]$Code = ""
    $codelistBeforePrint = @(
        "Static Sub CreatePayload()"
        "Dim s"
        "Dim n"
        "s = Environ(`"TEMP`") + `"\temp.ps1`""
        "n = FreeFile"
        "Open s For Output As #n"

    )
    $codelistAfterPrint = @(
        "Close #n"

        "End Sub"
    )

    for ($i = 0; $i -lt $codeList.Count; $i++) {
        $codeList[$i] = ("Print #n, `"" + ($codeList[$i] -replace "`"","`"`"") + "`"")

    }

    $codeList = $codelistBeforePrint + $codeList + $codelistAfterPrint

    foreach ($item in $codelist) {
        $Code += $item + "`n"
    }

    [ExcelSecurityRegistry]$excelRegistry = [ExcelSecurityRegistry]::new()
    $excelRegistry.SetWritable()

    $workbook = $excel.Workbooks.Open($filePath)
    foreach ($item in $workbook.VBProject.VBComponents) {
        if ($item.Name -eq $ModuleName) {
            $existModuleName = $true
        }
    }
    if ($existModuleName) {
        $workbook.VBProject.VBComponents.Remove($workbook.VBProject.VBComponents.Item($ModuleName))
    }
    $VBComponent = $workbook.VBProject.VBComponents.Add([Microsoft.Vbe.Interop.vbext_ComponentType]::vbext_ct_ClassModule)
    $VBComponent.Name = $ModuleName
    $VBComponent.CodeModule.AddFromString($Code)
    $workbook.Save()
    $excel.Quit()

    $excelRegistry.SetToBefore()
}

class ExcelSecurityRegistry {
    [int]$defaultAccessVBOM
    [int]$defaultVBAWarnings
    [string]$excelRegistryPath = "HKCU:\Software\Microsoft\Office\15.0\excel\Security"
    [void]SetWritable() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value 1 -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value 1 -Force | Out-Null
    }
    [void]SetToBefore() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value $this.defaultAccessVBOM -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value $this.defaultVBAWarnings -Force | Out-Null
    }
    ExcelSecurityRegistry() {
        $this.defaultAccessVBOM = (Get-ItemProperty -Path $this.excelRegistryPath).AccessVBOM
        $this.defaultVBAWarnings = (Get-ItemProperty -Path $this.excelRegistryPath).VBAWarnings
    }
}


CreateModule -fileName .\ExecutePwsh.xlsm

While ($refs.Count) {
    # スタックから弱参照を取得

    $comRef = $refs.Pop()

    # 解放するCOMを参照してる変数を全て取得

    $comVar = (Get-Variable).where{ [object]::ReferenceEquals($comRef.Target, $_.Value) }

    # Applicationオブジェクトであるかの判定

    $isApp = $comRef.Target -is [Microsoft.Office.Interop.Excel.Application]

    # アプリケーションの終了前にガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
        $comRef.Target.Quit()
    }

    # COMObjectの解放

    # COMObjectの解放

    while ([Marshal]::ReleaseComObject($comRef.Target)) { }
    $comRef.Target = $null

    # 変数を削除

    $comVar | Remove-Variable
    Remove-Variable comRef

    # Application オブジェクトのガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
    }
}

次に、先ほどのStartModuleの中で、CreatePayloadを呼び出す部分があります。これを、自動生成されるPayloadCreaterクラスモジュールから呼び出すように変更します。3,4行目でcreaterを追加していますね。

Sub StartModule()
    Call CreateLauncher
    Dim creater As New PayloadCreater
    Call creater.CreatePayload

    Dim WshObject As WshShell
    Dim sPath

    sPath = "%temp%/temp.cmd"

    Set WshObject = New WshShell

    Call WshObject.Run(sPath, 1, WaitOnReturn:=True)
End Sub

これで、write-vba.ps1を実行するたびに、main.ps1の内容が、PayloadCreaterクラスモジュールのCreatePayloadサブプロシージャに適用されます。

試しに、main.ps1の内容を以下のようにしてみます。

main.ps1

Write-Host (Get-Location).Path
Write-Host "this_is_main_ps1"

そうしましたら、write-vba.ps1を実行してから、ExecutePwsh.xlsmを開いてみます。

StartModuleを実行してみると？

ブラボーです

最後にセル操作を追加

main.ps1にセル操作ができるコードを入れて試してみましょう。
みなさんのコメントをお待ちしております

セル操作の参考記事を掲載させていただきます。

注意

WindowsにデフォルトでインストールされているのはPowershellではなく、Windows Powershellなので、注意が必要です。

まとめ

そもそも、Powershellを書いてるけど、VBAのリファレンス読みながらだったりして複雑な気持ちになります。

Excelsior!