みなさん、こんにちはケンケンです。
今回はVBA入門編の６回目となります。
突然ですが、本編を進める前にこの記事にたどり着いてくれた方へプレゼントです。
以下でForNext構文の書き方を動画（音声なし）でご紹介しているので、
コードの書き方を視覚的に勉強したいと思っている方、理解はしているのになかなか書けないな、と思っている方は是非参考にしてください。
１分ちょっとの動画なのでサクッと見れますよ。
みなさん、こんにちはケンケンです。 今回はExcelVBAのコードの書き方について解説します。 今までも書き方のコツを紹介してきましたが、いまいちピンとこないというお声をいただきました。 [siteca[…]
動画をご覧いただいた方も、そうでない方も前回までの復習は完璧ですか？
自信のない方は復習をしてから以下を読み進めてください。
みなさん、こんにちはケンケンです。 さあ、VBA入門編も５回目を迎えることとなりました。 [sitecard subtitle=前回復習記事 url=https:/[…]
前回はIf構文を使って条件分岐の勉強をしました。
条件分岐を覚えることによって、コードを書く幅が広がったかと思います。
今回は、条件分岐の知識も合算することで、請求書作成の自動化もついに最終段階に入ります。
手順を確認しながら、自動化を最後まで進めてみましょう。
※自動化（繰り返し処理）の理屈はわかるんだけど、自前で作ろうとすると
どうしても頭がフリーズしてしまう。。。
考え方の手順より作業の手順を重点的に教えて！
という方には以下の記事がおすすめです。
みなさん、こんにちはケンケンです。 みなさんの中にはこういった方いませんか？ VBAで繰り返し構文をうまく使いたいんだけど、 コツが分からなくて結局手作業を選んでいる。 思い当たる節がありませんか[…]
請求書作成自動化の手順
ここからいよいよ請求書作成自動化に取り掛かっていきます。
前回までの知識やコードをつなぎ合わせて、実際に自動化していきます。
しかし、まだ自動化に足りない知識もありますので、それは、次項で説明します。
ここではまず、自動化までの手順を示していきます。
なお、今回自動化する請求書は請求書作成初級編で作成した資料を前提としていますのでご認識ください。
請求書作成自動化手順
- 請求データシートのB列を昇順で並べ替える
- 請求先の境目の行数を取得する
- 請求書（元）シートをコピーして一番後ろに置く
- コピーしたシートの名前を変更する
- コピーしたシートのG2セルに作成日付を入力する
- コピーしたシートのC8セルに請求先あて名を入力する
- 請求先ごとの請求データをコピーして請求書に貼り付ける
- １～7をすべての請求先で繰り返す
- 請求データシートのA列を昇順で並べ替える
イメージを先にもってもらうために、上記の手順を画像で追っていきます。
1.請求データシートのB列を昇順で並べ替える
2.請求先の境界の行数を取得する
3.請求書（元）シートをコピーして一番後ろに置く
4.コピーしたシートの名前を変更する
5.コピーしたシートのG2セルに作成日付を入力する
6.コピーしたシートのC8セルに請求先あて名を入力する
7.請求先ごとの請求データをコピーして請求書に貼り付ける
8.１～7をすべての請求先で繰り返す
9.請求データシートのA列を昇順で並べ替える
以上の手順で自動化していきます。
それでは早速手順に沿って作成していきましょう。
手順に沿って自動化してみよう
それでは、手順に沿ってコードを書いていきましょう。
請求データシートのB列を昇順で並べ替える
並べ替えをるために自動記録を使います。
自動記録について不安がある方は以下の記事を参考にしてください。
みなさん、こんにちはケンケンです。 前回は、ForNext構文を使った例をお見せしました。 請求書作成自動化への道が一歩一歩切り開かれていますよ。もう少しで完結しますからがんば[…]
「開発」タブの「マクロの記録」をクリックして記録を始めましょう
「データ」タブの「フィルター」を選択し、B列請求先を昇順に並べ替えてフィルターを解除しましょう。
そこまで出来たら「記録終了」してください。
すると以下のコードが生成されました。
今回はこれを使っていきます。F5で再度実行してエラーが出ないことを確認してください。
そして、新しいプロシージャを作成してそこにコピーしていきます。
以下のようにしていきましょう。
請求先の境界の行数を取得する
請求先ごとに請求書を作成するために必要になってきますので、それぞれの境界の行数を取得してみましょう。
具体的には請求データシートのB列を調べます。
B列の一行一行を上と下で比べて違う会社名が入力されていたら、その境界となる行数を取得して書き出します。
これも前回の復習です。
今回は以下のように書きました。
請求データシートに書き出せれば成功です。
請求書（元）シートをコピーして一番後ろに置く
続いてシートのコピーです、こちらもすでに学習済みですね。
「Worksheets(“請求書(元)”).Copy」でシートのコピーメソッドを実行し、
「after:=Worksheets(Worksheets.Count)」で置く場所を指定します。「Worksheets.Count」はシートの最後尾を指定するときに便利なので覚えておきましょう。
それでは、F5で実行してください。
シート最後尾に請求書が追加されていれば成功です。
コピーしたシートの名前を変更する
先ほどコピーしたシートの名前を変えてみましょう。
コピーしたシートはアクティブ状態なのでActiveSheet.Nameで名前を変更します。
変更する名前は、株式会社AAAとしたいので請求データシートのB2セルから値を取得しています。
■コピーしたシートのG2セルに作成日付を入力する
コードを追記していきます。
日付を取得するときはDate関数を使うとよかったですね。
コピーしたシートのC8セルに請求先あて名を入力する
どんどん進めていきます。
右辺で文字列を「＆」で結合しています。
テクニックとして使えますので覚えておくと便利です。
ここまでのコードをF5で実行すると以下のようになります。
請求先ごとの請求データをコピーして請求書に貼り付ける
それでは、追加した請求書にデータをコピーしていきます。
ここで必要になる知識がRangeオブジェクトを使った範囲指定の方法です。
いままでご紹介していた方法はRange(“A2”)のように、ひとつのセルを指定する方法でした。
しかし、今回は複数の範囲を指定する方法をご紹介します。
以下の画像をご覧ください。
C2からG5までを範囲指定して請求書に貼り付けるので、まず範囲指定するコードを書いてみましょう。
Selectを使うと分かり易いです。以下のコードを実行すると、
上記のようにセル範囲を選択してくれます。
選択出来たら、SelectをCopyに変更して以下のコードを追記します。
このコードは、コピーしたデータを貼り付けるものです。
Pasteの後で形式を指定することができます。
形式はいろいろあるのですが、今回は値の貼り付けである「xlPasteValues」を使いました。
上記のコードを実行して貼付けが成功することを確認してください。
自動化する
ここまでで、一連の処理を個別にみてきました。
これからは、すべての処理を自動化してましょう。
まずは、完成形のコードを以下に貼り付けておきますので、動作を確認したい方はコピーして実行してみてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
Sub Create_Seikyuusyo() Worksheets("請求データ").Activate If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter End If Rows("1:1").AutoFilter ActiveWorkbook.Worksheets("請求データ").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("請求データ").AutoFilter.Sort.SortFields.Add2 Key:=Range _ ("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("請求データ").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.AutoFilter Dim cnt '請求データの数 Dim SRow '範囲指定最初の行 Dim LRow '範囲指定最後の行 SRow = 2 For cnt = 2 To 16 If Worksheets("請求データ").Range("B" & cnt).Value <> Worksheets("請求データ").Range("B" & cnt + 1).Value Then LRow = cnt Worksheets("請求書(元)").Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Name = Worksheets("請求データ").Range("B" & SRow).Value ActiveSheet.Range("G2") = Date ActiveSheet.Range("C8").Value = Worksheets("請求データ").Range("B" & SRow).Value & " 御中" Worksheets("請求データ").Range("C" & SRow & ":" & "G" & LRow).Copy ActiveSheet.Range("B17").PasteSpecial Paste:=xlPasteValues SRow = cnt + 1 End If Next Worksheets("請求データ").Activate If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter End If Rows("1:1").AutoFilter ActiveWorkbook.Worksheets("請求データ").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("請求データ").AutoFilter.Sort.SortFields.Add2 Key:=Range _ ("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("請求データ").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.AutoFilter End Sub |
コードを解説していきます。
すでに解説済みのものは省略します。
「Worksheets(“請求データ”).Activate」は請求データシートをアクティブの状態にする、ということです。
アクティブ状態にしておかないと、それ以外のシートを選択している場合、このコード以下が「請求データ」
シートで実行されなくなってしまいます。
最初にアクションを起こしたいシートをアクティブ状態にしておきましょう。
次のコードですが、
これは、請求データシートにフィルターがかかっているか確認して、フィルターがかかっていたら（True）
解除する、というコードです。
フィルターがかかったままコードを進めると、フィルターを解除してしまってエラーになってしまうため、
上記のようなコードを書きます。
AutoFilterMode がTrueの場合はフィルターがかかっている、Falseの場合はフィルターがかかっていない
を表すことになるので、条件分岐の際に使えるテクニックです。
上記はすでに解説済みですね。
フィルターをかけて昇順で並べ替えをしています。
自動記録で取得したコードでしたね。
さて、請求書作成を自動化する最大の肝となるコードが上記に表示しているものです。
分かりづらいかと思いますので、ひとつずつステップインモードで進めながら解説していきます。
その前にブレークポイントの入れ方をご紹介します。
ブレークポイントを入れておくとF5で実行した際、その箇所で処理を止めてくれる機能です。
上記の箇所にブレークポイントを入れてそこまで実行（F5)しましょう。
その状態からF8のステップインでひとつずつ見ていきます。
変数cntが５になるまで進めてください。
変数cntは請求データの数を取得する変数です。
つまり、この変数が５のときに止めたのは、請求先の会社名が変わる境界を確認するためです。
ここからさらにF8で進めます。すると
If文の条件に合致したので、その中が実行されます。
Ifの条件は「変数cntの該当するB列のセルの値と次の行のセルの値が違っていたら」、でしたね。
If文が苦手な方は以下の記事で復習しておくと良いでしょう。
みなさん、こんにちはケンケンです。 さあ、VBA入門編も５回目を迎えることとなりました。 [sitecard subtitle=前回復習記事 url=https:/[…]
そして、「LRow = cnt」はLRowという変数にcntの値「５」を入れます。
変数LRowは、会社名が変わる行数を格納しておく箱です。
この場合は、株式会社AAAの最終行の５を格納しておきます。
次のコードに行きましょう
これはおなじみ、シートをコピーして最後尾に置くというコードですね。
よく分からない方は復習してくださいね。
では、次のコードです。
このコードもおなじみでシート名を変更するものですが、ひとつ追加で解説します。
変数SRowを設けて、それを使っていますね。
これは、請求先会社名のデータが入っている最初の行を取得しています。
会社名の最初は株式会社AAAで２行目発進なので、初期値に２を入れています。
その値を使ってシート名を変更しています。
上記枠の２行は、請求書の作成日とあて名の変更でしたね。
ここまで出来ましたか。
出来たら次に進みましょう。
Rangeの範囲指定箇所が難しいですが、変数を代入して改めて見ると
Range（”C2:G5”）
となることが分かりますね。
この範囲は、株式会社AAAの請求範囲（日付～単価）を指定しています。
それでは、このRangeオブジェクトで指定した部分を請求書に貼り付けていきます。
上記コードを実行すると
株式会社AAAのシートに貼り付けられました。
続けてコードを見ていきましょう
この部分は、変数SRowにcnt+1の数値を代入しています。
どういうことかというと、直前のコードで株式会社AAAの請求書は完成したので
次は、株式会社BBBの請求書を作って行くことになりますが、「請求データ」シートを見ると
株式会社BBBのデータは６行目からになっていますね。
その６行目という数値を次の値として変数に渡しておくと、次の請求書を作る際にその変数が使える、ということです。
便利なので、変数に数値を代入するコツとして覚えておいてください。
さあ、ここまでできれば後はステップイン（F8）で株式会社FFFの請求書まで作ってみてください。
その際に、変数がどのように変化していくか確認しながら進めると理解が深まります。
最後に以下のコードで「請求データ」シートのA列を昇順に直しておきましょう。
いかかですか。
コードの説明は以上になります。
最後に今回書いたコードを一気に実行して、請求書作成が自動化できていることを確認してください。
まとめ
今回まで、VBA入門編として６回に渡り勉強してきましたが、いかがでしたでしょうか。
難しいと感じた人、意外に簡単だなと感じた人
様々かと思いますが、VBAは基本に忠実に手順を追って勉強すれば必ずできるようになります。
自動化するコツは、
- 一つ目の処理を丁寧に仕上げる
- それを条件分岐されて繰り返す
でしたね。
あくまで、一つ目の処理に対応するコードをどれだけシンプルに分かり易く書けるかが重要です。
そのためには関数や条件付き書式などの使い方を合わせて勉強しておきましょう。
コードを書く量を減らせますし、メンテナンスしやすくなります。
この記事を読んだ方がVBAで世界を広げてさまざまなことにチャレンジできるようになる一助になれたら幸いです。
それではまたお会いしましょう。
————————————————————————————-
○もし、よろしかったら僕宛にメールください。
内容は質問・相談・共有など何でもけっこうです。
すべて読んで返信させていただきますのでお気軽にどうぞ！
※Gmail以外をお使いの方はアドレスをコピーして送信してください。
————————————————————————————–
新しい言語に挑戦したい方におススメ記事
みなさん、こんにちはケンケンです。 今回は、Pythonを使って簡単なプログラミングをします。 なぜ、Pythonを紹介するかと言うと、Pythonは様々な機能をもった言語で学びやすいという 特徴がある[…]
ここまで読んでいただいた方におススメ記事
みなさん、こんにちはケンケンです。 エクセルVBA入門編では、請求書作成を自動化してみました。 今回は、入門編でご紹介しきれなかったテクニックについて解説していきます。 入門編で作ったツールをカスタマイ[…]
エクセルVBA入門編～請求書作成自動化への道～記事一覧
【エクセルVBA入門編①】VBAで何ができるのだろうか？【請求書自動作成の第一歩】
【エクセルVBA入門編②】めんどうな仕事はForNext構文で解決【請求書自動化の最重要構文】
【エクセルVBA入門編③】繰り返し作業はFor Next構文で完全自動化！【請求書作成自動化は目前！】
【エクセルVBA入門編④】自動記録を使い方とコード編集法を解説【請求書作成自動化への道】
【エクセルVBA入門編⑤】If Then構文で条件分岐をマスター【請求書自動化の最終ステップ】
【エクセルVBA入門編⑥】手順に沿って請求書を作成してみよう【請求書作成自動化最終章】
請求書作成の基礎を復習したい方はこちら
[請求書作成初級編②]エクセルを使いこなすための第一歩[入力規則・フィルター]
[請求書作成初級編③]エクセルを使いこなすための第一歩[完成＆PDF化と整理の仕方]
