任意のワークシートを新規Excelブックとして出力するクラス
ワークシートを新規Excelブックとして出力するクラス
ワークシートを新規Excekブックとして出力する
名簿なんかの一覧表をマクロで自動作成して、相手方にPDFで提供すると、しばしば
加工したいからExcelのままでくれ！
と言われることがよくある。
大量（っつっても大した量でもないけど）の元データやマクロだらけのブックをそのまま送りつけるわけにも行かないので、必要なシートだけを新しいブックにコピペして提供、という形になる。
ただ、それも１つや２つなら手作業でポンとやっておしまい、なんだが、次から次へ、となるとさすがにめんどくさい。
で、やってみたのが
なんだが、今後こうした作業が頻発することが予測されるのでクラス化しといた。
任意のワークシートを新規Excelブックとして出力するクラス
リスト１ クラスモジュール ExcelFileCreator
Option Explicit Private Const NOT_INITIALIZED As Integer = 1 Private Const NOT_CREATED As Integer = 2 'Fields' Private targetSheet_ As Worksheet Private filePath_ As String Private newWorkbook_ As Workbook Private isInitialized_ As Boolean 'Accessor' Public Property Get newWorkbook() As Workbook Set newWorkbook = newWorkbook_ End Property 'Constructor' Public Sub init(ByVal Sh As Worksheet) '引数としてExcelファイル化したいWorksheetオブジェクトを受け取る' Set targetSheet_ = Sh isInitialized_ = True End Sub 'Methods' Public Sub createExcelFile(ByVal fullPath As String, _ Optional ByVal isOnlyToCreate As Boolean) '引数fullPathは、新たに作成するExcelファイルのフルパス' '新しいExcelファイルを作成するだけでよいなら、引数isOnlyToCreateをTrueにする' If isInitialized_ = False Then Call raiseError(NOT_INITIALIZED) filePath_ = fullPath Set newWorkbook_ = Workbooks.Add targetSheet_.Copy before:=newWorkbook_.Worksheets(1) Application.DisplayAlerts = False Dim i As Integer With newWorkbook_ For i = .Worksheets.Count To 2 Step -1 .Worksheets(i).Delete Next End With Application.DisplayAlerts = True If isOnlyToCreate = True Then Call closeCreatedFile End Sub Public Sub closeCreatedFile() If newWorkbook_ Is Nothing Then Call raiseError(NOT_CREATED) Application.DisplayAlerts = False newWorkbook_.SaveAs fileName:=filePath_ newWorkbook_.Close False Application.DisplayAlerts = True End Sub Private Sub raiseError(ByVal typeOfError As Integer) If typeOfError = NOT_INITIALIZED Then Err.Raise Number:=10001, _ Description:="インスタンス生成後にinitメソッドの使用が必要です。" End If If typeOfError = NOT_CREATED Then Err.Raise Number:=10002, _ Description:="Workbook作成前に使用できません。" End If End Sub
かなりタテ長になってしまったので、パーツごとに解説する。
目次
宣言セクション
Private Const NOT_INITIALIZED As Integer = 1 Private Const NOT_CREATED As Integer = 2
今のところ定数を２つ設定している。誤った使い方をした場合にエラーを起こすようにしているが、どのエラーを起こすのかを区別するためのもの。
フィールド
Private targetSheet_ As Worksheet Private filePath_ As String Private newWorkbook_ As Workbook Private isInitialized_ As Boolean
それぞれの変数は、
targetSheet_
Excelブック化する対象のワークシートを格納する。
filePath_
新規Excelブックのフルパスを格納する。
newWorkbook_
新規Excelブックを格納する。
isInitialized_
擬似コンストラクタinitメソッドが実行されたかどうかを格納する。initメソッド実行済みであればTrue。
アクセサ
Public Property Get newWorkbook() As Workbook Set newWorkbook = newWorkbook_ End Property
今のところ、外部に公開するプロパティとしては、新しく作成されたブックが格納されているnewWorkbook_フィールドだけで良いと思っている。必要があれば再検討する。
コンストラクタ
Public Sub init(ByVal Sh As Worksheet) Set targetSheet_ = Sh '……(1)' isInitialized_ = True '……(2)' End Sub
おなじみ、擬似コンストラクタ。引数としてExcelブック化したい対象のWorksheetオブジェクトを渡して実行する。
(1)の
Set targetSheet_ = Sh
で、引数として受け取ったWorksheetオブジェクトを変数targetSheet_に格納。
(2)の
isInitialized_ = True
で変数isInitialized_をTrueにして、initメソッド実行済みかどうか分かるようにしている。
createExcelFileメソッド
Public Sub createExcelFile(ByVal fullPath As String, _
Optional ByVal isOnlyToCreate As Boolean) '……(1)'
If isInitialized_ = False Then Call raiseError(NOT_INITIALIZED) '……(2)'
filePath_ = fullPath
Set newWorkbook_ = Workbooks.Add
targetSheet_.Copy before:=newWorkbook_.Worksheets(1)
Application.DisplayAlerts = False
Dim i As Integer
With newWorkbook_
For i = .Worksheets.Count To 2 Step -1
.Worksheets(i).Delete
Next
End With
Application.DisplayAlerts = True
If isOnlyToCreate = True Then Call closeCreatedFile '……(3)'
End Sub
(1)の
Public Sub createExcelFile(ByVal fullPath As String, _
Optional ByVal isOnlyToCreate As Boolean = True)
で、引数を２つ設定している。
第１引数fullPathは、新規に作成するExcelブックのフルパス。
第２引数isOnlyToCreateは、Trueにすると指定したシートをそのままExcelブック化する。シートに何らかの加工をしてからExcelブック化したいなら、この引数をFalseにしておく必要がある。
(2)の
If isInitialized_ = False Then Call raiseError(NOT_INITIALIZED)
では、このクラスのインスタンス生成後、initメソッドが実行されているかどうかを判定し、未実行ならraiseErrorメソッドを呼び出してエラーを起こさせるようにしている。
さて、このメソッドのメインの処理については、前に書いたのと全く同じなので、詳しいことは
をどうぞ。
(3)の
If isOnlyToCreate = True Then Call closeCreatedFile
では、第２引数の値によって、このまま新規ブックの保存・終了に進むのかどうかを分岐している。
isOnlyToCreateがTrueならば、後は新規ブックを保存して終了するだけなので、closeCreatedFileメソッドを呼び出して終了。Falseだったら一旦ここで処理を中断して標準モジュールのメインのコードに戻ることになる。
closeCreatedFileメソッド
Public Sub closeCreatedFile() If newWorkbook_ Is Nothing Then Call raiseError(NOT_CREATED) '……(1)' Application.DisplayAlerts = False newWorkbook_.SaveAs fileName:=filePath_ newWorkbook_.Close False Application.DisplayAlerts = True End Sub
こちらは、新しく作成されたExcelブックに新しいファイル名を付けて保存し、ファイルを閉じる、という処理をしているだけ。
ただ、そもそも新規のブックが作成されてもいないのに、このメソッドを先に実行されてしまったら困るので、(1)の
If newWorkbook_ Is Nothing Then Call raiseError(NOT_CREATED)
で、新規ブック未作成ならcloseCreatedFileメソッドを呼んでエラーを起こさせるようにした。
raiseErrorメソッド
Private Sub raiseError(ByVal typeOfError As Integer) '……(1)'
If typeOfError = NOT_INITIALIZED Then
Err.Raise Number:=10001, _
Description:="インスタンス生成後にinitメソッドの使用が必要です。"
End If
If typeOfError = NOT_CREATED Then
Err.Raise Number:=10002, _
Description:="Workbook作成前に使用できません。"
End If
End Sub
(1)の
Private Sub raiseError(ByVal typeOfError As Integer)
では引数typeOfErrorを設定している。
あとは、受け取ったtypeOfErrorの値に応じてErrオブジェクトのRaiseメソッドを用いてエラーを吐かせているだけ。
今後、新たなエラー原因が分かったら逐次追加していけば良い。
使用例 その１
こんなシートをアクティブにして次のリスト2-1のコードを実行してみる。
リスト2-1 標準モジュール
Public Sub createExcelFileTest1() Dim Sh As Worksheet Set Sh = ActiveSheet '……(1)' Dim str As String str = ThisWorkbook.Path & "\超人墓場\" '……(2)'" str = str & "都道府県別1.xlsx" Dim xlsxCreator As ExcelFileCreator '……(3)' Set xlsxCreator = New ExcelFileCreator '……(4)' xlsxCreator.init Sh '……(5)' xlsxCreator.createExcelFile str, True '……(6)' End With End Sub
(1)の
Set Sh = ActiveSheet
で、アクティブシートを一旦変数Shに格納。
(2)からの２行
str = ThisWorkbook.Path & "\超人墓場\" '" str = str & "都道府県別1.xlsx"
では、新しく作成するブックのフルパスを変数strに格納。２回に分けているのは、可読性とメンテナンス性のため。別に１回で書いてもいい。
(3)の
Dim xlsxCreator As ExcelFileCreator
で、ExcelFileCreatorクラスのインスタンス用の変数xlsxCreatorを準備し、
(4)の
Set xlsxCreator = New ExcelFileCreator
でExcelFileCreatorクラスのインスタンスを生成して変数xlsxCreatorに格納。
(5)の
xlsxCreator.init Sh
でExcelFileCreatorクラスのinitメソッドにSh（対象のWorksheetオブジェクト）を渡して実行。
(6)の
xlsxCreator.createExcelFile str, True
でExcelFileCreatorクラスのcreateExcelFileメソッドにstr（新規ブックのフルパス）を渡して実行。第２引数がTrueなので、指定したWorksheetオブジェクトに何も加工せずに保存・終了することになる。
実行結果
このように、「超人墓場」フォルダに「都道府県別1.xlsx」ができていて、開くと
この通り、シートがそのまま保存されている。
ただ、このままだと、いかにもＡ～Ｃ列が邪魔。かといって手動で削除するとなると果てしなくダルい。
そこで、新しいブックを作った後、Ａ～Ｃ列を削除した後で保存・終了する、という処理にしてみる。
使用例 その２
リスト2-1を次のように書き換える。
リスト2-2 標準モジュール
Public Sub createExcelFileTest2() Dim Sh As Worksheet Set Sh = ActiveSheet Dim str As String str = ThisWorkbook.Path & "\超人墓場\" '" str = str & "都道府県別2.xlsx" Dim xlsxCreator As ExcelFileCreator Set xlsxCreator = New ExcelFileCreator With xlsxCreator .init Sh .createExcelFile str, False '……(1)' .newWorkbook.Worksheets(1).Columns("A:C").Delete '……(2)' .closeCreatedFile '……(3)' End With End Sub
変わったのは、
With xlsxCreator
.init Sh
.createExcelFile str, False '……(1)'
.newWorkbook.Worksheets(1).Columns("A:C").Delete '……(2)'
.closeCreatedFile '……(3)'
End With
この部分だけ。
(1)でcreateExcelFileメソッドを呼び出すときの第２引数をFalseにし、
(2)でＡ～Ｃ列を削除してから、
(3)でcloseCreatedFileメソッドを呼び出して新規ブックを保存・終了している。
実行結果
このように、「超人墓場」フォルダに「都道府県別2.xlsx」ができていて、開くと
この通り、Ａ～Ｃ列だった部分が削除されてシートが保存されている。
おわりに
今回紹介した程度の処理なんて、フツーに考えたらSubとかFunctionのレベルで再利用すればよいものだと思うが、一連の作業をやっていて、改めて「クラス」って便利だなーと思った次第。
実は、closeCreatedFileメソッドというのは、もともと一連の処理だったものの最後の部分だけを切り出したものなんだが、クラスにしたおかげでめちゃくちゃ簡単に切り出すことができた。
というのも、保存して閉じる、という作業に必要なデータである新規ブックのフルパスを、このクラスのインスタンスが既に保持しているので、いちいちこのメソッドにフルパスを引数として与えなくても済むから。
処理を小分けにして切り出す、という作業をやっていていつもぶつかるのが、
引数祭り
の問題。
処理に必要なパラメータが多いときに、プロシージャ間でのデータのやりとりがめちゃくちゃ煩雑になってしまうのだ。もちろん、変数のスコープを広げたら済む話なんだが、むやみやたらと変数のスコープを広げるのはやっぱり怖い。
その点、クラスのインスタンスにデータを持たせていたら安心。インスタンスが生きている限りいつでもデータを参照できるので。
しかも、単独のSubとかFunctionの場合、ファイル単位ではエクスポートできないけど、クラスモジュールならファイル単位でエクスポートできる。
やっぱりハードVBAerならクラスモジュールを使わない手はないよなあ。
