ノン・プログラマー、仕事のためのマクロ・VBA（３）既存マクロと自動記録を流用して、即席入力フォームを作成

2021年10月25日2024年6月8日

これまでにも何度か事例として紹介してきた、交通費などの「経費精算」。企業活動で避けて通れない事務手続には違いないのですが、地味で面倒くさい作業ですよね。できれば省力化したい、と考えるのは自然なことです。

以前にも、Microsoft Formsを活用したスマホからの入力事例などをご紹介しましたが、インプット;Excel => アウトプット;Excelでも、総務担当者が定型入力フォームを用意して配っておけば、複数担当者からあがってくるデータ書式が乱れることがありません。

この記事では、前回作成した地下鉄の運賃表検索をするマクロに、統一した書式でレコードを流し込むための、入力フォーム作成についてご説明致します。キモは、自動記録と、これまでに作ったマクロの使い回し、です。

このマクロを使ってやりたいことは、

ノン・プログラマー、仕事のためのマクロ・VBA（３）既存マクロと自動記録を流用して、即席入力フォームを作成

総務が各営業マンに配った入力フォームに「目的地」「乗車日付」を入力して「転記」ボタンを押すと、精算票の【最終行】に、片道運賃とともに転記される、というものです。

この一連の作業に関するVBAマクロは、下記の通りとなります。詳しくみてみましょう。

Sub sheet_entry()

' sheet_entry Macro

'  入力フォームの「目的地」「乗車日付」をコピー

    Range("B7:C7").Select
    Selection.Copy
    
'  精算票の最終行を検索して、その1行下に入力フォームの「目的地」「乗車日付」を貼り付け
     
     Sheets("SpentWS").Select
     
     Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
   
'  転記された精算票の表示書式を統一。ここは「マクロの自動記録」からのコピー&ペースト
    
    Columns("B:B").Select
    Selection.NumberFormatLocal = "yyyy/m/d"

    
'  VLOOKUP関数に似た運賃検索を行う、以前作成のマクロをそのまま呼び出し
    
    Call Like_Vlookup
    
   Sheets("Form").Select
     
    Range("B7:C7").ClearContents
   
End Sub

入力フォームを作成したら、前回使用した「終端行を探す」VBAで精算票最終行へ自動転記

今回収集したい情報は、各営業マンの「目的地」と「乗車日付」なので、この2項目を入力できるワークシートを作成したら、［開発］→［ボタン］→［マクロの登録］で、2項目を精算票終端行へ自動コピーするVBAを登録します。2項目を精算票終端行の一行下へコピーする指示は、

    Range("B7:C7").Select
    Selection.Copy
    Sheets("SpentWS").Select
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues

となります。

これで、入力フォームB7セル(目的地)とC7セル(乗車日付)の情報がコピーされ、精算票(SpentWS)A列の終端行(の一列下)へ貼り付けられます。

あれ? 日付がシリアル値のままで転記されちゃいました。

コピー＆ペーストや、過去のプログラムも有効活用

B列の書式を整えるためには、その指示文を直接プログラムに書き加えれば良いのですが、簡単な方法としては、［マクロの自動記録］で生成されたプログラムをコピー&ペーストしてしまうことも可能です。

［開発］→［マクロの記録］→［OK］を押してから、B列全体を選択して、［セルの書式設定］→［日付］を選択。ここまでの操作をした上でマクロの記録終了をすると、Visual Basic Editorに、

    Columns("B:B").Select
    Selection.NumberFormatLocal = "yyyy/m/d"

というプログラムができます。これを、今回のプログラム(Form_entryと名づけました)の中に、コピー＆ペーストすればよいのです。

さらに、今回新たに入力フォームから追加したレコードには、片道運賃が記載されていません。これを運賃表から検索して読み込むためのプログラムは、前回作成していますので、それをそのまま使い回せば良いのです。

使い回しの構文は極めて簡単!

呼び出し; Call Like_Vlookup (※Like_Vlookupが、前回作成したマクロ名)と記述すれば良いのです。

これで、今回入力シートを使用して実現したいことが完了すべてしました。

実務では、真似ごと(コピー＆ペースト)や「使い回し」も時間短縮の重要なカギ

律儀な日本人気質からすると、人の作ったものの「真似ごと」や以前の資料の「使い回し」という言葉には、ちょっとネガティブなイメージを持ってしまうかもしれません。

けれども、ビジネスの現場では、「真似」「コピペ(コピー&ペースト)」「パクリ(使い回し)」で、仕事の効率がグングン上がります。もちろん、使用が許可されていない資料などの転用は不正となりますので、資料の内容・出典をしっかり調べる必要はありますが、「使えるものは親でも上司でも使え!?」の精神で、過去資料の転用、使い回しができないかを考えてみて下さい。

案外、新しい業務効率化のタネは、ずっと昔の資料の中にあったりするものですよ。

リンク