セルの削除 – ExcelVBAの散策とruby

2016/03/21

参考サイト：　 Excel VBA 入門講座 セルの削除

《目次》

• 1.　概要
• 2.　マクロを登録するスクリプト
• 3.　win32oleのスクリプト(定義済み定数の読み込み方法を含む)
• 4.　exlapのスクリプト

1.　概要

　セルを削除するには Deleteメソッドを使います。

　Range("A2").EntireRow.Delete とすれば第２行目全体を削除します。

　Range("C1").EntireColumn.Delete とすれば第３列目全体の削除です。

　では、A1セル１個だけを削除した時はどうなるでしょうか。これは少々ややこしいです。

　３×３の９個のセルを考えます。セル番地でいうと A1:C3 です。各セルには「１番」　「２番」などを書き込んでおきます。電話機のプッシュフォンの数時ボタンと同じ並びです。

　今、「１番」を削除したとします。その時の挙動がどうなるかですが、２通り考えられます。

○　下に位置する「４番」と「７番」が上に繰り上がるパターン

○　右側にある「２番」と「３番」が左にずれるパターン

　Range("A1").Delete とすると、４番・７番が上に繰り上がります。

　Range("A1").Delete xlShiftUp としても同じように４番・７番が繰り上がります。

　Range("A1").Delete xlShiftToLeft とすれば、２番・３番が左にずれてきます。

　Deleteメソッドのパラメータに指定されている xlShiftToLeft(整数値 -4159), xlShiftUp(整数値 -4162)は、あらかじめ整数の値がセットされている定数です。

　このどちらかを指定することにより、削除後のずれ方を明確に指示することができます。

　しかし、わざわざ長い定数名を書くのは面倒です。そこで、定数を指定しない場合の挙動をもう少し見てみましょう。

　定数の指定を省略した時の挙動には次のルールがあります。仮に、各々のセルが正方形であるとイメージしてお読み下さい。

• 削除範囲が縦長の場合(A1:A2など)：　右側のものが左にずれる。
• 削除範囲が横長の場合(A1:B1など)：　下にあるものが上に繰り上がる。
• 削除範囲が正方形の場合(A1:B2など)：　下にあるものが上に繰り上がる。

　１番目と２番目のルールは、直感に合致するのではないかと思います。

　３番目のルールは覚えておくしかないでしょうか。

　削除するセルが１個だけのケースは、３番目の正方形の場合に該当します。

目次に戻る

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

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

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

　３×３のデータを書き込んでから、セルの削除を行うというパターンを４通り実行します。

　最初は A1:C3、次が A5:C7、それから A9:C11、そして A13:C15の領域に３×３のデータを書き込みます。

　WriteData() というマクロは３×３のデータをセルに書き込むものです。パラメータで渡された数値を行番号とみなして、その行以降に３×３のデータを書き込みます。

　Macro1というマクロの中で、まずはWriteDataを呼び出すてデータを書き込み、それからセルの削除を行っています。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Sub Macro1()
     Call WriteData(1)
     Range("A1").Delete  ' １個だけ削除：上に繰り上がり
     Call WriteData(5)
     Range("A5").Delete xlShiftToLeft  ' １個だけ削除：左シフトを指示
    Call WriteData(9)
    Range("A9:A10").Delete  ' 縦長を削除：左にシフト
    Call WriteData(13)
    Range("A13:B13").Delete  ' 横長を削除：上に繰り上がり
End Sub

Sub WriteData(Ri As Integer)
    Range(Cells(Ri,1), Cells(Ri,3)).Value = Array("１番", "２番", "３番")
    Range(Cells(Ri+1,1), Cells(Ri+1,3)).Value = Array("４番", "５番", "６番")
    Range(Cells(Ri+2,1), Cells(Ri+2,3)).Value = Array("７番", "８番", "９番")
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のスクリプトを掲げます。

　セルを削除するための Deleteメソッドに与えるパラメータ xlShiftToLeft は、予め定義されている定数ですが、これを使えるようにするには一定の手順が必要です。VBAの場合のように、いきなり使い始めることはできません。

