【VBAデスクトップツール】追加データを表に転記するマクロツール｜もしも普通のサラリーマンがお金とITを学んだら

このページでわかること

マクロVBAの初級編
表に新たなデータを追加するデスクトップツールが
作れるようになります。
応用すれば日々の出荷データや工場製品の生産履歴を管理することが可能です。
シート内での処理です。
まずはシートをまたがない処理をモノにしましょう。

こんにちは、hokkyokunです。

マクロVBAを活用したデスクトップツールを作ってみましょう。
といってもすごく簡単です。

簡単な割にしっかり実務に使える仕様となっているので
学習しつつ、実務に生かしつつで一石二鳥を狙いましょう。

Contents

お買い物リストのツールを作ってみよう
プログラム
体系的に学びたい　or 初歩の初歩から学びたい
実際に使ってみます。
【応用編】実際に私が作る場合のコード
いきなりエラーに強いコードを書くのは無理。でも目指すべき

お買い物リストのツールを作ってみよう

下記のようなお買い物をした商品リストを
表にまとめるマクロツールを作ってみましょう。

何となくわかると思いますが、

右下の青色の表（入力欄）に日付、商品、単価、数量を入力
プログラムを実行すると左の表の最下部に転記される
合計金額を右上の「合計」に計算して転記
入力欄の値をクリアしてプログラム終了

プログラム

ステップ0　コードを書く前の基本

書く前に知っておくべきことを紹介しておきます。

プログラムを書く場所（エディタ）は　Alt + F11　で開く
エディタの初期設定を行っておく（初回のみ）
ブック、シートは必ず指定して省略しない

①Alt + F11　でエディタを開く

Alt + F11を押すと下記のようなエディタが開きます。
私は目に優しいように黒背景にしていますが、
初期設定では白いはずです。

②エディタの初期設定

必須ではありませんが、
エディタの初期設定をいじっておいた方が
後々コーディングが楽になります。

③ブック、シートは必ず指定する

書籍やブログによっては
あまり触れられないところかもしれませんが、

癖として
対象としているブック、シートは
明確に指定をするようにしておくことをお勧めします。

どういうことかというと

Sub test()
 
'悪い例
Range("A1").Value = "リンゴ"
 
'良い例
ThisWorkbook.Worksheets(1).Range("A1").Value = "リンゴ"
 
'長いの嫌なら以下のどちらかがお勧め
'①
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
ws.Range("A1").Value = "リンゴ"
 
'②
With ThisWorkbook.Worksheets(1)
    .Range("A1").Value = "リンゴ"
End With
 
End Sub

今回くらいの規模であれば正直なんでもいいのですが、

シート間、ブック間のプログラムを書くようになった場合は
どのシート、ブックを指定しているのか明確に指示していないと
意図と違うシートやブックを操作することになります。

私はVBA覚えたてのころ
これで痛い目に何度も会いました。

少なくとも同じ轍を踏まないように
注意していただければと思います。

変数格納記事

コード

さて、では、プログラムコードをお見せします。
重要な部分について下記で解説します。

必要な知識を身につけましょう。

Sub add_Items()
 
'シートを変数に格納
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
 
 
'入力値を変数に格納
Dim Date_ As Date
Dim Item As String
Dim Unit As Double
Dim QTy As Long
 
Date_ = ws.Cells(7, "H").Value
Item = ws.Cells(8, "H").Value
Unit = ws.Cells(9, "H").Value
QTy = ws.Cells(10, "H").Value
 
 
'入力値を表に転記する
 
'表の最下段を取得する
Dim maxRow As Long
maxRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
 
ws.Cells(maxRow, "A").Value = Date_
ws.Cells(maxRow, "B").Value = Item
ws.Cells(maxRow, "C").Value = Unit
ws.Cells(maxRow, "D").Value = QTy
 
'小計を計算する
ws.Cells(maxRow, "E").Value = Unit * QTy
 
'合計を計算する
ws.Cells(3, "H").Value = Application.WorksheetFunction.Sum(ws.Range(ws.Cells(2, "E"), ws.Cells(maxRow, "E")))
 
'入力欄をクリアする
ws.Range("H7:H10").ClearContents
 
End Sub

8～17行目　セルの値の代入

変数を宣言し、値をセルに代入します。
こうすることで、特定の値を持ち運びすることができます。

基本的には欲しいデータは変数に入れて使うようにすると
コーディングも簡単になるし、間違いも起きにくく、おすすめです。

変数 = レンジオブジェクト.Value

詳しい解説はこちらの記事

【VBA】指定したセルを取得する方法。セルから値を取り出し、クリアする方法（複数範囲、ループ処理）わかること指定のセルを取得指定したセルから値を取り出す指定したセルに値を代入指定したセルの値をクリア ...

