セルの指定とプロパティセット – ExcelVBAの散策とruby

2016/03/21

参考サイト：　 Excel VBA 入門講座 セルの指定とプロパティセット

《目次》

1.　概要

　主な内容は次のとおり。

　　　　

特定のセルを指定するためのRangeとCells

　A1セルを指定するのに次の二つの方法があります。セルに「test」と書き込む例で示します。

  Range("A1").Value = "test"
  Cells(1,1).Value = "test"

　Cellsでは行番号と列番号を指定します。

　B1なら Cells(1,2) となり、A4なら Cells(4,1) です。

　　　

セル指定の前提となるアクティブシート

　セルはワークシートに所属します。なので、セルを指定する場合は、どのワークシートが注目のワークシートになっているかが大事です。

　注目のワークシートのことをアクティブシートといいます。

　新規のワークブックを開いた時は、三つのワークシートが用意され、そのうちの第１ワークシートがアクティブシートになっています。

　なので、VBAのマクロでいきなり Range("A1") と書けば、そのセルは第１ワークシートのA1セルということになります。

　第２ワークシートをアクティブシートにしたい場合は下のように書きます。

  Worksheets("Sheet2").Activate

　上のようにした後で Range("A1") とすれば、第２ワークシートのA1セルということになります。

　ひとまとまりのマクロの形で書くと次のとおり。

Sub Macro1()
    Worksheets("Sheet2").Activate
    Range("B1").Value = "B1・B1"
    Cells(4,1).Value = "A4・A4"
End Sub

　　　　

セルの高さと幅を設定

　セルにはValue以外にもいろいろなプロパティがあります。

セルの高さは RowHeightプロパティ、幅は ColumnWidthプロパティで設定します。たとえば下のとおり。

  Range("A1").Value = "EXCEL VBA"
  Range("A1").RowHeight = 20
  Range("A1").ColumnWidth = 20

　セルの高さのデフォルト値は 13.5 です。この数値の単位はポイント。

　１ポイント ＝ 1／72インチ ＝ 約0.3528ミリ (１インチ ＝ 約25.4ミリ)

　13.5ポイント ＝ 約0.3528ミリ×13.5 ＝ 約4.7628ミリ

　一方、セルの幅のデフォルト値は 8.38 です。これは、標準フォントの半角文字で 8.38文字分ということらしいです。

　ちなみに、Excelの標準フォントのサイズは 11.0ポイント。

　　　　

同一オブジェクトに複数のプロパティを設定する際のWithステートメント

　同じセルオブジェクトに Value, RowHeight, ColumnWidth の三つのプロパティを設定する場合、同一オブジェクトの記述を何度もするのは面倒です。

  Range("A1").Value = "EXCEL VBA"
  Range("A1").RowHeight = 20
  Range("A1").ColumnWidth = 20

　上は Range("A1") が３回出てきます。

　このような時にWithステートメントを用いると簡潔に書けます。次のように書きます。

With Range("A1")
    .Value = "EXCEL VBA"
    .RowHeight = 20
    .ColumnWidth = 20
End With

　Withステートメントの中に、またWithステートメントを入れ込むこともできます。たとえば次のように書きます。

With Worksheets("Sheet2")
    .Activate
    With Range("A1")
        .Value = "EXCEL VBA"
        .RowHeight = 20
        .ColumnWidth = 20
    End With
End With

2.　VBAマクロの登録

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

　マクロ部分だけを取り上げると下のとおり。

Sub Macro1()
    Worksheets("Sheet1").Activate
    With Range("A1")
        .Value = "Sheet01 apple"
        .RowHeight = 20
        .ColumnWidth = 20
    End With

    Worksheets("Sheet2").Activate
    With Cells(1,1)
        .Value = "Sheet02 banana"
        .RowHeight = 20
        .ColumnWidth = 20
    End With

    Worksheets("Sheet1").Activate
    Range("A1").Select
End Sub

　上のマクロをBook1.xlsに登録するためのrubyスクリプトは下のようになります。

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

 1# encoding: Windows-31J
 2require "./exlap"
 3
 4macro_str = <<'EOS'
 5Sub Macro1()
 6    Worksheets("Sheet1").Activate
 7    With Range("A1")
 8        .Value = "Sheet01 apple"
 9        .RowHeight = 20