　空のモジュール XlConstant を設けておいて、WIN32OLE.const_load() を呼び出します。すると、定義済みの定数を使えるようになります。

　XlConstant というモジュールの名前は任意です。好きな名前を使ってかまいません。

　ともあれスクリプトを記します。

 # encoding: Windows-31J
 require "win32ole"
 
 def fullpath(filename)
     fso = WIN32OLE.new("Scripting.FileSystemObject")
     return fso.GetAbsolutePathName(filename)
 end
 
 module XlConstant
end
include XlConstant

ary = [%W(１番 ２番 ３番), %w(４番 ５番 ６番), %w(７番 ８番 ９番)]
File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
app = WIN32OLE.new("Excel.Application")
WIN32OLE.const_load(app, XlConstant)
wb = app.Workbooks.Add()
ws = wb.Worksheets("Sheet1")
ws.Range("A1:C3").Value = ary
ws.Range("A1").Delete  # １個だけ削除：上に繰り上がり
ws.Range("A5:C7").Value = ary
ws.Range("A5").Delete(XlShiftToLeft)  # １個だけ削除：左シフトを指示
ws.Range("A9:C11").Value = ary
ws.Range("A9:A10").Delete  # 縦長を削除：左にシフト
ws.Range("A13:C15").Value = ary
ws.Range("A13:B13").Delete  # 横長を削除：上に繰り上がり
wb.SaveAs(fullpath("Book1"), XlWorkbookNormal)
app.Quit

　定義済みの定数を使えるようにする手順だけを抜き出すと下のとおり。

  module XlConstant
  end
  include XlConstant
  app = WIN32OLE.new("Excel.Application")
  WIN32OLE.const_load(app, XlConstant)

　include XlConstant は必須ではありませんが、これがないと、rubyスクリプト内で定義済み定数を記述するとき、XlConstant::XlShiftToLeft と書かなければなりません。単に XlShiftToLeft と書いただけではエラーになります。

　なお、VBAでは xlShiftToLeft と先頭文字が小文字の ‘x’ ですが、rubyでは大文字の ‘X’ にします。rubyでは、定数が大文字のアルファベットで始まるというルールがあるためです。

　３×３のデータは、配列の形で変数aryに代入しています。２次元配列です。

　そのセルへの書き込みは、ws.Range("A1:C3").Value = ary のように行っています。

目次に戻る

4.　exlapのスクリプト

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

　exlapでは Exlap.new が実行されると、定義済みの定数を使えるようになります。WIN32OLE.const_load() を呼び出さなくても大丈夫です。

 # encoding: Windows-31J
 require "./exlap"
 
 ary = [%W(１番 ２番 ３番), %w(４番 ５番 ６番), %w(７番 ８番 ９番)]
 File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
 Exlap.new("Book1.xls") do |wb|
   ws = wb.ss("Sheet1")
   ws.from_a("A1", ary)
   ws.Range("A1").Delete  # １個だけ削除：上に繰り上がり
  ws.from_a("A5", ary)
  ws.Range("A5").Delete(XlShiftToLeft)  # １個だけ削除：左シフトを指示
  ws.from_a("A9", ary)
  ws.Range("A9:A10").Delete  # 縦長を削除：左にシフト
  ws.from_a("A13", ary)
  ws.Range("A13:B13").Delete  # 横長を削除：上に繰り上がり
  wb.save
end

　３×３のデータは、２次元配列の形で変数aryに代入していますが、それをセルに書き込む時に ws.from_a("A1", ary) のように from_a() を用いています。

　from_aは、exlapで独自に定義されているメソッドです。第１引数は書き込みの開始番地(セルの番地)、第２引数は書き込むデータ(配列)です。

　開始番地を指定すればよく、A1:C3のようにセル範囲を指定しなくてもいいというのが特徴です。書き込みのセル範囲は、データである配列の大きさから判断されます。