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


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

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

前回の記事はこちら。

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

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

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

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

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

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

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

前回のおさらい

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

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

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 '住所1
    ws.Range("A7").Value = wsClient.Cells(n, 4).Value '住所2
    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

請求書を作成するプログラム

以下ポイントを整理しておきますね。

まず、10~45行目はnに対するFor文になっていますが、以下の「取引先マスタ」シート(wsClient)にリストされている、取引先の数だけ繰り返しをします。

エクセルVBAで操作する取引先マスタシート

14~17行目は、以下の「請求書ひな形」シート(wsTemplate)をコピーして、取引先別のワークシートを新たに作成する部分ですね。

エクセルVBAで操作する請求書ひな形シート

ちなみに、新たにコピーして生成されたシートはアクティブになりますので、以降ActiveSheetとして取り扱われます。

21~33行目は、以下の「請求データ」シート(wsData)のうち、納品日の年月が対象の年月で、かつ取引先が該当のものであれば、アクティブシートに転記するというものです。

エクセルVBAで操作する請求データシート

最後に36~43行目が、取引先情報やご請求金額、請求日、お支払期限を転記する部分です。

実行結果

実行すると、以下のように取引先別の請求書がシートとして作成されます。

エクセルVBAで取引先別に請求書を作成

まとめ

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

ActiveSheetなどユーザーの干渉を受けたくない場合、別名で取り扱いたい場合には、オブジェクト変数を使うのが有効です。

ワークシートの場合は、シートのオブジェクト名を使う方法もありますので、時と場合によって使い分けましょうね。

次回は、ワークブックの開き方についてお伝えします。

【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
初心者向けエクセルVBAのシリーズとして請求書を作成するマクロの作り方をお伝えしています。今回は、エクセルVBAで新たなブックを開く方法です。コレクションやWorkbooksプロパティについても解説しますよ。

どうぞお楽しみに!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
  3. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  4. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  5. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
  7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
  8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
  9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
  10. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  11. 【初心者向けエクセルVBA】For~Next文でセル範囲を一行ずつ移動させる
  12. 【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
  13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
  14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
  15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
  16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
  17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
  18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
  19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
  20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
  21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
  22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る

コメント

  1. せんかん より:

    全部みました。
    とても良い説明で、ありがとうございます。
    中級と高級のクラスはクラスが期待しています! ありがとうございます。

    • せんかんさん

      全て読んでいただきまして、ありがとうございます!
      クラス、いつかは書きたいですね…気長にお待ちいただければと思います!

タイトルとURLをコピーしました