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

# きっかけ

もう VBA を書きたくない。Powershell が好きだから、Powershell で何とかしたい。
:small_red_triangle_down:
でもファイル増えるのはイヤ。ユーザーに配るのは Excel ファイル１つにしたい。
:small_red_triangle_down:
Powershell ファイル生成実行する VBA にしたら？
:small_red_triangle_down:
いや VBA 触りたくないんだよ。
:small_red_triangle_down:
それなら、Powershell で、そのVBAコードを書き込めれば、全部 Powershell として扱えるよね。
:small_red_triangle_down:
いまここ

# VBAでVBAを書き込む

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

## プロジェクト構成

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/9eb975a4-4db2-7eba-e4ff-473e7bc2563b.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/ae126c62-dcd2-73d0-88ec-4a37315f87d6.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/b4d0db18-39a8-d670-a2be-8d2302102cc7.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/ca0bdc46-f831-9c0d-21c5-776e1322ac99.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/676b6b8d-4ae8-bfce-2127-33cf2f05382e.png)

へい:smile:

## コード

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

```vb: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
```

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

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

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

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

```vb:myModule
Sub myfunc()
    MsgBox "Hey!"
End Sub
```

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/8b531bde-f60c-919a-6f3c-96c1bf229a86.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/196f07cb-ef3c-e37d-9d55-d1d789777232.png)

# PowershellでVBAを書き込む

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

[OfficeをCOM Object経由でPowershellから扱うときの面倒を少しマシにする](https://qiita.com/SilkyFowl/items/b9a2d7c77312721c3c1a)を参考にしています。

```posh: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 を書き込む」ものと同じです。しかし、急に長くなりましたね:sob:

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

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

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

# VBAでPowershellを生成実行する

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

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

```posh: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`を生成してくれます。
中身はこんな感じになります。

```posh:%temp%/temp.ps1
Write-Host (Get-Location).Path
Write-Host this
```

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

```posh: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`が生成されます。

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

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

```posh: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`を実行すると結果はこんな感じです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/390e4b91-f429-250e-434e-a96bb13fd248.png)

ステキですね:blush:

# かけ合わせる①

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

はできたので、

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

先ほどの Powershell の中で、

```posh:write-vba.ps1
[string]$Code = "sub myfunc()`n`tmsgbox`"Hey!`"`nend sub"
```

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

```posh: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を実行してみると、

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/a4bd560a-9585-d685-356a-ab75901c4780.png)

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

いいですね:smile:

# かけ合わせる②

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

はできたので、

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

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

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

```vb:Module1
Private Sub CreateModule()
```

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

```vb: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`を書き換えます。

```posh: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`を追加していますね。

```vb
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`の内容を以下のようにしてみます。

```posh:main.ps1
Write-Host (Get-Location).Path
Write-Host "this_is_main_ps1"
```

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/ae65c83d-1c55-1f88-a862-4867cc552a28.png)

`StartModule`を実行してみると？

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134703/47cfbf9e-ed0c-fe5f-e586-8eebe49a1a1f.png)

ブラボーです:tada:

# 最後にセル操作を追加

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

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

https://qiita.com/vicugna-pacos/items/54c5e20d3c3ffae94518

https://qiita.com/miyamiya/items/161372111b68bad0744a

# 注意

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

# まとめ

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

**Excelsior!**