Addメソッドについて考えた

コレクションのAddメソッドの返り値

Addメソッドの返り値を追加したオブジェクトにするというアイディアを考えたやつは天才

Excelで明細みたいなのを作っていると、それぞれのシートなりブックなりをだいたいはPDF化したり、プリントアウトしたり、という使い方になるんだが、時たま「後で修正する必要があるかも知れんなー」ということで、Excelファイルとして出力したいというときがある。

この間、「一つ一つをExcelファイルにしておきたいなー」と思ってやってみたときにふと気づいたのが標題に書いたAddメソッドの返り値を追加したオブジェクトにするというアイディアを考えたやつは天才ということだった。

シートを新しいブックにコピーして保存するマクロ

仕様は次の通り。

  1. アクティブシートを新しいブックにコピーする
  2. 新しくできたブックから、余分なシートを削除する
  3. コピーしたシートのA1セルの値をファイル名として保存する

とまあ、こんな設定。

リスト1
Public Sub copySheetAndCreateNewExcelFile()
  Dim Sh As Worksheet
  Set Sh = ActiveSheet
  Dim newWb As Workbook
  Dim str As String
  str = ThisWorkbook.Path & "\それぞれのExcelファイル\"    '……(1)'"
  str = str & Sh.Range("A1").Value & ".xlsx"
  Set newWb = Workbooks.Add    '……(2)'
  Sh.Copy before:=newWb.Worksheets(1)    '……(3)'
  Application.DisplayAlerts = False    '……(4)'
  Dim i As Integer
  With newWb    '……(5)'
    For i = .Worksheets.Count To 2 Step -1
      .Worksheets(i).Delete
    Next
  End With
  newWb.SaveAs fileName:=str    '……(6)'
  newWb.Close False    '……(7)'
  Application.DisplayAlerts = True    '……(8)'
End Sub

(1)からの2行、

str = ThisWorkbook.Path & "\それぞれのExcelファイル\" '"
str = str & Sh.Range("A1").Value & ".xlsx"

では、新しくできるブックのファイル名を組み立てて変数strに格納している。

単に、後の記述を簡単にするためだけのこと。

(2)がミソ。

「Addメソッド」という名前からは、

コレクションにオブジェクトを加える

というイメージしか湧いてこない。

だから、私のような凡人がAddメソッドを作っていたとしたら

絶対にvoidメソッドにしてしまう

と思う。

「新しくできたオブジェクトを返り値にしよう」などという発想は出てこないと思うのだ。

Addメソッドの返り値が新規に生成されたオブジェクトである、というたったこれだけのことで(2)のように

Set newWb = Workbooks.Add

たったこれだけの記述で新しいオブジェクトを捕まえることができる。これは本当に素晴らしいアイディアだと思う。

言いたいことは言ったので、これで終わってもいいんだが、せっかくなので残りの部分も説明しておこう。

(3)の

Sh.Copy before:=newWb.Worksheets(1)

では、WorksheetオブジェクトのCopyメソッドを用いてシートを新しくできたブックにコピーしている。

引数「Before」に新しいブックの一番左のシートを指定しているので、コピーしたシートは新しいブックの一番左端に入ることになる。

この後、不要なシートを削除していくので、(4)の

Application.DisplayAlerts = False

で、一旦警告メッセージの表示を止める。

(5)からの5行、

With newWb
  For i = .Worksheets.Count To 2 Step -1
    .Worksheets(i).Delete
  Next
End With

で不要なシートを消す。

Excel2010までと2013以降では、デフォルトのシート数が異なるので、Forループの開始値はこんな書き方になる。

コピーしたシートが先頭に挿入されているので、Forループの開始値は、2010までなら4、2013以降なら2ということになる。

Forループの開始値と終了値をケツからさかのぼるような形にしているのがポイント。こうしないと途中でエラーになる(理由は自分で考えよう)。

まあ、

削除するときはケツから!

というのは基本ですな。

あとは、(6)の

newWb.SaveAs fileName:=str

で名前をつけて保存し、

(7)の

newWb.Close False

で新しくできたブックを閉じたらおしまい。

(8)の

Application.DisplayAlerts = True

で警告メッセージの表示を復活させるのをお忘れなく、といったところか。

おわりに

今まで何の気なしに使っていたメソッドだけれど、やはりプロが作ったものはよく考えられている。

自作クラスのメソッドにも応用できるなあと思った。

@akashi_keirin on Twitter