VBAが動かないって言われたので、レジストリを書き換えるスクリプトを書いた

概要

VBAが保護ビューのバグで起動しなくなった、
２年前の出来事を思い起こして書いています。

その時の「Excelの保護ビューがLAN内のファイルに対して働かないようにする」設定を行うPowershellを書いたお話です。

始まり

Aさん「この前作ってもらったExcelマクロなんだけど。」
私「（バグかな...）はい。」

Aさん「動かないんだよね。」
私「（でた、いつものパターンだ...）どう動かないんですか？エラーメッセージとかありました？」

Aさん「なんかね、出てた。英語だったね。」
私「（ここまで、いつものパターンだ...）そうですか。スクショとかしました？」

Aさん「してないよ。見てく？」
私「（まあ、そうですよね...）分かりました。見せてください。」

Aさん「どうぞ～」
私「（ぬん...）」

問題

ポチポチ設定を変えながら原因を切り分けてみたところ、
理由が見えてきました。

• 「インターネットから取得したファイルに対して、保護ビューを有効にする」が無効
• 「安全でない可能性のある場所のファイルに対して、保護ビューを有効にする」が無効

上記いずれかだとエラーでマクロが読み込まれないことがあるらしい。
「マクロを実行する？」と聞かれないらしい。

この辺りは、WindowsUpdate もしくは付随する OfficeUpdate によって、時々バグるらしい。

一番の問題は、全て、「らしい」ことである。

対処方法

基本的には全てデフォルト設定にする。
加えて、LAN内を「信頼できるネットワーク」に追加する。

これにより、LAN内で配布したファイルでは、
Excelのマクロの「保護ビュー」を介さずVBAを実行できるようにします。

上記を自動で行うスクリプトを書いて置き、
xlsmの配布と同時に使う。

その結果、２度とこれ関連でお呼ばれすることは無くなりました。
（２年経過）

手動設定をしてみる

「警告を表示してすべてのマクロを無効にする」をチェックして、
「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」のチェックを外します。

• 「インターネットから取得したファイルに対して、保護ビューを有効にする」
• 「安全でない可能性のある場所のファイルに対して、保護ビューを有効にする」
• 「Outlook の添付ファイルに対して、保護ビューを有効にする」

３つのチェックします。

インターネットオプションを開きます。

ローカルイントラネットを選んで「サイト」ボタンをクリックします。

「イントラネットのネットワークを自動的に検出する」のチェックを外します。

• 「ほかのゾーンに指定されていないローカル（イントラネット）のサイトをすべて含める」
• 「プロキシサーバーを使用しないサイトをすべて含める」
• 「すべてのネットワークパス（UNC)を含める」

３つをチェックします。

「詳細設定」ボタンをクリックします。

LAN内のアドレスをひたすら追加します。

以上です。

自動でやりたい

上記で手動の設定方法を書きましたが、
「ExcelVBAを動かすPCが100台あるんだけど、全部やっといて」
とか言われた日には、１回家に帰って、うさ耳サンドバッグを１万回ノックアウトしないと心が持ちません。

さて、手動など心が何個あっても足りないので、Powershellでスクリプトを書いて自動化してしまいます。

この自動化は Active Directory の Group policy や、Ansibleで解決するほうがラクです。

設定するレジストリの確認

まずは設定が必要なレジストリを見てみましょう。

• 「警告を表示してすべてのマクロを無効にする」にチェックを付ける
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\??\Excel\SecurityのVBAWarningsを2にする
• 「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」 のチェックを外す
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\??\Excel\SecurityのAccessVBOMを0にする
• 「インターネットから取得したファイルに対して、保護ビューを有効にする」のチェックを付けます
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\??\Excel\Security\ProtectedViewのDisableInternetFilesInPVを0にする
• 「安全でない可能性のある場所のファイルに対して、保護ビューを有効にする」のチェックを付けます
• HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\??\Excel\Security\ProtectedViewのDisableUnsafeLocationsInPVを0にする
• 「Outlook の添付ファイルに対して、保護ビューを有効にする」のチェックを付けます
• HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\??\Excel\Security\ProtectedViewのDisableAttachmentsInPVを0にする
• 「Office のサインイン ボタンを非表示」を有効にする
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\??\Common\SignInのSignInOptionsを3にする
• 「イントラネットのネットワークを自動的に検出する」のチェックを外す
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMapのAutoDetectを0にする
• 「ほかのゾーンに指定されていないローカル（イントラネット）のサイトをすべて含める」にチェックを付ける
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMapのIntranetNameを1にする
• 「プロキシサーバーを使用しないサイトをすべて含める」にチェックを付ける
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMapのProxyBypassを1にする
• 「すべてのネットワークパス（UNC)を含める」にチェックを付ける
  • HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMapのUNCAsIntranetを1にする
