ワークシートの削除 – ExcelVBAの散策とruby

2016/04/09

参考サイト：　 Excel VBA 入門講座 ワークシートの削除

《目次》

• 1.　概要
• 2.　マクロを登録するスクリプト
• 3.　win32oleのスクリプト
• 4.　exlapのスクリプト
• 5.　DisplayAlerts関連の補足

1.　概要

　ワークシートの削除は、Deleteメソッドで行います。

　ActiveSheet.Delete とすればアクティブシートが削除されます。

　Worksheets("Sheet3").Delete とすれば Sheet3 が削除されます。

　ただし、削除前に次ぎのような警告メッセージが出ます。

選択したシートに、データが存在する可能性があります。データを完全に削除するには、［削除］をクリックしてください。

　メッセージと一緒に ［削除］, ［キャンセル］ の二つのボタンが出て、［削除］をクリックすると、そこで初めてワークシートの削除が実行されます。

　この警告メッセージを出さずに、直ちに削除が行われるようにするには、下のようにして警告メッセージの出力を抑制します。

  Application.DisplayAlerts = False
  ActiveSheet.Delete
  Application.DisplayAlerts = True

　DisplayAlertsプロパティにFalseをセットすると警告メッセージの出力が抑制され、Trueをセットすれば再び警告メッセージが出るようになります。

　それから、あまり遭遇しないかもしれませんが、ワークブックにワークシートが１枚しかない状態で、そのワークシートを削除しようとするとエラーメッセージが出ます。ワークブックには、少なくとも１枚のワークシートが所属していなければならないためです。

　この時のメッセージは、警告メッセージではなくエラーメッセージです。なので、DisplayAlertsプロパティをFalseにしていてもエラーメッセージは出ます。

　このエラーに遭遇しないようにするには、Worksheets.Count でワークシートの枚数をみて、それが２以上であるとの条件の下で ActiveSheet.Delete などと実行してやればいいことになります。

　ちょっと先走りですが、エラーを回避するためのコードを掲げてみます。条件分岐のIf文を用います。

If Worksheets.Count >= 2 Then
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End If

　上のコードだと、ワークシートが残り１枚の時に何もしません。ユーザーからすれば「削除しようとしてもできない」と戸惑うかもしれません。Else文を追加して、「残り１枚なので削除できません」といったメッセージを出す方が新設でしょうか。

　Deleteメソッドの戻り値は True, False のどちらかです。削除に成功した時は True を返します。

目次に戻る

2.　マクロを登録するスクリプト

　「概要」で述べてきた事柄をVBAマクロとして登録するためのスクリプトを掲げます。

　これまでと同様に、マクロには Control + j というショートカットキーを割り当てます。

　アクティブシートを削除し、次に Sheet3 を削除します。

　最後に、アクティブシートのA1セルにそのワークシート名を書き込みます。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Sub Macro1()
     Application.DisplayAlerts = False
     ActiveSheet.Delete
     Worksheets("Sheet3").Delete
     Application.DisplayAlerts = True
    Range("A1").Value = ActiveSheet.Name
End Sub
EOS

macro_name = "Macro1"
File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
Exlap.new("Book1.xls") do |wb|
  wb.macro_add(macro_str)
  wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
  wb.save
end

目次に戻る

3.　win32oleのスクリプト

　前述のマクロと同じ処理を行うwin32oleのスクリプトは下のようになります。

 # encoding: Windows-31J
 require "win32ole"
 
 def fullpath(filename)
     fso = WIN32OLE.new("Scripting.FileSystemObject")
     return fso.GetAbsolutePathName(filename)
 end
 
 File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
app = WIN32OLE.new("Excel.Application")
wb = app.Workbooks.Add()
app.DisplayAlerts = false
wb.ActiveSheet.Delete
wb.Worksheets("Sheet3").Delete
app.DisplayAlerts = true
ws = wb.ActiveSheet
ws.Range("A1").Value = ws.Name
wb.SaveAs(fullpath("Book1"), -4143)
app.Quit

目次に戻る

4.　exlapのスクリプト

　同じ処理を行うexlapのスクリプトを掲げます。

　exlapで独自に定義されている wb.delete_sheet(……) を用います。指定されたワークシート１枚を削除するメソッドです。

 # encoding: Windows-31J
 require "./exlap"
 
 File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
 Exlap.new("Book1.xls") do |wb|
     wb.delete_sheet(wb.ActiveSheet)
     wb.delete_sheet("Sheet3")
     ws = wb.ActiveSheet
     ws.Range("A1").Value = ws.Name
    wb.save
end

　wb.delete_sheet(……) は、引数としてワークシートオブジェクトを与えると、そのワークシートを削除します。

　wb.delete_sheet("Sheet3") のように、削除したいワークシートの名前を与えることもできます。

　delete_sheetは、削除処理の前後で DisplayAlerts を調整して、警告メッセージが出ないようにします。なので、DisplayAlerts に関する記述を書かなくても大丈夫です。

目次に戻る

5.　DisplayAlerts関連の補足

　これまでのサンプルでは、警告メッセージが出ないようにするため、DisplayAlertsプロパティにFalseをセットしました。そして、ワークシートを削除後、今度は True をセットしました。

　しかし、厳密にいうと、その処理は妥当ではありません。

　もしマクロ記述の別の箇所で、DisplayAlertsを意図してFalseにしていたとしたらどうでしょうか。それを単純にTrueにしてしまうと、意図に反することになります。

　そうしたトラブルを避けるには、DisplayAlertsの値を一時的に変数に保存し、ワークシートの削除が済んだところでDisplayAlertsの値を元の値に復元します。こうした「一時的な待避と復元」は、プログラミングではしばしば必要になります。

　当シリーズでは、まだ変数について触れられていないので先走りですが、参考まで「一時的な待避と復元」の例を書いてみます。

Sub Macro1()
    Dim DVal As Boolean
    DVal = Application.DisplayAlerts
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = DVal
End Sub

　DisplayAlertsの値を変数 DVal に待避しています。

　この変数の型は Boolean です。これは、True, False に対応する型です。

　もし文字を代入するなら String、整数値を代入するなら Integer とか Long にします。

　VBAでは変数を使う前にその型を宣言します。rubyとかperlなどのスクリプト系プログラミング言語では宣言なしで変数をつかえるので、それに慣れていると面倒ですが、間違った変数の使い方をしてしまった時にデバッグしやすくなるといったメリットがあります。

　なお、exlapの wb.delete_sheet(……) は、DisplayAlertsの値を「一時的な待避と復元」で調整しています。