任意のワークシートを新規Excelブックとして出力するクラス

ワークシートを新規Excelブックとして出力するクラス

ワークシートを新規Excekブックとして出力する

名簿なんかの一覧表をマクロで自動作成して、相手方にPDFで提供すると、しばしば

加工したいからExcelのままでくれ!

と言われることがよくある。

大量(っつっても大した量でもないけど)の元データやマクロだらけのブックをそのまま送りつけるわけにも行かないので、必要なシートだけを新しいブックにコピペして提供、という形になる。

ただ、それも1つや2つなら手作業でポンとやっておしまい、なんだが、次から次へ、となるとさすがにめんどくさい。

で、やってみたのが

akashi-keirin.hatenablog.com

なんだが、今後こうした作業が頻発することが予測されるのでクラス化しといた。

任意のワークシートを新規Excelブックとして出力するクラス

リスト1 クラスモジュール 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

かなりタテ長になってしまったので、パーツごとに解説する。

目次
  1. 宣言セクション
  2. フィールド
  3. アクセサ
  4. コンストラクタ
  5. createExcelFileメソッド
  6. closeCreatedFileメソッド
  7. raiseErrorメソッド
宣言セクション
Private Const NOT_INITIALIZED As Integer = 1
Private Const NOT_CREATED As Integer = 2

今のところ定数を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)

で、引数を2つ設定している。

第1引数fullPathは、新規に作成するExcelブックのフルパス。

第2引数isOnlyToCreateは、Trueにすると指定したシートをそのままExcelブック化する。シートに何らかの加工をしてからExcelブック化したいなら、この引数をFalseにしておく必要がある。

(2)の

If isInitialized_ = False Then Call raiseError(NOT_INITIALIZED)

では、このクラスのインスタンス生成後、initメソッドが実行されているかどうかを判定し、未実行ならraiseErrorメソッドを呼び出してエラーを起こさせるようにしている。

さて、このメソッドのメインの処理については、前に書いたのと全く同じなので、詳しいことは

akashi-keirin.hatenablog.com

をどうぞ。

(3)の

If isOnlyToCreate = True Then Call closeCreatedFile

では、第2引数の値によって、このまま新規ブックの保存・終了に進むのかどうかを分岐している。

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メソッドを用いてエラーを吐かせているだけ。

今後、新たなエラー原因が分かったら逐次追加していけば良い。

使用例 その1

f:id:akashi_keirin:20170702093636j:plain

こんなシートをアクティブにして次のリスト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)からの2行

str = ThisWorkbook.Path & "\超人墓場\"  '"
str = str & "都道府県別1.xlsx"

では、新しく作成するブックのフルパスを変数strに格納。2回に分けているのは、可読性とメンテナンス性のため。別に1回で書いてもいい。

(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(新規ブックのフルパス)を渡して実行。第2引数がTrueなので、指定したWorksheetオブジェクトに何も加工せずに保存・終了することになる。

実行結果

f:id:akashi_keirin:20170702093647j:plain

このように、「超人墓場」フォルダに「都道府県別1.xlsx」ができていて、開くと

f:id:akashi_keirin:20170702093700j:plain

この通り、シートがそのまま保存されている。

ただ、このままだと、いかにもA~C列が邪魔。かといって手動で削除するとなると果てしなくダルい。

そこで、新しいブックを作った後、A~C列を削除した後で保存・終了する、という処理にしてみる。

使用例 その2

リスト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メソッドを呼び出すときの第2引数をFalseにし、

(2)でA~C列を削除してから、

(3)でcloseCreatedFileメソッドを呼び出して新規ブックを保存・終了している。

実行結果

f:id:akashi_keirin:20170702093721j:plain

このように、「超人墓場」フォルダに「都道府県別2.xlsx」ができていて、開くと

f:id:akashi_keirin:20170702093731j:plain

この通り、A~C列だった部分が削除されてシートが保存されている。

おわりに

今回紹介した程度の処理なんて、フツーに考えたらSubとかFunctionのレベルで再利用すればよいものだと思うが、一連の作業をやっていて、改めて「クラス」って便利だなーと思った次第。

実は、closeCreatedFileメソッドというのは、もともと一連の処理だったものの最後の部分だけを切り出したものなんだが、クラスにしたおかげでめちゃくちゃ簡単に切り出すことができた。

というのも、保存して閉じる、という作業に必要なデータである新規ブックのフルパスを、このクラスのインスタンスが既に保持しているので、いちいちこのメソッドにフルパスを引数として与えなくても済むから。

処理を小分けにして切り出す、という作業をやっていていつもぶつかるのが、

引数祭り

の問題。

処理に必要なパラメータが多いときに、プロシージャ間でのデータのやりとりがめちゃくちゃ煩雑になってしまうのだ。もちろん、変数のスコープを広げたら済む話なんだが、むやみやたらと変数のスコープを広げるのはやっぱり怖い。

その点、クラスのインスタンスにデータを持たせていたら安心。インスタンスが生きている限りいつでもデータを参照できるので。

しかも、単独のSubとかFunctionの場合、ファイル単位ではエクスポートできないけど、クラスモジュールならファイル単位でエクスポートできる。

やっぱりハードVBAerならクラスモジュールを使わない手はないよなあ。

@akashi_keirin on Twitter