外部ブックを開いて、値を読み取って、閉じる。それだけのマクロのつもりだった。
ところが後日、タスクマネージャーを開いたらExcel.exeがいくつも残っていた。マクロを実行するたびに、閉じたつもりのブックが裏でプロセスとして生き続けていたようだ。
原因は単純で、開いたブックをきちんとCloseしていなかっただけ。ただ、「開く」「操作する」「保護する」「閉じる」という一連の流れを最初にセットで押さえていれば、そもそも起きなかったミスでもある。
この記事では、Workbook・Worksheetを安全に開いて、操作して、保護して、閉じるまでの一連の流れをまとめました。
ブック・シート操作の全体像
ブックを扱うマクロは、だいたい次の4フェーズで構成されます。まずは全体を俯瞰してから、各フェーズを詳しく見ていきます。
| フェーズ | 主な処理 |
|---|---|
| 開く | 存在確認・多重オープンチェック・読み取り専用/パスワード対応 |
| シートを操作する | 参照・存在確認・追加/削除・アクティブ化 |
| 保護する | シート全体の保護、特定範囲だけ編集可にする |
| 閉じる | 保存有無の制御、Excelプロセスを残さない |
ブックを安全に開く
最短コードで開く
まずは一番シンプルな形です。パスが確実に存在するとわかっている、動作確認段階で使います。
Sub OpenBook_Min()
Workbooks.Open "C:\Data\Book1.xlsx"
End Subただし、このままだとファイルが存在しない場合や、同名ブックがすでに開いている場合にエラーで止まります。実務で配布するマクロには、もう一段のチェックが必要です。
存在確認と多重オープンチェックをセットで行う
Excelは同名ブックを同時に開けない仕様です。Dir関数でファイルの存在を、Workbooksコレクションのループで開き済みかどうかを、それぞれ確認してから開きます。
Sub OpenBook_Safely()
Const TARGET As String = "C:\Data\Book1.xlsx"
Dim fileName As String
Dim wb As Workbook
If Dir(TARGET) = "" Then
MsgBox "ファイルが見つかりません: " & TARGET, vbExclamation
Exit Sub
End If
fileName = Dir(TARGET)
For Each wb In Workbooks
If wb.Name = fileName Then
MsgBox fileName & " は既に開いています。", vbExclamation
Exit Sub
End If
Next wb
Workbooks.Open Filename:=TARGET
End Subこの2つのチェックだけで、実務でよく起きるエラーの大半は防げます。
パスが決まっていないときはダイアログで選ばせる
対象ファイルのパスを固定できない場合は、ユーザーに選んでもらうのが安全です。戻り値は選択パス（文字列）か、キャンセル時のFalseのどちらかになります。
Sub OpenBook_PickDialog()
Dim filePath As Variant
filePath = Application.GetOpenFilename("Excelブック,*.xls?")
If VarType(filePath) = vbBoolean Then Exit Sub ' キャンセル
Workbooks.Open Filename:=CStr(filePath)
End Sub読み取り専用・パスワード付きで開く
参照専用のブックにはReadOnly:=Trueを、パスワード保護されたブックにはPassword:=を指定します。実務ではこの2つを組み合わせたテンプレをよく使います。
Sub OpenBook_ReadOnlyWithPassword()
Dim wb As Workbook
Set wb = Workbooks.Open( _
Filename:="C:\Data\Book1.xlsx", _
ReadOnly:=True, _
Password:="secret123")
End Subパスワードが不要なファイルにPassword:=を渡しても無視されるだけなので、両方の引数を常にセットで書いておいても実害はありません。
Worksheetの基本操作
シートを名前で参照する
シートは名前とインデックスのどちらでも参照できますが、インデックス指定はシートの並び順が変わると別のシートを指してしまいます。基本的には名前で参照するようにしています。
Sub ReferSheet()
Dim wsByName As Worksheet
Set wsByName = ThisWorkbook.Worksheets("売上")
' 並び順に依存するため、シートが増減する運用では避けたい
Dim wsByIndex As Worksheet
Set wsByIndex = ThisWorkbook.Worksheets(1)
End Subシートの存在確認
存在しないシート名を指定するとエラーになります。On Error Resume Nextで一時的にエラーを無視し、変数に代入できたかどうかで判定するのが定番です。
Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(sheetName)
On Error GoTo 0
SheetExists = Not ws Is Nothing
End Functionシートを追加・削除する
シートを削除するとき、Excelは確認ダイアログを出します。マクロの中で完結させたい場合はApplication.DisplayAlertsを一時的にFalseにします。
Sub AddAndDeleteSheet()
Dim ws As Worksheet
' 末尾に追加
Set ws = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
ws.Name = "作業用"
' 確認ダイアログを出さずに削除
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("作業用").Delete
Application.DisplayAlerts = True
End Sub地味に見落としがちですが、DisplayAlertsをTrueに戻し忘れると、以降の処理で別の確認ダイアログまで無言でスキップされてしまいます。必ずセットで書いてください。
アクティブシートを切り替える
ユーザーの画面上で見せたいシートを切り替えるときはActivateを使います。裏側でデータを処理するだけなら、アクティブ化せずシートを直接指定して操作するほうが速く、画面のちらつきも防げます。
Sub ActivateSheet()
ThisWorkbook.Worksheets("集計").Activate
End SubProtect/Unprotectでシートを保護する
自分の作業用マクロでは保護をかけることはあまりありません。ただ、お客様に配布するツールでは「入力欄以外を誤って書き換えられたくない」という要望で保護をかけることが多いです。
シート全体を保護する
Sub ProtectWholeSheet()
ThisWorkbook.Worksheets("集計").Protect Password:="pass1234"
End Sub
Sub UnprotectWholeSheet()
ThisWorkbook.Worksheets("集計").Unprotect Password:="pass1234"
End Sub特定範囲だけ編集可能にする
セルには初期状態でLockedプロパティがTrueに設定されています。保護をかけると全セルがロックされる仕組みなので、入力してほしい範囲だけ先にロックを外しておきます。
Sub ProtectExceptInputRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("入力フォーム")
' 全セルを一旦ロック状態にする
ws.Cells.Locked = True
' 入力してほしい範囲だけロック解除
ws.Range("C3:C10").Locked = False
ws.Protect Password:="pass1234"
End Sub新規シートのセルは初期状態でLockedがTrueなので、「入力範囲だけFalseにする」という引き算の発想で組むとミスが減ります。
VBAからは書き込みたいのに保護もしたい場合
保護をかけたシートに対して、マクロ側からセルを書き換えようとすると実行時エラー1004になります。都度Unprotect・Protectし直すのは面倒ですし、処理の途中で止まるとProtectし忘れるリスクもあります。
こういう場面では、UserInterfaceOnly:=Trueを付けて保護すると、ユーザーの手入力だけをブロックし、VBAからの書き込みは許可する状態にできます。
Sub ProtectButAllowVBA()
ThisWorkbook.Worksheets("集計").Protect _
Password:="pass1234", UserInterfaceOnly:=True
End Subただし、この設定はブックを閉じると引き継がれません。Workbook_Openイベントで毎回Protectし直す運用が必要です。知らないと「昨日まで書き込めていたのに今日はエラーになる」という現象に遭遇します。
パスワードは万能ではないと理解しておく
シート保護のパスワードは、あくまで「誤操作の防止」レベルの保護です。解析ツールで突破できることが知られているので、本当に見られたくないデータの保護には向きません。
お客様から「絶対に触られたくない」と言われた場合は、パスワード保護だけで完結させず、そもそも別ファイルに分離するなどの運用面での対策もあわせて提案するようにしています。
開いたら閉じる — プロセス残留を防ぐ
冒頭の話の通り、開いたブックを閉じ忘れると、画面上は見えないままExcelプロセスだけが残り続けます。開いて処理して閉じる、という一連の流れをワンセットで書くくせをつけておくと防げます。
Sub ProcessAndClose()
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Data\Book1.xlsx")
' ここで読み取り・書き込みなどの処理
Debug.Print wb.Worksheets(1).Range("A1").Value
wb.Close SaveChanges:=False
End Sub保存の有無を制御する引数や、Excelアプリケーション自体を終了するApplication.Quit、それでもプロセスが残ってしまう場合の対処については、別記事でCloseまわりだけを掘り下げています。
よくあるエラーと対処
「別のプロセスで使用されているため、アクセスできません」
同名ブックがすでに開かれているか、他のユーザーが同じファイルを開いている状態です。「存在確認と多重オープンチェックをセットで行う」で紹介したチェックを入れておくと、少なくとも自分のマクロ内での多重オープンは防げます。
保護されたシートで実行時エラー1004が出る
保護中のシートにVBAから書き込もうとしたときに起きます。一時的にUnprotectしてから処理する方法もありますが、頻繁に書き込むならUserInterfaceOnly:=Trueでの保護に切り替えたほうが処理がシンプルになります。
シートが見つからないという実行時エラー9
シート名の全角/半角の違いや、末尾の空白によるタイプミスが原因のことがほとんどです。「シートの存在確認」で紹介した関数を先頭に挟んでおくと、エラーメッセージだけで済んで原因調査がしやすくなります。
まとめ
ブック・シート操作は「開く」「操作する」「保護する」「閉じる」の4フェーズで考えると迷いません。特に保護は、UserInterfaceOnly:=Trueを知っているかどうかで実装の手間がかなり変わります。
そして最後の「閉じる」を忘れないこと。開いたら閉じる、それだけのことですが、タスクマネージャーにExcel.exeが積み上がっていくのを見て初めて気づく人は、僕以外にもきっといるはずです。
コメント