VBAを使ったツール

【VBAデスクトップツール】追加データを表に転記するマクロツール

記事内に商品プロモーションを含む場合があります

こんにちは、hokkyokunです。

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

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

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

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

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

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

プログラム

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

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

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

①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

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

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

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

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

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

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

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

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

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

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

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

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

【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

使ってみます。

今度は「2023/5/4」「ブドウ」「300円」「4個」で入力します。

動かしてみます。

このコードは改変に強い

こう思った方もいるのではないでしょうか。

これのどこが簡潔なんだよww

最初のコードの方がシンプルでわかりやすいよ

確かに最初のコードの方が簡単で
分かりやすいかもしれませんが、
本領はシートを改変したときです。

試しに、入力欄に「備考」「スーパー」を追加してみます。

お気づきになりましたでしょうか?

この改変には問題がいくつかあります。
特に最初のコードを使うと次の点で躓きます。

  • 表の項目が増え、転記する位置も変わる
  • 入力欄の順番と表の順番が違う
  • 合計の位置も変わる

項目が増えたり、順番が変わるは実務上仕方がないことだと思います。

厳しい言い方ですが、
そのたびにコードを直していては真の業務効率化にはなりません

ちなみに新しい方のコードで
「2023/5/5」「山の上スーパー」「メロン」「500円」「3個」「特売」として打ちます。

動かしてみます。

問題なく動きました。

いきなりエラーに強いコードを書くのは無理。でも目指すべき

さて、いかがでしょうか。
「こんなにまでやらなくていいよ」と思ったでしょうか。

ちなみに私は最初のコードが書けたら全然満足し、
即業務に使っていました。

それは悪いことではありません。
それで救われたことは何度もあります。

ただ、表を改変するたびにエラーが起きて
その処理で大変な目にあったのも事実
です。

最初からエラーに強いコードは目指さなくていいと思います。

でも、いつかは挑戦してほしいと思います。
それが後々自分と組織を守ることになると思います。

VBAの学習方法をまとめました。

VBA(マクロ)のおすすめの学習方法 こんにちはhokkyokunです。 VBAを学ぶことで確実に業務は効率化し、余裕をもって仕事をすることができるようになります。 ...

VBAを高コスパで、短期間で学ぶにはUdemyがおすすめです。
Udemyは良質の学習プラットフォームですが、
動画数が多すぎてどれを見ればよいか迷います。

おすすめの講師をまとめました。

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

ブログ村ランキング参加中です。よかったらフォローお願いします!!

PVアクセスランキング にほんブログ村