【エクセルVBA番外編】実務で使えるテクニック紹介します【最終行の取得・マイナスステップ】

みなさん、こんにちはケンケンです。

エクセルVBA入門編では、請求書作成を自動化してみました。

今回は、入門編でご紹介しきれなかったテクニックについて解説していきます。

入門編で作ったツールをカスタマイズする形で当記事は進めていきます。

番外編とはなっていますが、どれも重要なテクニックなのでしっかり勉強していきましょう。

最終行の取得方法

前回紹介した方法だとデータの最終行が事前にわかっている状態でした。

その最終行を直接コードに記述していましたね。

ただこの方法だと、ミスが起こりやすくコードを壊してしまう可能性があります。

コードは一度書いたら、メンテナンスをするとき以外は書き直さないのが原則です。

なので、常に変わる可能性のある値に対応できるコードを用意する必要があります。

その方法をご紹介します。

では「 For cnt = 2 To 16」を以下のように書き換えてください。

このコードの解説をする前にショートカットの解説をします。

実は、ショートカットを使って最終行を指定することができます。

Ctrl＋十字キーでデータの入っている塊の最後まで移動できます。

いかがですか。

それでは、今度は逆にデータが入っていないところからCtrl＋十字キーをするとどうなるか見ていきましょう。

適当なセルをクリックしてCtrl+↑を実行するとデータが入っている塊まで移動します。

なぜ、このショートカットを先に紹介したかというと、先ほどお見せしたコードが、このショートカットと同じ動作をするからです。

先ほどのコードを日本語に変換すると以下のようになります。

Worksheets(“請求データ”).Range(“A” & Rows.Count).End(xlUp).Row

請求データシートのA列1048576行目を起点としてCtrl＋↑を実行して最初にぶつかったデータがある行

rows.countはエクセルシートの最終行を検索します。エクセル2016では1048576行ですね。

そこから、Ctrl+↑を実行すると１６行目にぶつかり、その行数を取得します。

1048576行以上なければデータが何行あっても最終行を取得できて便利ですね。

また、増減があっても即対応できます。

■変数に格納するとスッキリする

最終行のコードは長いので変数に格納しておきましょう。

変数を使うとコードが見やすくなります。

変数「Saigo」を宣言しておいて、そこに最終行を格納しておけば、ForNext構文がすっきり書けます。

こうしておくと、あとでコードを読み返したときに思い出しやすく、メンテナンス効率も上がります。

それと、簡単にコメントを付しておくと便利です。

今回も変数に何が格納されているかサッとメモして、見返しやすくしておきました。

ちなみに、コードが見やすかったり人間が読んで理解しやすい状態を可読性が高いと言ったりします。

シートの削除

一度作ったシートを削除できるツールを作って行きましょう。

入門編では、一度作成したシートを手作業で削除していたかと思います。

これも自動化して、さらなる効率化を目指しましょう。

■シートの枚数を調べて削除しよう

シートの削除方法はいろいろあると思いますが、今回は、

1. 削除してはいけないシートの枚数を数える
2. その枚数以降のシートはすべて削除する

という手順で作成していこうと思います。

まず、今回削除してはいけないシートは何枚あるでしょうか。

４枚ですね。

つまり、５枚目以降のシートを削除すればうまくいきそうです。

それを前提にコードを書いてみます。

まず、以下の完成形をコピペして実行してみてください。

５枚目以降のシートが削除されればOKです。

Sub sheet_delete()
    Dim Scnt 'シートの枚数を取得
    Dim Dsheet '削除シートをカウント
    
    Scnt = Sheets.Count
    
    If Scnt <= 4 Then
        Exit Sub
    End If
    
    Application.DisplayAlerts = False
    For Dsheet = Scnt To 5 Step -1
        Worksheets(Dsheet).Delete
    Next
    Application.DisplayAlerts = True
    
End Sub

上記を分解してひとつずつ解説していきます。まず、

• 既存のシートの枚数を数えるために必要な変数Scnt
• 削除対象のシートをカウントするための変数Dsheet

