家計簿作成Part4〜VBAにてマクロ作成〜

VBA

いよいよ家計簿作成の最終パートです。

最後はマクロ作成をしていきます。

今回作成するマクロは3種類です。

  • ユーザーフォームを表示するマクロ
  • ユーザーフォームに入力した内容をシートへ転記するマクロ
  • ユーザーフォームを閉じるマクロ

マクロ作成の前に

標準モジュールの用意

まずは標準モジュールを用意しましょう。

標準モジュールとは、マクロ作成のためにVBAを記入する場所です。

Visual Basic のタブ「挿入」より標準モジュールをクリックすると、モジュールが用意されます。

参照設定

次に参照設定をしましょう。

参照設定とは、VBAで外部のアプリケーションやライブラリを利用する際に必要となるものです。例えば、OutlookをVBAで動かす際には、参照設定でOutlookを指定する必要があります。

ただし、作業の自動化をするだけなら参照設定について十分な理解をする必要はありません。

最初は、下記の5つを基本的には設定しておき、WordやOutlookを利用する際には追加すれば良いという認識で良いでしょう。

Visual Basicのタブ「ツール」より設定可能です。

ユーザーフォームの表示

ユーザーフォームを表示する

手順通りにユーザーフォームの表示マクロから作成します。

主な要素は以下の通りです。

  • マクロの開始・終了…『Sub「タイトル」〜 End Sub』
  • 日付の取得 と テキストボックスへの入力
  • コンボボックスの設定
  • ユーザーフォームの表示

最初は、詳細に把握する必要はありません。「大まかに各行が何の役割を持っているか」さえ理解できるようになれば実務上は問題ありません。

メソッドやプロパティなどを理解せずとも、ある程度コードの解読・作成はできるようになります。

本マクロの内容は上図でほぼ分かるはずです。

「各コンボボックスの入力内容を設定」について追加説明をします。

Withは同じオブジェクトに対しての処理を何度も行う際に、オブジェクトの入力を1回だけで済ませるものです。要するに、省略して楽をしたいだけです。

例えば、今回の例で言うと、オブジェクトは「UserForm1.ComboBox1」など4つのコンボボックスのことです。

そして、「UserForm1.ComboBox1」に対して、入力内容「+」と「-」を追加しています。

この際に、「UserForm1.ComboBox1」の記載を1つにまとめられているのは、Withの働きによるものです。

図形にマクロ登録

マクロ作成後、図形やショートカットにマクロを設定しておくと便利です。

今回は、図形にマクロ登録をします。

1 タブ「挿入」の図形で好みのものを配置

2 貼り付けた図形で右クリック「テキストの編集」で「入力」と記入

3 同様に右クリック「マクロの登録」

4 登録したいマクロを選択し「OK」をクリック

以上を行うと、図形をクリックするだけでユーザーフォームが表示されるようになります。

ユーザーフォームからシートへ転記

次にユーザーフォームに記入した内容をシート「マスター」へ記入するマクロについてです。

概要は下記の通りです。

変数の設定と代入について

変数でデータ型の設定について悩むことがあるかと思います。

最初のうちは、全ての変数に対してデータ型「Variant」を利用すると良いでしょう。

データ型Variantは全てのデータを受けることができるので、データ型によるエラーは発生せずに済みます。

※データ型を特定することで、予期せぬデータ入力があった場合にはエラーを発生させられるメリットがあります。例えば、「数字を入れたい変数なのに、文字を入力された」場合には、エラーが起き、マクロを停止させることできます。

また、代入は数学的に考えると混乱を招いてしまうことがあります。(実際、私がそうでした。)

「=」を「←」と読み替えるとイメージしやすいです。

例:VBAコード「Range(“A1”) = Range(“B5”) 」について(Rangeはセルの所在)

誤:「X = Y 」だから「XとY」は同義。セルA1とセルB5は同じ値が入力される

