こんにちは！加邉です。
弊社では入社が決まると同時にExcelVBAの研修が始まります。（VBAについて他の人が詳しいブログを上げているので気になったら見てみてください）
研修ではVBA課題にそったツールを実際に作っていき、なかでもかなり実用的なものもあります。
しかし、課題をこなすだけではVBAの実用化についてイメージがわきずらいとのことで、本日は実用例を作ってみたので、自分のアウトプットも含めて記事にしたいと思います。
何を作るか？
VBAの実用例としてまず思いついたのが
請求書の作成
でした。
ということで、まずは請求書のテンプレートと顧客情報が入ったサンプルファイルをChatGPTに作らせます。
仕事が早くて助かります。
上記プロンプトで作らせたファイルはデスクトップ→VBAというフォルダに保存しました。
実際の中身はというと
上記が顧客一覧と商品一覧で
こちらが請求書のテンプレートですね！
10秒くらいで作ってくれるのがありがたい！！
※請求書のテンプレートはマクロ有効ファイルの拡張子(xlsm)に変更しております。
実際のコーディング
ひとまずということで、現状の私にすぐにかけそうなマクロを作ってみました。
FileDialogを使用して、”請求書用_顧客商品情報.xlsx”の顧客一覧のシートからABC商事の
“会社名”、”住所”、”担当者”をピックアップし”請求書.xlsm”の”請求先”、”ご住所”、”担当者”に情報を代入するマクロを
組んでみました。
Sub Sample1()
Dim ws As Worksheet
Dim fd As FileDialog
Dim 顧客Path As String
Dim 顧客wb As Workbook
Dim 顧客ID As Long
Dim 顧客行 As Long
Dim 顧客名 As String, 担当者 As String, 住所 As String, 電話 As String
Set fd = Application.FileDialog(msoFileDialogFilePicker) 'FileDialogで読み取りたいファイルへ
With fd
.Show
.Title = "顧客情報ファイルを選んでください"
顧客Path = .SelectedItems(1)
End With
Set 顧客wb = Workbooks.Open(顧客Path, ReadOnly:=True)
Set ws = ThisWorkbook.Sheets(1)
With 顧客wb '顧客workbookから必要な情報を抽出
顧客名 = .Sheets(1).Range("B2").Value
担当者 = .Sheets(1).Range("C2").Value
住所 = .Sheets(1).Range("D2").Value
電話 = .Sheets(1).Range("E2").Value
End With
ws.Range("B3").Value = 顧客名 & " 様" 'With 顧客wbで抽出した情報を請求書に貼り付け
ws.Range("B4").Value = 住所
ws.Range("B5").Value = 担当者
ws.Range("G1").Value = Format(Date, "yyyy/mm/dd") '今日の日付を入力
End Sub
これを実行し出てきたダイアログから、”請求書用_顧客商品情報.xlsx”のファイルを選択すると….
このように会社、住所、担当者の欄と発行日（その日の日付になってます。）が入力されました。
しかし、これだけでは実用的かと言われるとまだほど遠いですね
それでは、表のNoのところに、商品ID、数量に適当な数値を入れた場合、自動で商品名、単価、を出力する
マクロがあったら便利かと思われます。
ということで、請求書xlsmのSheet(1)のモジュールに下記のようなコードを入力していきます。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long 'iはActiveWorkbook.Sheet(2)の行数
Dim r As Long 'rはThisWorkbookの行数
Dim 商品名 As String
Dim 商品ID As Variant
Dim 数量 As Variant
Dim 単価 As Variant
Dim 商品一覧wb As Workbook
'関係ないセルが選択された場合無視する
If Intersect(Target, Range("A10:A14,D10:D14")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Set 商品一覧wb = Workbooks("請求書用_顧客商品情報.xlsx")
On Error GoTo 0
If 商品一覧wb Is Nothing Then
MsgBox "「請求書用_顧客商品情報.xlsx」を開いてください。", vbExclamation
Application.EnableEvents = True
Exit Sub
End If
For r = 10 To 14
商品ID = Range("A" & r).Value
数量 = Range("D" & r).Value
Debug.Print 商品ID, 数量
商品名 = ""
単価 = ""
If 商品ID <> "" Then
'iは商品一覧シートの行番号、商品一覧のシートの商品IDを特定し、隣のセルの商品名、数量を特定
With 商品一覧wb.Worksheets("商品一覧")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
Debug.Print "比較：", "請求書ID=" & 商品ID, "商品一覧ID=" & .Cells(i, 1).Value
If CStr(.Cells(i, 1).Value) = CStr(商品ID) Then
商品名 = .Cells(i, 2).Value
単価 = .Cells(i, 3).Value
Debug.Print "ヒット！", 商品名, 単価
Exit For
End If
Next i
End With
End If
Range("B" & r) = 商品名
Range("C" & r) = 単価
If IsNumeric(単価) And IsNumeric(数量) Then
Range("E" & r).Value = Format(単価 * 数量, "##,###")
Else
Range("E" & r).Value = ""
End If
Next r
Application.EnableEvents = True
End Sub
つたないコードで申し訳ないです。
Private Subをつかうことで定義したイベントが発生したら発動するマクロとなっています。
※今回の場合はA10～14とD10～14に特定の数字を入力したら発動するマクロに設定しています。
FileDialogで開いた”請求書用_顧客商品情報.xlsx”の商品一覧のシートA列の番号を”請求書.xlsm”に入力すると
請求書の商品名の欄に対応する商品名、数量を入力すると金額が表示される仕組みになっております。
実行結果は以下の通りです。
Noに番号を入力すると…
対応した商品名と単価が入力され
数量を入力すると、金額が自動で入力されました。
冷静に考えるとここはVBAじゃなくてもExcelの関数で事足りますね
一応、”小計”、”消費税”、”合計”の部分にはそれぞれに対応したExcelの関数を組み込んだので、結果は自動で出力されます。（VBAでも可能だが、Excelの関数でどうにかなる部分はわざわざマクロを書く必要はないかと）
まとめ
今回のブログ記事は、VBAコードを書きながら考えていたのでかなりいい学習のアウトプットになったかと思います。
勉強するとあらゆるところでVBAを使いたいと考えがちですが、使う必要のないところで使うとかえってコードが複雑になってしまうということにも気づきました。
VBAは便利ですが、使いどころは検討の必要がありそうですね。
コメント