【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法

photo credit: rcamboim “Frágil” / “Fragile” via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

初心者向けエクセルVBAのシリーズとして請求データから請求書を作成するマクロの作り方をお伝えしています。

前回の記事はこちら。

【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法

初心者向けにエクセルVBAで請求データ一覧から請求書を自動で作成する方法をお伝えしています。今回はワークシートをコピーする方法、Activesheetの使い方、シート名を変更する方法についてお伝えします。

ワークシートのコピーとシート名の変更について伝えしましたので、それを活用して、取引先別の請求書を別シートとして作成していきたいと思います。

それを進める上で、「ワークシートをオブジェクト変数にセット」をしたほうが良いので、今回は、その点について解説をしていきます。

エクセルVBAでオブジェクトを変数にセットして取り扱う方法についてお伝えします。

なお、本記事は以下のYouTube動画と連動していますので、合わせてご覧くださいませ。

では、行ってみましょう！

前回のおさらい
1. ActiveSheetを使い続けるリスク
オブジェクト変数とは
複数の取引先向けに請求書を作成するプログラム
1. 請求書を作成するプログラム
2. 実行結果
まとめ
連載目次：データ一覧から請求書を自動で作る

前回のおさらい

まずは、前回のおさらいから。

作成したプログラムはこちらです。

Sub 取引先ごとのシートをコピー()

Dim rowsClient As Long
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '取引先マスタの最終行数

Dim n As Long
For n = 2 To rowsClient
    
    Dim client As String
    client = wsClient.Cells(n, 1).Value
    wsTemplate.Copy After:=wsTemplate
    ActiveSheet.Name = client
    
Next n
End Sub

「請求書ひな形」シート（wsTemplate）をコピーして、「取引先マスタ」シート（wsClient）に記載されている取引先分だけシートを作成するというものです。

あとは、各取引先ごとのシートに、以下の「請求データ」シート（wsData）のデータを転記していくという処理を追加したいわけです。

ActiveSheetを使い続けるリスク

12行目以降に、生成した各取引先ごとのシートにデータを転記していくわけですが、そのシートを指し示すものとして、「ActiveSheet」をずっと使い続けるのは望ましくありません。

というのも、マクロを実行中に、ユーザー操作によってアクティブなシートが変更されてしまい、プログラム内で想定していないワークシートが処理の対象となってしまう可能性があるからです。

なので、コピーして生成されたActiveSheetを、すぐさま「オブジェクト変数」にセットして、その「オブジェクト変数」を対象にマクロの処理を進めていくのが理想です。

そうすれば、ユーザーの操作による干渉を受けずに済みます。

オブジェクト変数とは

オブジェクト変数とは、オブジェクトをセットするための変数のことです。

オブジェクト変数にはオブジェクトを格納することができ、オブジェクト変数は格納したオブジェクトそのものとして扱うことができるようになります。

オブジェクト変数の宣言

数値や文字列を格納する変数と同様に、オブジェクト変数もDimステートメントで宣言をします。

Dim オブジェクト変数 As データ型

データ型には、例えば「Worksheet」や「Range」といった固有のオブジェクトをデータ型として指定することもできますし、どのオブジェクトにも対応するデータ型「Object」を指定することもできます。

今回の場合、例えば、Worksheetオブジェクトを格納する「ws」というオブジェクト変数を宣言するなら、以下のようなDimステートメントとなります。

Dim ws As Worksheet

オブジェクト変数にオブジェクトをSetする

次に、宣言したオブジェクト変数にオブジェクトを格納していきます。

その際、オブジェクト変数の場合は、数値や文字列の代入と異なり、Setステートメントを使用します。

書式は以下の通りです。

Set オブジェクト変数 = オブジェクト

オブジェクト変数を格納するときには「Set」です。非常に忘れやすいので、注意してくださいね。

例えば、ActiveSheetをオブジェクト変数wsにセットするのであれば、以下のように記述します。

Set ws = ActiveSheet

オブジェクト変数を用いたオブジェクトの操作

冒頭のプロシージャについて、オブジェクト変数を用いたものに書き直すと以下のようになります。

Sub 取引先ごとのシートをコピー()

Dim rowsClient As Long
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '取引先マスタの最終行数

Dim n As Long
For n = 2 To rowsClient
    
    Dim client As String
    client = wsClient.Cells(n, 1).Value
    wsTemplate.Copy After:=wsTemplate
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Name = client
    
Next n
End Sub

実行すると、ちゃんと動いてくれるはずですので、確認してみて下さい。

複数の取引先向けに請求書を作成するプログラム

ということで、請求書作成マクロを修正してみましょう。

以下記事の、単一の取引先向けに請求データから請求書を作るものと

【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する

初心者向けエクセルVBAのシリーズとして請求データから請求書を作るマクロの作り方についてお伝えしています。今回はDateSerial関数を使って月末日または翌月末日を自動算出する方法についてお伝えします。

今回紹介した前述のプロシージャをミックスしたものが、こちらです。

Sub 請求書作成()
Dim rowsData As Long, rowsClient As Long
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '請求データの最終行数
rowsClient = wsClient.Cells(Rows.Count, 1).End(xlUp).Row '取引先マスタの最終行数

Dim dayCutoff As Date
dayCutoff = Application.InputBox("年月を入力してください", "対象年月を入力", Format(Date, "yyyy/mm"))

Dim n As Long
For n = 2 To rowsClient
    
    Dim client As String
    client = wsClient.Cells(n, 1).Value
    wsTemplate.Copy After:=wsTemplate
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Name = client
    
    Dim i As Long, k As Long
    k = 21
    For i = 2 To rowsData
        
        If wsData.Cells(i, 2).Value = client Then
            Dim deliDate As Date
            deliDate = wsData.Cells(i, 1).Value
        
            If Year(deliDate) = Year(dayCutoff) And Month(deliDate) = Month(dayCutoff) Then
                wsData.Range(wsData.Cells(i, 3), wsData.Cells(i, 5)).Copy ws.Cells(k, 1)
                k = k + 1
            End If
        End If
        
    Next i

    '終了処理
    ws.Rows(k & ":50").Hidden = True  'データがない行を隠す
    ws.Range("A18").Value = "ご請求金額：" & Format(ws.Range("D54").Value, "#,##0") & " 円"
    ws.Range("A3").Value = client & "御中"  '取引先名
    ws.Range("A5").Value = "〒" & wsClient.Cells(n, 2).Value '郵便番号
    ws.Range("A6").Value = wsClient.Cells(n, 3).Value '住所１
    ws.Range("A7").Value = wsClient.Cells(n, 4).Value '住所２
    ws.Range("D15").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 1, 0) '請求日
    ws.Range("D16").Value = DateSerial(Year(dayCutoff), Month(dayCutoff) + 2, 0) 'お支払期限

Next n
End Sub