10        .ColumnWidth = 20
11    End With
12
13    Worksheets("Sheet2").Activate
14    With Cells(1,1)
15        .Value = "Sheet02 banana"
16        .RowHeight = 20
17        .ColumnWidth = 20
18    End With
19
20    Worksheets("Sheet1").Activate
21    Range("A1").Select
22End Sub
23EOS
24
25macro_name = "Macro1"
26File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
27Exlap.new("Book1.xls") do |wb|
28  wb.macro_add(macro_str)
29  wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
30  wb.save
31end

3.　win32oleおよびexlapのスクリプト

　私の知る範囲では、残念ながらVBAのWithステートメントに該当する書き方がrubyの側には見当たりません。

　オブジェクトを短い変数名に代入して、それを反復して用いるという方法しか思いつきません。

　先のマクロと同じ動作をするrubyの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.Activate
14c = ws.Range("A1")
15c.Value = "Sheet01 apple"
16c.RowHeight = 20
17c.ColumnWidth = 20
18
19ws = wb.Worksheets("Sheet2")
20ws.Activate
21c = ws.Cells(1,1)
22c.Value = "Sheet02 banana"
23c.RowHeight = 20
24c.ColumnWidth = 20
25
26ws = wb.Worksheets("Sheet1")
27ws.Activate
28ws.Range("A1").Select
29wb.SaveAs(fullpath("Book1"), -4143)
30app.Quit

　　　　

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

　win32oleのスクリプトと同じ記述になってもつまらないので、括弧を用いて行数が少なくなるようにしてみます。括弧の中が優先して実行されることを利用します。

 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    (ws1 = wb.ss("Sheet1")).Activate
 7    (c = ws1.Range("A1")).Value = "Sheet01 apple"
 8    c.RowHeight = 20
 9    c.ColumnWidth = 20
10
11    (ws2 = wb.ss("Sheet2")).Activate
12    (c = ws2.Cells(1,1)).Value = "Sheet02 banana"
13    c.RowHeight = 20
14    c.ColumnWidth = 20
15
16    ws1.Activate
17    ws1.Range("A1").Select
18    wb.save
19end

　(ws1 = wb.ss("Sheet1")).Activate というのは、括弧の中(変数ws1にワークシートオブジェクトを代入する)をまず実行し、その上で ws1.Activate を実行することを意味しています。

　スクリプトの行数を減らすことにはなりますが、読みにくい感じになってしまうので素直に２行で書く方がいいかもしれません。

4.　アクティブシートについての補足

　ExcelをGUI操作している場合、第３ワークシートのセルに何か書き込もうと思ったら、まず第３ワークシートをアクティブシートにしなければなりません。

　しかし、マクロでセルに書き込みをする時は、必ずしもアクティブシートにする必要はありません。アクティブシートを第１ワークシートにしたままで、第３ワークシートのセルに書き込むことができます。

　たとえば、下のマクロを実行してもアクティブシートは変更されません。

Sub Macro1()
    Worksheets("Sheet3").Range("A1").Value = "test"
End Sub

　上は第３ワークシートのA1セルに書き込みをするものですが、第３ワークシートをアクティブにしているわけではありません。

　では、セルに書き込みをするだけでなく、その高さと幅を設定したい時はどうしたらいいでしょうか。Worksheets("Sheet3") を何度も繰り返して書きたくない場合です。

　当然ながら Withステートメントを用います。該当箇所を示すと下のとおり。

With Worksheets("Sheet3")
    With .Range("A1")
        .Value = "Sheet03 tomato"
        .RowHeight = 20
        .ColumnWidth = 20
    End With
End With

　ここでの要点は、Range("A1") の前に ‘.’ が置かれていることです。

　このピリオドがないと、第３ワークシートにではなく、アクティブシートのA1セルに書き込みが行われます。

　このピリオドは、直前の With の後に書かれているオブジェクトに所属していることを意味済ます。

　ちょっとややこしいですが、ピリオド１個で違ってしまうので参考まで記しました。

　　　　

　先に掲げたrubyスクリプトでは、VBAのマクロと同じ処理にするため Activate を記述しました。

　でも、全自動一括処理ではアクティブシートをわざわざ切り替える意味が薄いので、スクリプト中に出てくる .Activate という記述は消去しても差し支えないと思います。

前のページ：オブジェクト、プロパティ、メソッド

次のページ：セル範囲の指定

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