セル範囲の指定 – ExcelVBAの散策とruby

2016/03/21

参考サイト：　 Excel VBA 入門講座 セル範囲の指定

《目次》

1.　概要

　セル範囲(一連の複数のセル)を指定する方法を取り上げます。

　下は、いずれも同じ７行・５列の範囲(7×5=35個のセル)を指定する例です。

　35個のセルのどれにも「Excel VBA」が書き込まれます。

　実践でセル範囲を使うケースはいろいろあると思いますが、たとえば、範囲のセルを同じフォントサイズにするとか、該当のセル全部の内容を消去するとか、あるいはセル結合するといった場合でしょうか。

　例を上げると下のとおり。

  Range("A1:E1").Font.Size = 14
  Range(Cells(1,1), Cells(2,2)).Clear
  Range("A1", "D1").MergeCells = True

　　　　

　セル範囲に同じ文字ではなく、異なる文字を書き込む場合は配列(Array)というのを使います。

　同一の行のセル範囲に書き込むなら下のようにします。

  Range("A1:C1").Value = Array("東京都", "愛知県", "大阪府")

　複数の行にわたるセルへの書き込みを一括して行うのは、VBAでは簡単でなさそうなのでここでは省略します。

目次に戻る

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

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

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

 1# encoding: Windows-31J
 2require "./exlap"
 3
 4macro_str = <<'EOS'
 5Sub Macro1()
 6    Range("A1:E1").Value = "test"
 7    Range("A1:E1").Font.Size = 14
 8    Range(Cells(2,1), Cells(3,2)).Value = "VBA"
 9    Range(Cells(3,1), Cells(3,2)).Clear
10    Range("A4").Value = "Excel"
11    Range("A4", "B4").MergeCells = True
12    Range("A5:C5").Value = Array("青森", "岩手", "秋田")
13End Sub
14EOS
15
16macro_name = "Macro1"
17File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
18Exlap.new("Book1.xls") do |wb|
19  wb.macro_add(macro_str)
20  wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
21  wb.save
22end

　今回のマクロにはワークシートを指定する記述がありません。なので、マクロを実行するとアクティブシートのセルに対して処理を行うことになります。

　第１ワークシートにおいてマクロを実行すれば、そのセルに書き込みなどの処理が行われます。第２ワークシートをアクティブシートにした状態でマクロを実行すれば、そこのセルが変化します。

目次に戻る

3.　win32oleのスクリプト

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

 1# encoding: Windows-31J
 2require "win32ole"
 3
 4def fullpath(filename)
 5    fso = WIN32OLE.new("Scripting.FileSystemObject")
 6    return fso.GetAbsolutePathName(filename)
 7end
 8
 9File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
10app = WIN32OLE.new("Excel.Application")
11wb = app.Workbooks.Add()
12ws = wb.Worksheets("Sheet1")
13ws.Range("A1:E1").Value = "test"
14ws.Range("A1:E1").Font.Size = 14
15ws.Range(ws.Cells(2,1), ws.Cells(3,2)).Value = "VBA"
16ws.Range(ws.Cells(3,1), ws.Cells(3,2)).Clear
17ws.Range("A4").Value = "Excel"
18ws.Range("A4", "B4").MergeCells = true
19ws.Range("A5:C5").Value = "青森", "岩手", "秋田"
20wb.SaveAs(fullpath("Book1"), -4143)
21app.Quit

　VBAのマクロと対比したときの相違点を挙げると次のとおり。

　上の相違点に関連して少し補足します。

　真偽を示す True, False を、rubyでは先頭文字を小文字にして true, false と書きます。

　あと、VBAでの Nothing が、rubyでは nil です。

　　　　

　rubyにおける配列は、通常 ["青森", "岩手", "秋田"] のように大括弧で囲んで書きますが、一種の省略記法として、大括弧を省略し "青森", "岩手", "秋田" と書くことができます。

　引用符を何度も書くのが面倒なら、%w(青森 岩手 秋田) と記述することも可能です。コンマも書きません。

目次に戻る

4.　exlapのスクリプト

　exlapのスクリプトとwin32oleのスクリプトとの違いは下の点です。

　ws.Range(ws.Cells(2,1), ws.Cells(3,2)) を次のように書くことができます。

　ws.rr(2,1, 3,2)

　rr() はexlapで定義されている独自のメソッドです。セル範囲の左上の番地と、右下の番地を指定することで領域を設定します。

　以下、exlapのスクリプトです。

 1# encoding: Windows-31J
 2require "./exlap"
 3
 4File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
 5Exlap.new("Book1.xls") do |wb|
 6  ws = wb.ss("Sheet1")
 7  ws.Range("A1:E1").Value = "test"
 8  ws.Range("A1:E1").Font.Size = 14
 9  ws.rr(2,1, 3,2).Value = "VBA"
10  ws.rr(3,1, 3,2).Clear
11  ws.Range("A4").Value = "Excel"
12  ws.Range("A4", "B4").MergeCells = true
13  ws.Range("A5:C5").Value = %w(青森 岩手 秋田)
14  wb.save
15end

目次に戻る

5.　補足(CurrentRegion, UsedRange)

　ちょっと先走りですが、セル範囲を扱うのに私が便利だと感じているプロパティを二つあげておきます。

CurrentRegion
空白行と空白列を含まない一連のセル。きれいな長方形・正方形にならない場合は該当の最大範囲(空白セルを含むこともある)。
UsedRange
何か書き込みのある領域の最大の範囲。つまり、使用されているセルの最も左上から右下までをカバーする範囲。使用されていない上の領域と下の領域は含まれない。

　　　　

　これらの利用例をVBAのマクロの形で書くと次のとおり。

　　　　

　あるワークシートについて、使用されている最終番地のアドレスを見たい場合、UsedRange を用いると便利です。

　アクティブシートの最初と最後の番地を表示するVBAマクロは次のようになります。

Sub Macro1()
    MsgBox "最初と最後の番地：" & ActiveSheet.UsedRange.Address
End Sub

　上のマクロを実行すると、たとえば $b1:$D3 のような表示が出ます。この場合、最終番地は $D$3 です。

　$D$3 は、D3を絶対番地の方式で示したものです。

前のページ：セルの指定とプロパティセット

次のページ：行全体・列全体の指定

「ExcelVBAの散策とruby」トップページ