正:「X ← Y 」のイメージだから、セルB5の値をセルA1へ転記する

オートフィルター解除

最初のうちはオートフィルター解除のコードについては、上記のものを覚えて利用していいでしょう。

ここでは、なぜオートフィルター解除が重要なのか説明します。

もしフィルターでデータを絞った状態のままだと以下のような不都合が生じます。

  • 空白最終行の取得ができなくなる
  • 複数範囲のデータをまとめて貼り付けると狙った範囲に貼れない

以上より、オートフィルターを設定しているエクセル上でマクロ作成をする際には、必ずオートフィルター解除を組み込むべきです。

データ入力されている最終行の特定

データ入力するにあたって、シート「マスター」のデータ入力されていない行の内、1番上の行を特定したいです。

つまり、「何行目までデータが入力されているか」を特定すれば、その次の行がデータ入力するべき行と言えます。

下記がVBAで記入されたものの解説です。

(データ入力したい行)=(データ入力された最終行)+ 1

変数X:データ入力したい行

Cells(Rows.Count,1).End(xlUp).Row:データ入力された最終行

※Cells(Rows.Count,1).End(xlUp).Rowについて

Cells(Rows.Count,1)は「列Aの1番下のセル(例:セルA1048576)」を意味します。

「Endキー + ↑」を押すと、値が入力されたセルまで移動する機能があります。

セルA1048576から「Endキー + ↑」をすると、データ入力された最終行のセル番号(例:A10)が判明します。

なのでCells(Rows.Count,1).End(xlUp)はデータ入力された最終行のセル番号(例:A10)です。

ただし、今回知りたいのは行数なので、「Cells(Rows.Count,1).End(xlUp).Row」にすると行数が得られます(例:10)。

特定した行数を利用して、シートへ転記

先述した代入と同様にはなりますが、「特定のセルへ変数を入力」していきます。

セルの指定方法はRangeを使うと良いでしょう。

例えば、セルA1はRange(“A1”)として表現できます。

ただし、今回の場合はセルの行番号については変数を利用していますので以下のように表現されます。

Range(“A”&X):列AのX番目のセル(Xは変数)

ユーザーフォームを閉じ、ホーム指定

コード自体は解説するほどのものではないので、概要通りに入力してもらえれば良いです。

ここでは、なぜこのようなコードを記入したかについて説明します。

もしこのコードがないと

  • マクロ終了後にもユーザーフォームが開いたままになる(手動で消さないといけない)
  • 利用するシートは「ホーム」なのに「マスター」を開いたままマクロ終了する可能性がある(手動でホームに戻さないといけない)

といった2点の面倒が発生してしまいます。

ユーザーフォームを閉じる

ユーザーフォームを閉じるマクロは上記のものとなります。

1行で完結するものですので、他のマクロに組み込んでおけば良いような気もします。

今回は、便宜的に1行のマクロを用意し、他のマクロで利用する際にはCallで呼び出して利用してみました。

まとめ

これでマクロ作成も終わり、家計簿作成は終了です。

今までエクセルに触れたことがないと、大変に感じたかもしれません。他にもマクロ作成する機会があれば、案外慣れるものです。

しかし、慣れるのに必要なことは、マクロ作成だけではありません。

実際に作ったマクロを利用して、どんなシステムで動いているか意識することも慣れに繋がります。

システムを意識していると、どんな流れでコンピュータが動いているか、もっと改善できないか、様々なことに意識が向き始めるから、次第にマクロに慣れていきます。

なので、せっかく家計簿を作成した皆さんは、以下のことを意識しながら家計簿を運用しましょう。

  • どんな仕組みで入力・算出されているか
  • 改善点はないか、またどうしたら解決できるか
  • 追加したい機能はないか

事務員のエクセルクエスト」では、今回作成した家計簿をより便利なものに進化させられる情報を発信し続けていきますので、よろしくお願いします!

コメント

タイトルとURLをコピーしました