xlsmをxlsxに変換して保存するVBA|マクロを外して社外に送る方法【VBA中級】

Excel開発のシーンでは、ベースとなるExcelマクロ形式のファイルの更新後、VBAソースの削除が必要となる場合があります。

みなさん、こんにちは。セルネッツ竹本です。

今回は、「VBAプログラムソース削除の方法」をテーマに、解説をしたいと思います。

結論から言うと、マクロ付きのブック(*.xlsm)からマクロを外すだけなら、VBAソースを1行ずつ消す必要はありません。
送りたいシートを新しいブックにコピーし、「Excel ブック(*.xlsx)」形式で保存すれば、マクロは自動で外れます。

この記事では、次のことがわかります。

• xlsmとxlsxの違いと、マクロを外して保存する場面
• 手作業とVBAで、xlsmをxlsxに変換して保存する方法
• VBAソースそのものを削除する方法と、社外に送る前の注意点

xlsmとxlsxの違い

xlsmとxlsxの違いは、マクロ(VBA)を保存できるかどうかです。
見た目のシートや数式は同じでも、xlsxにはマクロを入れられません。

拡張子	ファイルの種類	マクロ	VBAで保存するときの値
.xlsx	Excel ブック	保存できない	xlOpenXMLWorkbook(51)
.xlsm	Excel マクロ有効ブック	保存できる	xlOpenXMLWorkbookMacroEnabled(52)
.xlsb	Excel バイナリ ブック	保存できる	xlExcel12(50)
.xls	Excel 97-2003 ブック	保存できる	xlExcel8(56)

値はMicrosoftの「XlFileFormat 列挙体 (Excel)」によるものです。
社外の方に渡すなら、マクロの警告が出ないxlsxが安心です。

マクロを外して保存するのはどんな場面か

例えば、部門スタッフが共有している「在庫一覧表.xlsm」の更新後、現在庫情報を出荷担当者へExcel(*.xlsx)でメール添付で送付したい。

要望としては、外部のお客様には、純粋なExcel形式で送付したいケースが多いと思うので、その方法をご紹介します。

保存したxlsxを、そのままメールで送るところまで自動化することもできます。
手順は「VBAでOutlookメール送信する方法と限界」で解説しています。

手作業でxlsxに変換する方法

手作業なら、「名前を付けて保存」でファイルの種類をxlsxに変えるだけです。

1. xlsmのブックを開き、「ファイル」>「名前を付けて保存」を選ぶ
2. ファイルの種類で「Excel ブック(*.xlsx)」を選び、「保存」を押す
3. 「次の機能はマクロなしのブックに保存できません」と出たら、「はい」を押す

これで、マクロを除いたxlsxが別のファイルとしてできます。
元のxlsmはそのまま残りますが、開いている画面はxlsxに切り替わっている点に気を付けてください。

VBAでxlsxとして保存する方法(おすすめ)

VBAなら、シートを新しいブックにコピーし、FileFormat:=xlOpenXMLWorkbookで保存するのがおすすめです。
元のブックには手を加えず、後で説明するセキュリティ設定の変更もいりません。

Option Explicit

Sub M_xlsx形式で保存()
    Dim wbNew As Workbook
    Dim strPath As String

    '保存先(日付入りのファイル名)
    strPath = ThisWorkbook.Path & "\在庫一覧_" & Format(Date, "yyyymmdd") & ".xlsx"

    '送りたいシートだけを新しいブックにコピー
    ThisWorkbook.Worksheets("在庫一覧").Copy
    Set wbNew = ActiveWorkbook

    'マクロなしのブック(xlsx)として保存
    Application.DisplayAlerts = False
    wbNew.SaveAs Filename:=strPath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    wbNew.Close SaveChanges:=False
    MsgBox "保存しました。" & vbCrLf & strPath
End Sub

ポイントは次の3つです。

• 複数のシートを送るときは、Worksheets(Array(“在庫一覧”, “出荷予定”)).Copy のように並べる
• シートのモジュールに書いたコードもコピーされますが、xlsxで保存した時点で外れる
• DisplayAlerts = False で、「マクロなしのブックに保存できません」の確認を出さずに保存する

保存の処理があちこちのマクロに散らばっている場合は、1か所にまとめると管理が楽になります。
考え方は「Save As ステートメントを1Moduleに集約して分散管理を回避」をご覧ください。

VBAソースそのものを削除する方法

コピーしたブックからコードを消しておきたい場合は、VBProjectの各モジュールのコードを全行削除します。
この方法は、事前にセキュリティ設定を変える必要があります。

【注意】VBAプロジェクトオブジェクトモデルへのアクセスを信頼する(V)
マクロ実行環境のPCでは、以下の設定が必要になります。
既定値はチェックがありませんが、オンにしないと実行時エラーが発生するので、留意してください。

設定の場所は、「ファイル」>「オプション」>「トラスト センター」>「トラスト センターの設定」>「マクロの設定」です(バージョンによっては「セキュリティ センター」と表示されます)。
「開発」タブの「マクロのセキュリティ」からも開けます。

Microsoftサポートの説明では、この設定は既定でアクセスを拒否する、開発者向けの設定です(Excel のマクロのセキュリティ設定を変更する)。
コード中のコメントにある「ツール>マクロ>セキュリティ」は古いExcelの操作なので、上の手順に読み替えてください。

