Addメソッドについて考えた
コレクションのAddメソッドの返り値
Addメソッドの返り値を追加したオブジェクトにするというアイディアを考えたやつは天才
Excelで明細みたいなのを作っていると、それぞれのシートなりブックなりをだいたいはPDF化したり、プリントアウトしたり、という使い方になるんだが、時たま「後で修正する必要があるかも知れんなー」ということで、Excelファイルとして出力したいというときがある。
この間、「一つ一つをExcelファイルにしておきたいなー」と思ってやってみたときにふと気づいたのが標題に書いたAddメソッドの返り値を追加したオブジェクトにするというアイディアを考えたやつは天才ということだった。
シートを新しいブックにコピーして保存するマクロ
仕様は次の通り。
- アクティブシートを新しいブックにコピーする
- 新しくできたブックから、余分なシートを削除する
- コピーしたシートの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
で警告メッセージの表示を復活させるのをお忘れなく、といったところか。
おわりに
今まで何の気なしに使っていたメソッドだけれど、やはりプロが作ったものはよく考えられている。
自作クラスのメソッドにも応用できるなあと思った。