こんにちは、デスクワークラボの吉井良平です。
このサイトを読んでいただいている方から、「大体のマクロの書き方は分かりましたが、実際には作れません・・・」というコメントをいただきました。
確かに英語とかと同じで、VBAの基本的な文法が分かっても、実際に使えるようになるまでには、結構大変なのです。
なので、少しずつ「マクロの作り方」についても紹介していこうと思います。
具体的な作成例として何が良いかな、と考えたところ、まずは見積書の作成が良いかと思いますので、見積書作成マクロの作り方を紹介します。
結構細かい部分まで紹介しているので、いきなりは難しいと思いますが、少しずつモノにしていってくださいね。
ここまでできれば、かなり実用的なファイルが作れますよ！
今回作る見積書のイメージ
ただ単純に見積書を作るだけであれば、マクロを使わなくてもできるのですが、「手作業だと面倒くさい」部分をマクロで対応します。
具体的には、見積番号の取得、見積の履歴を取る、見積書を別名で保存する、という点を、マクロで対応します。
手作業だと面倒くさくてやってられない、とか、たまに抜け漏れが出る、という部分をマクロにして、「ミスなく、素早く行う」ようにします。
実際にどんな動きになるかは、動画をご覧くださいね。
見積書を作るために必要なモノ
マクロを使って見積書を作っていく際に、いくつか必要なモノがあります。
まずは、必要な要素をそろえていきましょう。
（今回の記事で作るサンプル用に、必要な要素をすべてそろえたエクセルファイルを用意しています。必要に応じてダウンロードしてくださいね。）
見積書のフォーマット
見積書を作成するために、見積書のフォーマットが必要になります。
会社で見積書のフォーマットがあれば、そのフォーマットを使ってくださいね。
特に決まったフォーマットが無ければ、ビズオーシャンなどのホームページから無料でダウンロードできます。
ワードのファイルでもできないことはないですが、今回はエクセルでの作り方の紹介なので、エクセルの見積書フォーマットをダウンロードしてください＾＾
入力用シート
見積書のマクロを作る際には、見積書のフォーマットに直接入力するのではなく、入力は入力用シートで行なって、見積書のフォーマットに転記する形にします。
フォーマットというのは、印刷用だったり手書き用に調整されたものなので、入力には適していないことが多いです。
また、フォーマットに直接入力すると、コピペをした際に罫線などの書式がコピーされてフォーマットが崩れたり、あとで面倒なことになる可能性もあります。
入力用フォーマットは、罫線等の書式にはあまり凝らなくても良いと思います。入力しやすいことが大事です。
入力する部分以外に色をつけて入力位置を分かりやすくするとか、複数の人が使う場合はシートに保護をかけておくとか、データの入力規則で間違った値が入らないようにするとか、入力を間違えないような工夫をしておくと、なお良いです。
「入力と出力を分ける」というのは、とても重要な考え方なので、マクロを使う方は必ず身につけて下さいね。
なぜ入力と出力を分けるかは、エクセル方眼紙の討論会に出席された長岡さんのサイトに詳しく書いてありますので、詳しく知りたい方は下記の長岡さんの記事をご覧ください。
Excel方眼紙公開討論会 presented by Forguncyで使用した資料置いときます
（長岡さんのサイトに飛びます）
価格表（商品マスター）
見積書を作るのに、毎回単価を手入力しなくて良いよう、価格表があった方が良いです。
小さな会社の場合は価格表を作っていないことも多いですが、自分のサービスメニューの確認にもなりますので、もし価格表が存在しなければ、価格表を作ることをお勧めします。
価格表は、ご自分が見積書を作る際に必要な項目を網羅してくださいね。
価格表というと罫線が引かれた表をイメージされるかもしれませんが、見積書を作る際には特に罫線など必要ありません。
品番（サービス名）と価格が一対一になっていれば大丈夫です。
（一対一、というと難しいかもしれませんが、品番と価格が同じ行にあって、同じ内容が重複していなければOKです。）
価格表の例：商品と価格が一対一になっていればOK
番号簿（見積台帳）
見積書でも納品書でも請求書でも発注書でも、書類には番号をつけておくと便利です。
宅配便でも「お問い合わせ番号」というのがありますが、番号で特定しておくと、お互いの理解が違うことがなくなりますし、仕事も早くなります。
番号の付け方は、通し番号だけだったり、年月+通し番号だったり、お仕事に合った形にしていただければ良いのですが、ともかく番号をつけることが大事です。
ということで、見積書をマクロで作る場合は、見積りの番号簿（管理台帳）を作りましょう。
メンテナンスはマクロで自動で行ないますので、とりあえずはシートを作っておくだけで大丈夫です。
見積履歴（データベース）
見積りの履歴を取っておくと、後で役に立ちます。
せっかくなので、見積りの履歴を取っておけるよう、一つシートを作っておきましょう。
大体、どこの業界でも売れ筋商品が決まっていたり、セットにして提案する商品があったりします。
そんな時に、見積りの履歴を取っておくと、過去の見積りからコピペして見積書を作ることができるようになります。
また、見積りした内容そのままで請求書を作ることも多いですよね。
見積りの履歴をとっておけば、履歴から請求書を作ることができるようになります。
見積書のVBAを書いていく
必要なシートが揃ったので、見積書を作るVBAを書いていきましょう！
今回作る見積書はそんなに複雑なものじゃないので、このままVBAのコードを書いていきますが、少し複雑なものであれば、簡単なフローチャートを書いておいた方が良いです。
今回のような簡単な（慣れないうちは難しいと思いますが）プログラムは、「手作業でやるイメージをそのまま」プログラムに書き起こすという感じで作れば大丈夫です。
では、少しずつパートに分けて解説していきますね。
（最後に、プログラムの全てをコピペできるようにしています）
必要事項の入力漏れを回避する
見積書でも何でも、書類を印刷した後で「あっ！ここが入力漏れだった」ということに気が付いて、処理をやり直すことがたまにあります。
（性格にもよります。私は、こういったウッカリがそこそこ多い方・・・なので、エクセルの機能をいろいろ使うよう工夫するようになったというところもあります。）
致命的な入力漏れが無いよう、あらかじめ必須事項の入力漏れが無いようにVBAのコードを入れておくと便利です。
変数の宣言
まずは、プログラムで使っていく変数を宣言しています。
変数の名前は、お好みで付ければ良いです。今回は、「WS1～WS5」をワークシートを格納する変数にしています。
今回のように、全てのシートに変数を割り振る場合は、左から順番の位置で変数名を作っておくと分かりやすいと思います。
LCRN1という変数名は、個人的によく使っている変数名で、「Last Cell Row Number」ということで最終行の意味合いで使っています。
最終行もシートごとに違うので、これも1～5の番号をふっておきます。
入力漏れの時に、処理を終わらせる
見積書なので、見積先が入力されていないと書類として成立しません。
なので、
If WS1.Range(“D3”) = “” Then
MsgBox “見積先が入力されていません”
Exit Sub
End If
で、D3のセルが空白の場合、メッセージを表示して、マクロから抜ける（Exit Sub）ようにしています。
これで、見積先が入力漏れの場合には、次の処理に進まないようにすることができます。
同様に、合計金額（H5）が0の場合も、次の処理に進めないようにしています。
（H3のセルに、計算式で合計金額を表示させていることが前提です）
担当者の欄を必須にするなど、必須の項目を増やす場合は、同じようなやり方で入力漏れを回避してくださいね。
見積番号を取得する
次は、見積番号を取得します。
コードの説明
WS3という変数に、「番号簿」というシートを割り当てて、番号簿のシートの最終行の次の行をLCRN3という変数に入れています。
LCRN3 = WS3.Cells(AllRow,1).End(xlUp).Row+1
AllRowという変数は、一番最初に「すべての行」という値を入れているので、この時点ではエクセルの行数（1048576）という値が入っています。
セルの1048576行目の1列目（A1048576）から上方向に向けて次に値がある行番号に、1を足した値をLCRN3という変数に入れているのです。
※これは、LCRN3=WS3.Cells(Rows.Count,1).End(xlUp).Row+1 とも書けます。AllRowという変数を使わずに、直接、行数を数えた値を入れるパターンで、慣れたらこちらの書き方の方が多くなるかもしれません。
下の画像の例では、一番下のセルから上方向に向けて値が入っている行は、3行目です。
よって、LCRN3という変数には、3+1 で 4という値が入っています。
見積番号は、RefNum という変数名にしています。（Reference Numberの略）
RefNum = “M” & LCRN3 – 3
で、MにLCRN3から 3 を引いた数をつなげたものを、見積番号ということにしています。
（この場合は、1～3行目までは番号が入力されていないので、4行目から3を引いている）
そして、この”M”というのは見積番号をただの数字ではなく、文字列として認識させるために見積番号の頭につけているものです。
（数字として認識されてしまうと、一番初めの”0″が表示されなくなったりなど、不具合が生じるため。）
WS3.Cells(LCRN3, 1) = RefNum で、WS3のLCRN3行目の1列目に、見積番号を入力
WS3.Cells(LCRN3, 2) = Date で、WS3のLCRN3行目の2列目に、今日の日付（Date）を入力
WS3.Cells(LCRN3, 3) = WS1.Range(“D3”) で、WS3のLCRN3行目の3列目に、WS1（見積入力シート）のセルD3（見積先）を入力
WS3.Cells(LCRN3, 4) = WS1.Range(“H5”) で、WS3のLCRN3行目の4列目に、WS1（見積入力シート）のセルH5（合計金額）を入力
するようにして、番号簿も同時にメンテナンスがされています。
これで、見積書番号を取得する部分は完了です。
見積履歴を保存する
次は、見積履歴を保存しましょう。
コードの説明
ここは、For～Next の繰り返しが二つ出てきているので、少し難しいかもしれません。
見積番号と同様に、WS4という変数に、「見積履歴」というシートを割り当てています。
見積が入力されている範囲（最終行）をLCRN1という変数で取得して、明細が入力が始まっている8行目から、最終行まで、N1という変数で繰り返し処理をするようにしています。
見積履歴を追加する行は、明細ごとに変わってくるので、追加する行の取得（LCRN4）は、For～Nextの中に、繰り返しごとに取得するようにしています。
実際に見積書を作成する
いよいよ見積書を作っていきますね。
見積書を作る際は、原紙に直接書き込むのではなく、原紙をコピーしたシートを作って、そのコピーしたシートに書き込みます。
シートをコピーして、データを書き込む前の準備
Worksheets(“見積書原紙”).Copy で、見積書原紙というシートをコピーした「新規のブック」ができます。
（同じファイル内にシートをコピーする場合は、Copy の後に before や after で、コピーしたシートを入れ込む位置を指定します。Copyのみで直接ワークブックを作るのは、パソコンのメモリを食うのか、PCによってはエクセルファイルが落ちることがあるので、その場合は一旦シートを作りましょう。）
新しくできたブックと、元々の見積書作成ファイル（ブック）の2つのブックを操作することになるので、WB1 と WB2 という変数に、2つのブックをセットしておきます。
コピーしたシートは、見積番号をシート名にして、WS5という変数にセットしておきます。
見積データの書き込み
あとは、入力シート（WS1) のデータを、見積書のシート（WS5）に転記していくだけです。
1点、見積入力の際に、見積先があって見積先の担当者がいない場合もありますので、そこは If 文を使って御中を書き込みするかどうかを判断させています。
見積書を別のファイルとして保存する
作った見積書は、別ファイルとして保存するようにします。
（ここまで書いていて、見積の案件名をデータに入れておくのを忘れていたことに気づきました＾＾ 最終のコードでは直しています。）
保存はとても簡単ですね。
ファイル名の変数に、見積番号と見積先、案件名を入れ込みます。
保存は、SaveAs + ファイル名 で、現在のフォルダーに保存されます。
※保存先を特定のフォルダーにしたい場合は、そのフォルダーのパス名を入れるようにします。
なお、エクセルファイル形式だけでなく、PDFで保存したり、出力したPDFをOutlookに添付したりすることもできます。
必要な方は、調べてやってみてくださいね。
入力用のデータを削除する
これで見積書ができました。
次回の入力のために、今回入力したデータを削除（クリア）しておきましょう。
範囲の指定は、Range（開始セル,終了セル)で指定します。
入力してある値の削除（クリア）は、ClearContentsです。
単純にClearにすると、書式設定もクリアされるので、大体はClearContentsを使うことが多いと思います。
なお、セルが結合されているとClearContentsは使えないので、件名の部分は =”” で、空白値を入れるようにしています。
セル結合をしている場合は、注意してくださいね。
これで見積書作成マクロが完成したので、End Sub があります。
出来上がったコード
勉強のためには、一つ一つ書いてもらった方が良いのですが、出来上がったコードをダウンロードできるようにしておきますので、ご活用ください。
今回は学習用に作ったものなので、商品マスタの品番が増えたりした場合等には対応していません。
PDF変換したり、Outlookで見積書を自動送信したり、請求書を作る時に見積書を呼び出したり、頑張ればいろいろできますので、工夫してやってみてくださいね。
以上、これからマクロ、VBAを使って効率化を進めようとされている方のお役に立てれば幸いです。
また、弊社ではシステム開発も行っておりますので、
ダウンロードしたものでは不十分、カスタマイズして欲しいということであれば
お問合せフォームからお問合せ下さい。
弊社のシステム開発についてお知りになりたい方は、
下記より説明ページへ飛べます。