以下のModuleをCallします。

Option Explicit

'★★★　不要オブジェクト削除用～外部保存における活用
Public P_BufBookModule削除用 As Workbook
Public P_VB_CompoModule削除用 As Object

'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
Sub M_VBAソース削除()
'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
'ツール>マクロ>セキュリティ>信頼できる発行元>Visual Basic プロジェクトへのアクセスを信頼するにチェック

   '★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
    '★ 自分自身のSheetモジュール(プロシージャ)を削除
    '★ VBA「プロジェクトへのアクセスをすべて信頼する」ONが前提
    '★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

        Dim lngLines As Long

        Set P_BufBookModule削除用 = ActiveWorkbook  ' 対象シートをコピーした新規ブック

        '-----(↓VBProjectに対するアクセスが許可されていない場合はエラー
        '-----( VBProject内の各コンポーネントのコードを削除

        For Each P_VB_CompoModule削除用 In P_BufBookModule削除用.VBProject.VBComponents

            With P_VB_CompoModule削除用.CodeModule

                lngLines = .CountOfLines  ' コードを削除(全行)

                If lngLines <> 0 Then .DeleteLines 1, lngLines

            End With

        Next P_VB_CompoModule削除用

End Sub

これで、VBAソースが削除されるので、xlsx 形式の保存が可能になります。

赤字の「Dim lngLines As Long」は、今回追加した1行です。
Option Explicitがあるため、この宣言がないと「変数が定義されていません」というコンパイルエラーになります。

また、このModuleは、実行したときに前面にあるブック(ActiveWorkbook)のコードを消します。
必ずシートをコピーした新しいブックを前面にしてから呼び出してください。元のxlsmで実行すると、元のソースが消えてしまいます。

2つの方法の比較

社外に送るxlsxを作るだけなら、コピーしてxlsxで保存する方法で十分です。

比べる点	コピーしてxlsxで保存	VBAソースを削除
セキュリティ設定の変更	いらない	必要(使うPCごと)
元のブックへの影響	なし	対象を間違えると元のソースが消える
コードの量	数行	コピーと保存の処理も別に必要
向いている場面	社外送付・定期的な配布	xlsmのまま、コードだけ空にしたいとき

社外に送る前の注意点

マクロを外しても、ブックの中には見えない情報が残ることがあります。送る前に次の5点を確認してください。

信頼の設定はオンのままにしない

「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」は、他のプログラムからもVBAを書き換えられる状態にする設定です。
ソース削除の方法を使った場合も、作業が終わったらオフに戻しましょう。

マクロを登録したボタンを残さない

図形やボタンに登録したマクロは、コピー先でも元のブックのマクロを指したまま残ります。
先方で押すと、元のブックを探してエラーになるため、送るシートからは削除しておくと安心です。

非表示のシートが一緒に渡らないか

手作業でブックごと保存すると、非表示のシートもそのまま相手に渡ります。
VBAで「再表示」できないようにしたシートも同じです(存在するシートなのに表示させない方法～xlSheetVeryHidden)。送るシートだけをコピーする方法なら防げます。

名前の定義や元ブックへのリンクが残らないか

シートをコピーすると、名前の定義も一緒に付いてきます。
コピーしなかったシートを参照する数式は、元のブックへのリンクになり、先方で「リンクの更新」の確認が出ることがあります。
数式を値に変えるか、不要な名前を消しておきましょう(不要な「名前の定義」を一括削除する方法)。

同じ名前のファイルを上書きしない

DisplayAlerts = False にすると、同じ名前のファイルがあっても確認なしで上書きされます。
ファイル名に日付を入れるなど、前回の送付ファイルを消さない工夫をしてください。

社外に渡す前のブックの点検項目は、「Excelシステム～納品前ブック点検【重要7項目】」にまとめています。

よくある質問

拡張子を「.xlsx」に書き換えるだけではだめですか?

だめです。ファイルの中身はxlsmのままなので、開くときに「ファイル形式またはファイル拡張子が正しくありません」と表示されて開けません。
必ず「名前を付けて保存」かVBAのSaveAsで、形式を変えて保存してください。

xlsxで保存したあと、マクロを元に戻せますか?

戻せません。xlsxで保存した時点で、マクロは削除されます。
元のxlsmは上書きせず、別のファイルとして残しておきましょう。

確認メッセージを出さずに保存するには?

保存の直前に Application.DisplayAlerts = False を書き、保存が終わったら True に戻します。
途中でエラーが出ると戻らないことがあるため、エラー処理の中でも True に戻すと安全です。

まとめ

• xlsmとxlsxの違いは、マクロを保存できるかどうか
• マクロを外すだけなら、シートをコピーしてxlsx(51)で保存する方法がおすすめ
• VBAソースを削除する方法は、信頼の設定と、対象ブックの取り違えに注意
• 送る前に、ボタン・非表示シート・名前の定義・上書きを確認する

今回のテーマは、「VBAプログラムソース削除の方法」について、解説を行いました。

本記事が、Excel実務の効率化において、ご参考お役立てになれば幸いです。

---