22~24行　表の最下段を取得

よく使うコードです。
ぜひマスタしてください。

最下段
シートオブジェクト.Cells(Rows.Count, 1).End(xlUp).Row

【VBA】エクセル、セルの最下段（最終行）・最右列（最終列）を取得する方法。テーブルも対応このページでわかること VBAを使ってシートにある表の最下段（最終行）と最右列（最終列）の位置を取得できます。テーブルオブジ...

34～35行目　入力欄をクリア

セルの値をクリアするための手法です。

レンジオブジェクト . ClearContents

詳細は以下の記事を確認してみてください。

体系的に学びたい　or 初歩の初歩から学びたい

全てを体系的にブログで学ぶには正直限界がありますので
書籍もしくはUdemy等動画チャンネルでの学習をお勧めします。

書籍は私はこちらの書籍を最初に購入しました。
とりあえず、マクロを動かすのであれば十分な良書です。

Excel マクロ＆VBA　［実践ビジネス入門講座］【完全版】第2版

Amazonで見る楽天市場で見る Yahoo!ショッピングで見る

動画はUdemyがおすすめです。

Udemyに匹敵する動画数、
知名度（有名であるほど、ネットトラブルは少ないです。）、
コスパはあまりありません。

なんせ、動画購入後もほとんど見る前であれば
返金可能なんで!(^^)!

ちなみに、今回のような入力データを表に転記するマクロを解説している
動画もあります。

有料ですが、その分入力フォームも作れるので
さらに実務に近い。というか実務で使える内容です。

Udemyの公式サイトはこちらです。

動画のおすすめ講師についてまとめています。
よかったら参考にしてみてください。

【Udemyは講師で選べ！】UdemyがVBA学習に最適な理由とおすすめのVBA講師こんにちはhokkyokunです。巨大学習プラットホームUdemyの中からVBAに関する動画について講師に焦点を当ててまとめま...

実際に使ってみます。

実際にコードを動かしてみましょう。

動かすためにボタンを作成してみました。
入力内容は　「2023/5/3」「ミカン」「50円」「8個」とします。

ボタン「入力」を押してみます。

無事最下段に入力されました。
合計金額もしっかり更新され、入力欄もきれいに消されています。

【応用編】実際に私が作る場合のコード

辞書とテーブルを使ってエラーに強く、簡潔なコードにする

プログラム自体にはまったく問題ありませんが、
もし私が自分で使うツールを作る場合はもう少し違うコードで書きます。

理由は以下の通りです。

このままでは項目の追加をするとエラーが発生する
コードが冗長。
このくらいならまだいいが、
これ以上複雑になると、このままでは修正が面倒

私は以下のツールや方法を使って
コーディングします。

表をテーブル化
入力欄もテーブル化
辞書を活用

どちらも大事なツールで
その魅力は一言で言い表せませんが、

辞書は例えば{“雨”:”傘”}のように
1：1の関係でキーと値を格納します。

これはプログラミングではよく使う概念であり、
そして何かとお世話になります。

エクセルの機能もうまく使おう（テーブル）

テーブルはエクセル独特の概念ですが、
学習必須事項です。

VBAのコーディングも楽になりますし、
エクセル上でもメリットがあります。

下記のように表と入力欄をテーブル化させます。
表のテーブル名：datas
入力欄のテーブル名：input
こうしておくと簡単にVBAでもテーブル操作を行うことができます。

シート関数も積極的に使っていきましょう。
テーブルはシート関数の扱いも簡単にしてくれます。

シート関数を使うことでVBA上で行っていた
「小計計算」と「合計計算」を省略できました。
省略できただけでなく、
データの位置や量が変わっても対応してくれるようになっています。
これがテーブルのすごさです！！

辞書もテーブルも癖があるので
やや困惑してしまいますが、徐々に覚えていきましょう。

テーブルに関する記事です。

コード

Sub add_Items2()
 
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
 
'入力欄と表をテーブルとして扱う
Dim table, table_input As ListObject
Set table = ws.ListObjects("datas")
Set table_input = ws.ListObjects("input")
 
'入力データを辞書に格納
Dim dicts As New Dictionary
Dim r As Range
For Each r In table_input.ListColumns("項目").DataBodyRange
    dicts.Add r.Value, r.Offset(, 1).Value
Next
 
'入力データを表に転記
Dim key As Variant
With table.ListRows.Add
    For Each key In dicts
        .Range(table.ListColumns(key).Index).Value = dicts.Item(key)
    Next
End With
 
'入力欄のクリア
table_input.ListColumns("値").DataBodyRange.ClearContents
End Sub