を宣言しました。

それぞれの変数名は

Scnt = SheetCountの略

Dsheet = Delete Sheetの略

です。

そして変数Scntに今現在存在しているシートの枚数を数えて格納します。

次のコードはIf文を使って、もしシート枚数が４枚以下だったらコードを実行しない、という意味です。

Exit Subは、プロシージャを終了させるコードです。

今回は、以下のようにシートを９枚にしてから削除を実行してみます。

シートが９枚あるのでIf文はクリアしますね。

If文を無事通過すると次はシートを削除するコードを書きます。

削除のコードを見ていきましょう。

ここで見慣れないコードが出てきましたね。

Application.DisplayAlerts = False（True）

このコードは、以下のような確認画面を表示するか否かを設定するものです。

Application.DisplayAlertsをFalseにするとこれを表示しない設定にできます。

シートを削除し終わった後にTrueにすることで設定を戻しています。設定を戻さなくても問題はないのですが、戻すことが推奨されているので最後はTrueで締めるようにしてください。

さて、ここからはシート削除のコードを検証していきます。

ForNext構文で削除していきますが、ここでも見慣れないコードがありますね。

Step-1という部分が初めて出てきましたね。

実は、ForNext構文は「Step+増減値」を使うと増減値の間隔で処理を実行していきます。

具体例を以下にあげます。

上記２つのプロシージャを実行すると、それぞれA列B列に結果が表示されます。

B列（nantoka2）は増減値が２なので２つの間隔を空けて処理されます。

数値を変えていろいろ試してみましょう。

そして、このStepはマイナスの間隔も取れます。

つまり、最大値から最小値に向かってマイナスしていく感覚です。

ではなぜシートの削除だとマイナスしていくかと言いますと、プラスしていく方法では参照する値がなくなってエラーになってしまうからです。

プラスの方法で実行した場合を以下の画像で確認してみましょう。

ForNext構文の最初の値は５(枚目)で良いですね。４枚目までは消してはいけませんからね。

そして、Deleteメソッドを使って５枚目を削除します。

しかし、このまま処理を進めていくと途中で削除対象のシート枚数を変数Dsheetの数値が越えてしまい、削除実行できなくなってしまいエラーがでます。

どうしてこうなってしまったかを以下のような表にまとめました。

変数Dsheetは増加しているのに残っているシート枚数は減っていくので、削除対象のシートがなくなってしまっていますね。

このような現象を避けるために、マイナスステップを使っていきます。

上記コードは、最大値からマイナスしていくので削除対象のシート枚数を変数Dsheetの数値が越えることはありません。

上記の表のように数値が動くので、５枚目まで処理が可能になります。

どうですか、変数をマイナスさせる理由が分かりましたか。

使えるテクニックなので覚えておいてください。

Step以下の数値をいろいろ試してみるといいですよ。

ボタンの作り方

最後になりますが、ボタンの作り方をご紹介します。

ボタンを作ると視覚的に作業することができ、ユーザビリティが良くなります。

使わない手はないですね。

具体的に請求書作成ボタンを作って行きましょう。

手順は以下の通りです。

1. 開発タブ選択
2. 挿入を選択しフォームコントロールを開く
3. フォームコントロールからボタンを選択
4. シート上のボタンを作りたい箇所でドラック
5. マクロの登録ダイアログ画面で該当するプロシージャを選択
6. ボタン名を「請求書作成」に変更する

画像をお見せしますので参考にどうぞ。

この方法で作成し、実際にボタンを押して請求書が作成されるか確認してください。

そして、もうひとつ「シート削除」ボタンも作っておきましょう。

これで完成です。

交互にボタンを押して実行できることを確認してください。

まとめ

いかがでしたか。

今回は、

• 最終行の取得方法をショートカットと交えて解説
• ForNext構文をマイナスステップする方法
• ボタンの作り方

以上をご紹介しました。

どれも、実務で使えるものばかりですので、しっかり復習してどんどんスキルアップしてください。

今後もVBAで使えるテクニックは随時紹介していきますので、お楽しみに！