• 「ローカルイントラネット」にネットワークを追加する
  • HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Rangesの子要素を追加
    • 追加したいネットワークの数だけRange[番号]という名前で子要素を作成
      • 例：Range1
    • Range[番号]それぞれに子要素:Rangeを作成し、値として各ネットワークのアドレスを設定
      • 例：192.168.1.0
    • Range[番号]それぞれに子要素fileを作成し、1を設定

スクリプト内でこれらのレジストリを設定していきます。

上記のレジストリアドレスの??という部分には、Excelのバージョンによって異なる文字列が入ります。
そのため、全バージョンのExcelに対応できるようにスクリプト内で随時書き換えを行います。

Powershellスクリプトの作成

main.ps1という感じで、拡張子をps1にしたテキストファイルを作成します。
この中身を下記のスクリプトにします。

main.ps1

Write-Host "Please wait 'Finish' from here." -ForegroundColor White -BackgroundColor Black

function Set-ExcelToDefault {
    param (
        # Path of Target Excel Registry

        [string]
        $TargetExcelPath
    )
    if (Test-Path $TargetExcelPath) {
        # 「警告を表示してすべてのマクロを無効にする」

        Set-ItemProperty -Path $TargetExcelPath\Security -Name VBAWarnings -Value 2
        # 「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」 のチェックを外します

        Set-ItemProperty -Path $TargetExcelPath\Security -Name AccessVBOM -Value 0
        # 「インターネットから取得したファイルに対して、保護ビューを有効にする」のチェックを付けます

        Set-ItemProperty -Path $TargetExcelPath\Security\ProtectedView -Name DisableInternetFilesInPV -Value 0
        # 「安全でない可能性のある場所のファイルに対して、保護ビューを有効にする」のチェックを付けます

        Set-ItemProperty -Path $TargetExcelPath\Security\ProtectedView -Name DisableUnsafeLocationsInPV -Value 0
        # 「Outlook の添付ファイルに対して、保護ビューを有効にする」のチェックを付けます

        Set-ItemProperty -Path $TargetExcelPath\Security\ProtectedView -Name DisableAttachmentsInPV -Value 0
    }
}

function Set-CommonToDefault {
    param (
        # Path of Target Office Registry

        [string]
        $TargetCommonPath
    )
    if (Test-Path $TargetCommonPath) {
        # 「Office のサインイン ボタンを非表示」

        Set-ItemProperty -Path $TargetCommonPath\SignIn -Name SignInOptions -Value 3
    }
}

function Get-ExcelRegistryPath {
    param (
        [string]$version
    )
    return "Registry::HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\" + $version + "\Excel"
}

$targetExcels = @(
    Get-ExcelRegistryPath("16.0"), # for Excel 2016・2019

    Get-ExcelRegistryPath("15.0"), # for Excel 2016・2019

    Get-ExcelRegistryPath("14.0"), # for Excel 2016・2019

    Get-ExcelRegistryPath("12.0"), # for Excel 2016・2019

    Get-ExcelRegistryPath("11.0")# for Excel 2016・2019

)

foreach ($targetExcel in $targetExcels) {
    # Test-Path $targetExcel

    Set-ExcelToDefault -TargetExcelPath $targetExcel
}

function Get-CommonExcelRegistryPath {
    param (
        [string]$version
    )
    return "Registry::HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\" + $version + "\Common"
}

$targetCommons = @(
    Get-CommonExcelRegistryPath("16.0"), # for Excel 2016・2019

    Get-CommonExcelRegistryPath("15.0")# for Excel 2016・2019

)

foreach ($targetCommon in $targetCommons) {
    Set-CommonToDefault -TargetCommonPath $targetCommon
}

$LocalIntranetZoneSetting = "Registry::HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap"
# 「イントラネットのネットワークを自動的に検出する」のチェックを外す

Set-ItemProperty -Path $LocalIntranetZoneSetting -Name "AutoDetect" -Value 0
# 「ほかのゾーンに指定されていないローカル（イントラネット）のサイトをすべて含める」にチェックを付ける

Set-ItemProperty -Path $LocalIntranetZoneSetting -Name "IntranetName" -Value 1
# 「プロキシサーバーを使用しないサイトをすべて含める」にチェックを付ける

Set-ItemProperty -Path $LocalIntranetZoneSetting -Name "ProxyBypass" -Value 1
# 「すべてのネットワークパス（UNC)を含める」にチェックを付ける

Set-ItemProperty -Path $LocalIntranetZoneSetting -Name "UNCAsIntranet" -Value 1

$LocalIntranetZonePath = "Registry::HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Ranges"
$LocalSubnets = @(
    "192.168.0.*",
    "192.168.1.*",
    "192.168.2.*",
    "192.168.3.*",
    "192.168.4.*",
    "192.168.5.*"
)
if (Test-Path $LocalIntranetZonePath) {
    # 使用済みのレジストリキーを確認しておく

    $oldnums = @()
    foreach ($item in $(Get-ChildItem $LocalIntranetZonePath)) {
        $buf = $($item.PSChildName).Substring($item.PSChildName.Length - 1, 1)
        if ($oldnums -notcontains $buf) {
            $oldnums += $buf
        }
    }
    foreach ($LocalSubnet in $LocalSubnets) {
        # 登録済のIP範囲かどうか確認する

        $neverSet = $true
        foreach ($item in $(Get-ChildItem $LocalIntranetZonePath)) {
            if ($($(Get-ItemProperty -Path $("Registry::" + $item)).":Range") -eq $LocalSubnet) {
                $neverSet = $false
            }
        }
        # 未設定のIP範囲であればレジストリに登録する

        if ($neverSet) {
            $addid = 1
            while ($true) {
                if ($oldnums -notcontains $addid) {
                    $oldnums += $addid
                    # レジストリキーを作成

                    New-Item $LocalIntranetZonePath\Range$addid
                    # プロパティ「:Range」を追加

                    New-ItemProperty $LocalIntranetZonePath\Range$addid -Name ":Range" -Value $LocalSubnet
                    # プロパティ「file」を追加

                    New-ItemProperty $LocalIntranetZonePath\Range$addid -Name "file" -Value 1
                    break
                }
                else {
                    $addid += 1
                }
            }
        }
    }
}

Write-Host "Finish"

このスクリプトは上述したレジストリ設定を自動で行ってくれるものです。

スクリプト起動用バッチの作成

上記のPowershellスクリプトをユーザーのPC内で実行するには、
ユーザー環境にスクリプト実行権限を付与する必要があります。

具体的にはPowershellスクリプトの実行ポリシーをRemoteSignedまで引き下げます。

ただし、実行権限を書き換えてそのままでは、
セキュリティ対策上問題が残ります。

そこで、実行するスクリプトの中でだけ権限を付与します。

また、普通にPowershellを動かすと遅いので、
ユーザー環境を読み込まないようなオプションを付けて起動します。

これらの項目を盛り込んだのが下記のバッチです。

launch.cmdのような感じで、テキストファイルの拡張子をcmdにして、中身には下記のコードをコピペします。

launch.cmd

@echo off
echo "Excel Setting"
pushd %~dp0
pwsh -Noprofile -ExecutionPolicy RemoteSigned -File ./main.ps1
pause

１行目：実行時に、この中身を表示しない
２行目：タイトルコール
３行目：カレントディレクトリをこのファイルの場所にする
４行目：powershellスクリプトを実行
５行目：終了メッセージを表示

４行目の最後のファイル名は上記で作成したPowershellスクリプトのファイル名に書き換えます。

配布もしくは使用

バッチファイルをPowershellスクリプトと同じフォルダに入れます。
Excelマクロのユーザー環境へ持ち込み、バッチファイルをダブルクリックで実行します。

sshなどでユーザー環境に接続できる場合、
100台あっても一瞬で設定が完了します。

このスクリプトが利用できる環境

• Windows 7, 8, 10, 11
• Excel 2007, 2010, 2013, 2016, 2019
• Powershell 6 or later
• Windows Powershell 5.1

まとめ

これで私の電話は、「マクロが起動しない」という理由で鳴ることは無くなりました。
この記事を読んで、VBAコーダーの皆様の悩みが少しでも減りますように！

Excelsior!