Excelでゆっくり育てる会計簿プログラム編

サンプルのブック（sampl-3.xls）から出発しして、いよいよマクロのモジュラーを書きます。

ここまでのワークシートの作り方は、ゆっくり育てる会計簿マクロ準備編を見てください。

VBAについての説明はところどころで触れますが、詳しいことは入門書で調べてください。

例えばWebに日経パソコンPC Online「「やさしく学ぶExcelVBA」（牧村あきこ）が連載されています。参考にしてください。http://blog.nikkeibp.co.jp/pconline/vba/2006/11/makimura1.html

メニューでクリックして入力する表へ

入力シートのメニューのボタンから、それぞれの入力する表へアクティブセルを移動させるマクロを作ります。

Excelにはマウスとキーボードでする動作を記録してマクロを作る機能（キー記録）がありますので試してみます。

マクロの記録

以上の操作で[Macro1]が記録されています。

[メニューへ戻る]をクリックしてセルA1へ戻り、メニューバーの［ツール］［マクロ］［マクロ］をクリックして、[Macro1]が選択されている状態で［実行］ボタンをクリックすると瞬時にセルC22が選択されたのが確認できます。

マクロがどんな風に記録されているか見てみます。

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2006/8/1  ユーザー名 : susumu
'
'
    ActiveWindow.SmallScroll Down:=15
    Range("C22").Select
End Sub

メニューバーの［ツール］［マクロ］［マクロ］をクリックして、、[Macro1]が選択されている状態で［編集］をクリックします。[Microsoft Visual Basic - sampl-4.xls]と言うタイトルバーのウインドウの中に［sampl-4.xls - Module1 (コード)］に左のように記録されていてここで編集することが出来ます。

１行目のSub Macro1()から最後のEnd Subまでがプロシージャ(Subプロシージャ)という一連の命令（コード）の集まりです。Macro1はマクロの記録でつけたマクロ名です。全角文字も使用できます。マクロを作るということはこのコードを記述することです。マクロの記録はコードの記述を簡便化したものです。複雑なコードはマクロの記録では得ることが出来ませんが、どのように書いたらよいのか参考になります。

「'」（アポストロフィ）のある行はコメントで動作に影響しません。行の終わりにもコメントを書くことが出来ます。説明を書いておくと後になってから何をするコードか判りやすくなります。

このプロシージャで実際に動くコードは、［ActiveWindow.SmallScroll Down:=15］と［Range("C22").Select］です。よく見ると「アクティブなウインドウを15行下へスクロールして」「セルC22を選択する」ということのようです。

［ActiveWindow]の中をマウスでクリックしてカーソルを置き、F1キーを押すと、ヘルプが起動してActiveWindow プロパティについ［最前面に表示されているアクティブ ウィンドウ (Window オブジェクト) を返します。］などと表示されます。プログラムを作るときの言葉で「取得します」とか「値を返します」と言う表現が良くあります。

これでは書いてある意味がわかりにくいのですが、次の［SmallScroll］のヘルプは、SmallScroll メソッドについて［Down 省略可能です。バリアント型 (Variant) の値を使用します。指定した行数分だけ、ウィンドウの文字列が下にスクロールします。］と比較的判りやすく？書かれています。

ActiveWindow プロパティとSmallScroll メソッドとの間に［ . ］（ピリオド）があります。プロパティは「特性・特質」、メソッドは「方法・方式」と言う意味がありますが、ここの書き方は、 [オブジェクト] . [メソッド(引数, ...)] と言う書式で書かれてあります。ActiveWindow はオブジェクト（物体・対象の意味がある）を返すプロパティで、最前面に表示されるアクティブWindowを返します。SmallScroll がメソッド、Down:=15は文字列を15行下へスクロールします。他にUp、ToRight、ToLeftがあります。

マクロの記録では、記録している間に余計な操作をするとそれも記録されますから、必要の無いものは削除して、動作に問題が無ければボタンに登録して実用に使用できます。

記録したマクロをボタンから起動

Private Sub CommandButton1_Click()

ActiveWindow.SmallScroll Down:=18
Range("C22").Select

End Sub

これでボタンにマクロが登録された筈です。試しにVBEの左上のアイコンかタスクバーでExcelの入力シートに戻って、コントロールボックスの［デザインモードの終了］のアイコンをクリックしてから、［１　入金］ボタンをクリックして見ます。

プロシージャの中は、コメントを除いて最初の１行がマクロの記録のときと違います。

ボタンには［１　入金］と書いてありますが、コードでは CommandButton1のボタンとして扱われています。そしてこのボタンをクリック(Click)したときに動作するマクロです。最初の［Private Sub］のPrivateは、同じモジュール(Module)内でのみ利用できるプロシージャです。（Public Sub はどのモジュールでも利用できます。）またプロシージャにはSubのほかにFunctionがあり関数に使われます。

ここまでのサンプルファイル　sampl-4.xls　96.5KB　圧縮ファイル　sampl-4.lzh 38.3KB

プロシージャとモジュールなど

プロシージャ(Procedure)とは、1 つの単位として実行処理されるコード（Code　命令を符号化したもの）の集まりをいいます。モジュールの中に書かれます。

モジュール(Module)はプロシージャの集まったもので、標準モジュールやワークシートのモジュール・ユーザフォームなどがあります。何れもVBAのコードを記録する場所です。モジュールの最初に宣言セクションがあって、その後にプロシージャが書かれます。

Excelのワークブックがプロジェクト(Project)で、モジュールの集まりです。プロジェクト名はブック名になります。

ＶＢＡ(Visual Basic for Applicationｓ Edition )を編集するＶＢＥ（Visual Basic Editor）では、コードウインドウでモジュールの内容を編集します。VBEにはコードウインドウのほかにプロジェクトエクスプローラー・プロパティウインドウ･イミディエイトウインドウ・ローカルウインドウ・ウォッチウインドウがあり、VBAを編集するのに便利になっています。

VBEで直接プロシージャを書く

Private Sub CommandButton1_Click()

    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollColumn = 1
    Cells(22, "c").Select
    
End Sub

［１　入金］ボタンと同じように他のボタンを設定しても良いのですが、入力する表に移動する別の方法としてマクロ記録ではなく直接コードを書く方法を試してみます。

マクロ記録は簡単でよいのですが、ソースコードを分析しないでそのまま使うだけでは進歩しません。直接コードを書くのは、プロパティやメソッドを知らなければらないし、コードを1字ずつ入力していくので大変ですが、手作業やマクロ記録では作られない動作をさせることが出来ます。

また、同じことをするにも色々な方法があります。どの方法が最適かはその場面場面により違うと思います。ボタンの動作などだけでなく、「セルに入力したデータを特定のシートの特定のセルに入力する。」などはコードを直接書かないと出来ないようです。

ActiveWindow.ScrollRow = 19　　'ウインドウの上端の行を19行に
ActiveWindow.ScrollColumn = 1　'ウインドウの左端の列番号をAに
Cells(22, "c").Select　　　　　'セルC22を選択する

実際に入力するときは、ActiveWindow.　までオブジェクトとピリオド入力すると、次に入力するプロパティの候補が表示されます。スペルを間違ったり、ピリオドを入れ忘れたりすると、VBEが判断してエラーが表示されます。

コントロールボックスの［デザインモードの終了］をクリックしてから、［１　入金］ボタンをクリックして期待通り動くことを確認したら、次の［２　出金］ボタンをダブルクリックして、VBEのウインドウに移ってまた３行を入力するのですが、コピー＆ペーストして一部修正するなど作業を簡易化できます。

［２　出金］以降ののボタンのマクロ

マクロ記録では２行で済んだものが、この方法では３行になります。他の表のプロシージャを作る際に、表ごとにスクロールする行数を調べる方が良いか、コピー貼り付けしたものをセルの位置で修正する方が良いか。ボタンにマクロを登録する方法がどちらが簡単か。等など、どう選択してよいか迷いますが、この方法で他のボタンをクリックしたときのマクロを作ることにします。

入力シートのメニューの表に作った［２　出金］ボタンをデザインモードでダブルクリックして、VBEの画面を出して・・・と、一つずつ作っていくことも出来ますが、［１　入金］ボタンのプロシージャが出来ていますから、これをVBEでコピー＆貼り付けをして編集して作る法が簡単と思います。

準備

入力シートのそれぞれの入力する表のセルを調べておきます。

コードの追加

Alt + F11（Altキーを押しながらF11を押す）でVBEを開きます。（メニュー［ツール］［マクロ］［Visual Basic Editor］でも同じ）開いた画面のタイトルバーには、[Microsoft Visual Basic - sampl-5.xls]となっていますから、VBAだけでなくVBも編集できるエディタのようです。その画面の右下の広い部分が［コードウインドウ］でプロシージャが書かれています。ここに残る５個のプロシージャを書きます。

Private Sub CommandButton1_Click()  '現金出納簿１　入金ボタン
    ActiveWindow.ScrollRow = 19     'ウインドウの上端の行を19行に
    ActiveWindow.ScrollColumn = 1   'ウインドウの左端の列番号をAに
    Cells(22, "c").Select          'セルC22を選択する
End Sub

Private Sub CommandButton1_Click() から End Subまでを選択してコピーし、End Subの下に５個順に貼り付けます。

同じプロシージャが6個並びましたが、2個目以降の次の部分（右表の強調された部分）を修正してそれぞれのボタンのマクロを作ります。

確認

各プロシージャの修正が出来たら、VBEの画面で念のためにツールバーのリセット(四角のボタン)をクリックし、コードウインドウの1行目の中にカーソルを置いて下向き矢印キーで1行ずつ下へ移動してみます。構文上に問題があれば何か表示されますが、問題が無ければExcelに戻り各ボタンをクリックして目的の最初に入力セルに移動するか試してみます。入力メニューへはハイパーリンクで戻られます。この部分をボタンにすることも可能です。

VBEではコードが間違っていたり、矛盾があるとデバックを促します。簡単なものでは、指定された行に問題がありますが、複雑になると指定されたところ以前の行に問題があることがあります。プログラム作りでは間違いや矛盾はあるのが当たり前で、デバックが上手にできるようになれば上達したことになるのだそうです。

ここまでのサンプルファイル　sampl-5.xls　97.0KB　圧縮ファイル　sampl-5.lzh 38.4KB

使用する変数

sampl-5.xlsを開いてそのままファイル名をsampl-6.xlsで保存しておきます。

変数の宣言を強制する設定

VBAだけでなくプログラムを作るときは、さまざまなデータを[変数]という入れ物に入れて、パソコンの中で処理されます。［４月１日］とか［3,500］というデータをそのまま直接セルに入力するのではなく、［日付］［金額］と言った名前をつけた入れ物に入れてやり取りされるのです。

VBAでは簡単なコードの場合は変数をあらかじめ定めていなくても自動的にバリアント型(Variant)に設定され動きますが、変数の宣言をしておいた方が安定した動作を期待出来ます。どういうデータをどういう名前の入れ物に入れるか、データの型はどんなものか、その変数はどの範囲で有効なのか、などをモジュールやプロシージャの最初に宣言するのです。

プロシージャの中だけで通用する変数は、そのプロシージャの先頭に宣言すればよいのですが、プロシージャの集まりであるモジュール内で変数を有効にしておくにはモジュールの最初に宣言しておく必要があります。その場合、同じ変数を複数のプロシージャで使用する時は、使用前に初期化するとか値を確認するとかの注意が必要です。

VBEは初期値では変数の宣言を強制していません。オプションの設定で宣言しない変数の使用を制限できます。

VBEのメニューバーで［ツール］［オプション］の［編集］タブで、［変数の宣言を強制する］のチェックボックスにチェックを入れます。

この設定はこのモジュールやこのブックだけでなくこのパソコンのExcelの設定です。他のブックのマクロでも変数を強制されます。

これで新しく作るモジュールなどで最初に［Option Explicit］が入りますが。既に作ってあるモジュールは最初に行を作り［Option Explicit］と入力しておきます。

参考：ここまでのVBEのコードウインドウに書かれているコード

変数の種類とデータ型

会計簿に入力するデータをまとめて見ます。[１入金]〜［６修正］のセル番号は入力シートで入力するデータのあるセルです。

変数名には最初の文字は英字にし、2字以降は英字・数字・アンダースコア " _ " が使える。大文字小文字は区別されない。予約語(アプリケーションで使われている言葉)は使えないが、含むことは出来る。などの決まりがあります。名前の付け方に一定のルールで付けると判りやすいそうですが、ここでは気にしないことにします。

上表の入力するデータのほかに、操作をするセルを特定するために変数を使います。変数名［targetCall］、オブジェクトの種類[Range]を宣言します。

変数の宣言

今までは［デザインモード］のときにボタンをダブルクリックしてVBE（Visual Basic Editor）を開きましたが、変数の宣言は直接VBEを起動してコードを書きます。sampl-6.xls を開き（マクロを有効にします）［入力］シートをアクティブにして、メニューバーの［ツール］［マクロ］［Visual Basic Editor］をクリックするか、Alt キーを押しながら F11 を押すとVBEが起動します。

多分VBEの画面には、中央の大きいコードウインドウ（タイトルバーは[sampl-6.xls-Sheet1(コード)]）には、Option Explicit　以下のコードが書かれ、左側のプロジェクトエクスプローラーウインドウはSheet1(入力)が選択されていると思います。もし表示されていなかったら、左側のプロジェクトエクスプローラーウインドウは、メニューバーの［表示］［プロジェクトエクスプローラー］をクリックします。コードウインドウは、プロジェクトエクスプローラーで［Sheet1(入力)］が選択されている状態で、このウインドウのツールバーの［コードの表示］をリックします。

変数の宣言には、Dim ステートメント(命令文)を使います。Dim ステートメントを書く場所によって効力の範囲が違います。プロシージャの中の最初に書くとそのプロシージャの中だけで効力があります。モジュールの先頭に書くとそのモジュールの中で有効になります。ここでは、モジュールの最初の［Option Explicit］の下に次のように入力します。

Dim targetCell As Range
Dim da As Date
Dim number1 As Integer, number2 As Integer, number3 As Integer, number4 As Integer
Dim item1 As String, item2 As String, item3 As String
Dim penny1 As Long, penny2 As Long, penny3 As Long
Dim note As String

書式は　Dim 変数名 As データ型　となりますが、1行に複数並べる時はカンマ（ , ）で区切って、変数名 As データ型と続けます。

実際に入力するときは、［Option Explicit］の下の横線の下に入力し終わると横線が下に移動します。小文字で入力しても最初の文字を大文字に変えてくれます。[as]を入力して半角のスペースを入れると、データ型の候補リストが出ます。打ち続けると1字ごとに目的のデータ型に近づきますので、スペルに気を使わなくて済みます。変数名はユーザーが決めるものですから、間違わないように入力します。

もし、間違ってここに宣言した変数名以外の変数を使用するとエラーになります。また、宣言したもの以外のデータ型のデータを代入しようとするとエラーになります。

ここまでのサンプルファイル　sampl-6.xls　88.5KB　圧縮ファイル　sampl-6.lzh 35.9KB

［入金入力］ボタンをクリックしたとき

[１ 入金]の表で、テキストボックスにそれぞれのデータを入力してから［入金入力］をクリックして、それぞれのシートの中の該当セルにデータを入力するには、マクロにどういう事をしてもらわなければならないかを考えて見ます。

これをコードに書き込むのですが、3番目の入力する行の［日付］列のセル　を探すのが、手動で操作するときはマウスでセルをクリックだけですが、マクロで動かすには手順を踏まなければなりませんから少し面倒です。

入力する行を探す

紙の帳簿に手書きするときは、既にデータが書いてある行の次の行に書き込みます。Excelでも手動で入力するときは、マウスで該当の空いているセルをクリックするのですが、マクロの場合入力されている最下行を探すには、一旦シートの下の行（例えば1000行）に移ってからデータのあるセルまで上ってくる方法があります。しかし、日付を遡って入力をしたい場合があるときはこの方法は利用できません。

［月日］（変数da）を、既に入力されている月日と比較して、変数daよりも多くなるか空白のセルを探し、空白行を挿入してその行のA列にに変数daを入力して、右方向に移動して関連するデータを入力するようにします。

アーキテクチャーというほど大袈裟なものではありませんけれど、新しいマクロを作るときに最初からこんな風に計画して出来ればよいのでしょうが、実際には試行錯誤をくりかえすことになります。苦労するだけ出来上がった達成感は格別です。

具体的には、Do.....Loop ステートメントを使います。

Do While .....Loop 指定した条件である間処理を繰り返す。

Do Until .....Loop 指定した条件になるまで処理を繰り返す。　　こちらを使います。

日付はA列です。セルA6を選択しアクティブセルを1行ずつ下に移動してセルの内容を入力された変数daと比較して、値が大きくなるか空白の場所まで移動します。空白の場合はそのまま入力できますが、初めて値が大きくなったセルの場合は、そこにデータが入っているので入力できませんから新たに空白行を挿入して出来た空白の行に入力するようにします。

どのシートでも先ず日付（変数da）を入力する場所を探して入力するのですから、このマクロは独立したものを別に作り必要なときに呼び出すようにすれば、同じコードを何回も書かなくて済みますし、多分ファイルサイズも小さくなるはずです。それでは日付入力プロシージャを作って見ます。

これで今まで作ってきたプロシージャの後に　Private Sub 日付入力( )　と　End Sub　が入力され、その間にコードを書くことができるようになりました。プロシージャの名前に全角文字を使用することが出来ます。コードは次のように入力します。

条件式
　targetCell.Value > da Or 入力された日付よりも遅い日付か或いは
　targetCell.Value = ""　　空白のセル

targetCell.Offset(1, 0)　 targetCellを相対的に1行下の行のセルに
targetCell.Offset(-1, 0)　では、相対的に1行上の行のセルへ移動します。

targetCell.Value = da　　 等号（イコール）ですが、ここでは変数daの値を
　　　　　　　　　　　　　targetCellの内容として格納しています。

Private Sub 日付入力(targetCell , da)
Do Until targetCell.Value > da Or targetCell.Value = ""
　　　　　　'targetCellの値が変数daより多いか空白になるまで
    Set targetCell = targetCell.Offset(1, 0) '1行ずつ下がっていく
Loop
targetCell.Select                    '条件に合っているセルを選択
Selection.EntireRow.Insert           '行を挿入
Set targetCell = targetCell.Offset(-1, 0) '1行上へ移動して
targetCell.Value = da                     '変数daを入力
End Sub

最初の行のPrivate Sub 日付入力( )　の括弧の中に　targetCell , da　と入力されています。これはこのプロシージャを呼び出すプロシージャで使っていた変数を引き継いで、このプロシージャで使うためです。

ここで困ったことがあります。Selection.EntireRow.Insert で1行挿入されるのですが、最初のtargetCellのセルA6の場合、上の行の書式が挿入された行に継承され青い背景色になってしまいます。マクロの記録によって挿入した行の下の行の書式を適用する方法が見つかりました。この行に次のように追加します。

Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromRightOrBelow

,CopyOrigin:=xlFormatFromRightOrBelow
[:=xl・・・]は最初のキー記録のところでも出ていました。
VBAの入門書では説明を見つけられませんでした。
[xlFormatFromRightOrBelow]はExcelの定数で、
右か下のセルの書式をさしているようです。
[:=]は名前付き引数といい、
この場合前の[Insertメソッド]の引数を書き順に拘らないで
書く方法のようです。
入門書やヘルプなどを調べてもよく理解できません。
マクロの記録で書かれたコードのまま使います。

今まで触れてありませんでしたが、VBEでコードウインドウに書いたものはワークプックのファイルに含まれています。ブックを上書き保存をすればシートと一緒に保存されます。「シートは変更しなかったから・・」といって保存をしないと苦労して書いたコードは失われます。VBEの画面でも上書き保存をすることが出来ます。

イベントプロシージャ

今まではデザインモードでボタンをクリックして、VBEのコードウインドに新しいプロシージャを作って来ました。［入金入力］ボタンについても同じで、デザインモードでボタンをダブルクリックすればPrivate Sub CommandButton7_Click()　というプロシージャが出来ます。

これらのプロシージャは、イベントプロシージャ　と呼ばれています。この場合は「ボタンをクリックした」というイベントがあった時に自動的に動作するマクロです。

イベントは「あるセルがアクティブになった」「○キーが叩かれた」「マウスでクリックされた」などのときに発生します。イベントが発生したときにコンピュータにやって貰いたい事をコードに書くわけです。イベント一つずつにプロシージャが作られます。

イベントは人がExcelを操作しているときに発生するだけでなく、マクロを実行する中でイベントを発生させることが出来ます。

データを変数に

［入金入力］のボタンをクリックしたときに、先ず表に入力してあるデータを変数に代入します。

Private Sub CommandButton7_Click()  '入金入力ボタンをクリックしてイベントが発生
Worksheets("入力").Activate         'ワークシート入力をアクティブに
    da = Range("C22").Value         '日付を変数daに代入
    number1 = Range("C24").Value    '収入科目を変数number1に代入
    item1 = Range("C25").Value      '入金項目を変数item1に代入
    penny1 = Range("C26").Value     '入金金額を変数penny1に代入
    note = Range("C27").Value       '備考を変数noteに代入

Worksheets("入力").Activate　は、［入金入力］のボタンをクリックしたのだから、入力シートがアクティブになっている筈で不要かもしれませんし、Activate と Select はどう違うのか？など今のところよくわかりません。

このプロシージャで使う変数について入力されたセルから代入します。この辺の作業は変数の種類とデータ型の表を参考にして作ることが出来ます。

ワークシートにデータを入力するコード

シート収入内訳の日付列の最初のセルA6をtargetCell にします。
Worksheets("収入内訳").Range("A6")
　　オブジェクト . Range(Cell)　という書き方でセルを特定します。
Call 日付入力(targetCell, da)
　　日付入力プロシージャを呼び出します。
　　このプロシージャで使っている変数(targetCell,da)を引き渡します。

Worksheets("収入内訳").Activate
Set targetCell = Worksheets("収入内訳").Range("A6")
Call 日付入力(targetCell, da)

Select Case number1
    Case 1 '会費の場合
        Set targetCell = targetCell.Offset(0, 1)　　'targetCellを日付から1列右へ移動して会費の項目列へ
            targetCell.Value = item1　　　　　　　　'targetCellの内容に変数item1を入力
        Set targetCell = targetCell.Offset(0, 1)　　'targetCellを1列右へ移動して会費の金額列へ
            targetCell.Value = penny1　　　　　　　 'targetCellの内容に変数penny1を入力
    Case 2 '寄付金の場合
        Set targetCell = targetCell.Offset(0, 3)　　'targetCellを日付から3列右の寄付金の項目へ
            targetCell.Value = item1　　　　　　　　'targetCellの内容に変数item1を入力
        Set targetCell = targetCell.Offset(0, 1)　　'targetCellを1列右へ移動して寄付金の金額列へ
            targetCell.Value = penny1　　　　　　　 'targetCellの内容に変数penny1を入力
    Case 3 '雑収入の場合
        Set targetCell = targetCell.Offset(0, 5)　　'targetCellを日付から5列右の雑収入の項目へ
            targetCell.Value = item1　　　　　　　　'targetCellの内容に変数item1を入力
        Set targetCell = targetCell.Offset(0, 1)　　'targetCellを1列右へ移動して雑収入の金額列へ
            targetCell.Value = penny1　　　　　　　 'targetCellの内容に変数penny1を入力
    End Select

Worksheets("現金出納").Activate　　　　　　　　　　　　'ワークシート現金出納をアクティブに
Set targetCell = Worksheets("現金出納").Range("A6")    '日付の最初のセルを変数targetCellに代入
Call 日付入力(targetCell, da)      　　　　　　　　　　'日付入力プロシージャを呼び出し、変数targetCell,daを引き渡す。
Set targetCell = targetCell.Offset(0, 1)  　 　　　　　'targetCellを1列右へ移動して項目列へ
    targetCell.Value = item1               　　　　　　'targetCellの内容に変数item1を入力
Set targetCell = targetCell.Offset(0, 1)   　　　　　　'targetCellを1列右へ移動して入金額列へ
    targetCell.Value = penny1              　　　　　　'targetCellの内容に変数penny1を入力
Set targetCell = targetCell.Offset(0, 3)   　　　　　　'targetCellを3列右へ移動して備考の列へ
    targetCell.Value = note                  　　　　　'targetCellの内容に変数note を入力
End Sub

以上で［入金入力]ボタンをクリックしたときのイベントプロシージャが一先ず完成です。

［入力取消］ボタン

Private Sub CommandButton8_Click()                      '入力取消ボタンクリック
    Worksheets("入力").Range("C22,C24:C27").Clear       '入力したデータを削除する
End Sub

データを入力してみます

ここまでのサンプルファイルです。sampl-7.xls 107KB　圧縮sampl-7.lzh 43.5KB

［出金入力］ボタン以降のコード

この項の話ではないのですが、元のExcelのシートですが、該当の日付の行を挿入してデータを入力するのですから、列の合計欄の式を50行まで計算する必要が無いことに気が付きました。

例えば現金出納シートの場合表は10行までにして、セルC4には［=SUM(C5:C10)］と入力しておいても良いことになります。（データを入力する行は5行になりますが、データが入力されれば行が増えます。）

［出金入力］ボタンをクリック

デザインモードで出金入力ボタンをダブルクリックすると、VBEに　Private Sub CommandButton9_Click()のプロシージャが出来ます。入金入力のボタンをクリックしたときのコードをコピーして貼り付けて、変数などを変更すれば出来上がると思います。変更する箇所は概ね次のとおり。

［入力取消］のコードを書いてから、仮のデータを入力して動作を確かめます。

宣言してある変数名と違う文字を変数に使うとエラーになります。エラーのウインドウには修正のヒントが書かれています。「デバッグ」は間違いを直すことです。修正してからVBEのツールバーの［マクロの実行］（右向き三角）をクリックすると画面にエラーが表示されなければ、Excelに戻るとマクロが実行されたことがわかります。

振替口座のボタンのマクロ

振替口座関係の入力は、会費、一般のほか振替口座・現金出納簿間の移動がありますが、何れもコードは現金出納簿の入金・出金の例によればよいので手数はかかりますが、余り問題ないと思います。

［振替　一般］は入金と出金を別々に入力されるので入力ボタンも分けることにします。当然プロシージャも別になります。

また、振替・現金間の移動は移動の方向別にボタンがありますので、プロシージャが別になります。

既に出来ているプロシージャからコピーして貼り付け、必要な変更を繰り返して作っていけますが、変更する部分を間違わないように注意します。実際にデータを入れてみて問題なく動くか確かめます。

入力が無事終わったことがわかるように、入力のプロシージャの最後にメッセージボックスを出すようにします。

MsgBox ("入力完了。" & vbCrLf & "振替・収入内訳・支出内訳シートを確認してください。")

MsgBox は関数です。括弧の中の引数に表示する文字列を入れます。文字列の場合は［""］ダブルクォーテーションで囲みます。複数の文字列にするときは文字列を［&］アンパサンドでつなぎ、間に改行のコード［Chr(13)］または定数［vbCrLf］(vbCRでもよいようです)を挟みます。

デバッグ

コードを書いてデータを入力してボタンをクリックて、順調に完了のメッセージボックスが表示されるのは稀だと思います。手で入力していると何処かで間違ってしまいます。

コードは変数以外を小文字で入力したときに、スペルに間違いがなければ、VBEが先頭の文字を大文字に変えてくれます。変数以外が小文字のままだったらVBAがわからないコードを入力していた事になります。

コンパイル
人間が書いたソースコードをコンピュータが処理できる形式(オブジェクトコード)に変換すること。
そのためのソフトをコンパイラーという。

VBEは入力しているときに大きな矛盾があるときは、マクロを実行しなくても警告してくれる事があります。「コンパイルエラー」と表示されることがあります。「VBA はインタプリタで、ソースコードを１行ずつ実行するときに機械語に直して実行する」と聞いていたのですが、どうも違うようです。

入力して確定した段階で間違いが見つかるのは便利です。VBEの初期設定がそのようになっていました。Excelの画面で Alt + F11 キーでVBEを開き、メニューバーの［ツール］［オプション］の［全般］タブをクリックすると、右下の[コンパイル]にある［順次コンパイル］［バックグランド　コンパイル］のチェックボックスにチェックが入っていす。親切に出来ています。

もし、［順次コンパイル］にチェックを入れない場合は、VBEのメニューバーで［デバッグ］［VBAProjectのコンパイル］をクリックすれば、コンパイルされて構文に問題があればエラーが表示される筈です。構文にエラーがなければ正常に動くという訳ではありません。

デバッグ
プログラムの間違いをバグ(bug)といい、バグを探して修正することをデバッグ(debug)という。
bugとは小さい虫・昆虫の意。
1947年にハーバード大学で海軍研究所に納めるコンピュータ（初期の真空管式）をテストしているときに不具合がありスイッチの間に挟まっていた蛾が発見されたという。これがバグの第一号。

コードを書いて一区切りしたら、仮のデータを入れて入力ボタンを押してマクロを実行して見ます。順調に動いてしまうと余り勉強になりません。

途中で止まってしまい、入力完了のメッセージボックスが出なく、何か警告かお知らせのウインドウが出たときは、何が書いてあるのか判らなくても読みます。（「今わからなくてもキット判るようになる」と思って）

VBAはたとえ間違ったコードでも、プロシージャの上から順に書いてあるとおりに実行して、動きが取れなくなるまで実行して止まります。ですから止まった所に間違いがあるとは限りませんが、止まったところまでの間に間違いがあります。VBE上には黄色く表示されている行やカーソルが止まっているところは、VBAが「問題あり」と判断しているところだと思います。

私がよく間違ったのは、変数名・アクティブにしているシートやセル名です。

VBEにはデバッグするための準備がされています。

デバッグ・ツールバー

VBEのメニューバーで［表示］［ツールバー］［デバッグ］をクリックすると、デバックツールバーが現れます。このまま使うことが出来ますが、VBEウインドウの右側に縦に表示させると邪魔になりません。

バーの左端をポイントするとマウスポインタが十字の矢印になります。そのままドラッグしてウインドウの右端までドラッグします。バーが縦になったらマウスのボタンを離します。

ステップイン

プロシージャを一行ずつ実行して、問題のあるところを探すことができます。

ブレークポイント

長いプロシージャでは、問題のないところを一行ずつステップインするのは大変です。途中にブレークポイントを設定してボタンをクリックして実行すると、ブレークポイントで停止しますから、それ以降でステップインを実行できます。

やり方は、ブレークインする行にカーソルを入れてデバッグツールバーの［ブレークポイント］をクリックします。

イミディエイト・ウインドウ

デバッグツールバーの［イミディエイト］をクリックすると、コードウインドウの下にイミディエイトウインドウが表示されます。VBEのメニューバーの［表示］［イミディエイトウインドウ］でも表示できます。

このウインドウでは、コードを入力してEnterキーを押せばそのコードを実行します。「一寸試してみる」ことができます。

このイミディエイトウインドウで、現在の変数の値を［Print 変数名］で確かめることが出来ます。[Print]は「表示せよ」という命令でしよう。［?変数名］でも同じです。これでマクロがうまく動かないときに、ブレークポインタを入れたときやスティプイン中の変数の値を調べて、コードの間違ったところを探すことが出来ます。

ウォッチ・ウインドウ

デバッグツールバーの［ウォッチウインドウ］をクリックすると、コードウインドウの下にウォッチウインドウが表示されます。VBEのメニューバーの［表示］［ウォッチウインドウ］でも表示できます。

プロシージャから変数をこのウォッチウインドウにドラック&ドロップします。VBEのメニューバーの［デバッグ］［ウォッチ式の追加］でもウォッチウインドウに追加できます。

ステップインを実行すると変数などが変化するのが判ります。

入力済みデータの削除のマクロ

いくらマクロで自動的に入力するとは言っても、間違って入力ボタンを押してしまうことがあります。その時に手動でそれぞれのシートを修正すると、修正漏れや修正の間違いが出ます。そこで修正もマクロでやって見ます。

マクロで間違ったところを探して修正すればよいのですが、面倒ですから入力したものを全部削除して、正しいデータを入力し直すことにします。方法はシートごとに削除する行の日付のセルをアクティブにして、作業番号を入力しボタンをクリックです。

If da2 And da3 = da Then 
     Worksheets("振替").Activate
        ActiveCell.EntireRow.Delete
            Worksheets("収入内訳").Activate
        ActiveCell.EntireRow.Delete
            Worksheets("支出内訳").Activate
        ActiveCell.EntireRow.Delete
    Else
            MsgBox "違う行が選択されています。" & vbCrLf & "確認してください。", _
            Buttons:=vbCritical, Title:="警告！"
End If

このときに各シートごとにアクティブにしたセルに間違いが無いか　調べます。

If 条件式（日付の変数の内容が同じか） Then
  条件を満たす場合の処理（日付の変数の内容場合の処理）
  Eise
     条件を満たさないと切り処理（日付の変数の内容が違う場合の処理）
End IF

書き方は違いますが、ワークシートのIf関数と使い方は同じです。MsgBoxの引数の中にある半角アンダースコア［ _ ］は、1行に入力すべきところを画面の都合により改行して書くときに使います。

様々な手直し−−−永遠のベータ版

仮にデータを入力して実際に動かしてみると、いろいろな問題が生じてきます。マクロだけでなくワークシートの不都合も見えてきます。実際に会計簿を使用するとさらに使い勝手で修正したくなってきます。

自分が作った会計簿でここまで育ててきたのですから、出来るだけ良いものにしたい気持ちになります。磨きをかけるのはこれからです。他人が作ったプログラムを理解するのは非常に努力と知識が必要になりますが、自分が作ったものは要所要所にコメントを入れておけば小さな修正は難しくありません。

データ削除の方法

入力・削除を繰り返していると、入力するセルに入っていた罫線がなくなり、金額は3桁ごとのカンマが入らなくなったことに気がつきます。単に［Clearメソッド］では書式も削除されてしまうのでした。

VBEの入力取消ボタンのプロシージャで［Clear］の中にカーソルを入れてF1キーを押すと、Microsoft Visual Basic のヘルプが起動して、［Clearメソッド］についての説明を見ることが出来ます。VBA・Excel・Office・MSFormsからExcelを選んで、しばらく待つとヘルプが表示されます。この場合Rangeオブジェクトに対してClearメソッドを使っていますので、［オブジェクト全体をクリアします。］でした。

「それならどうしたら良いのか｣がわからないとヘルプになりません。ヘルプの上のほうに［関連項目］がありました。クリックすると[ClearContentsメソッド]と[ClearFormatsメソッド]があります。ClearFormatsは書式のクリアでしょうから、ClearContentsが書式を残してデータを消す目的が果たせそうです。

入力を確定してボタン

データを入力してボタンをクリックしても動作しないことがありました。原因は最後のセルに入力してから確定してなかったためです。必要ないのにクリックするのは「悪い癖」なのですが、変換の必要ない半角の数字を入力してもEnterキーで確定しないと、次の動作に入られません。これはマクロの書き方が悪いわけではありませんが、入力の表に「入力を確定してからボタンをクリックしてください。」と表示しました。

現金出納に科目欄を設ける

現金出納は金額のほかに項目と備考がありますが、科目が入っていないと判り難いので、ワークシートに新しい列を作り［科目］欄を設け関係する［入力ボタン］と｛修正　誤入力行の削除ボタン］のプロシージャを変更します。マクロを書く前に気が付けば良かったのですが、かなり手数がかかります。

［科目］の入力は、数字で入力されているので、数字（number1,number2）を元にして新しい変数（subjectにしてモジュールの最初の宣言に書き加えました）に文字列を代入することにします。

収入内訳書・支出内訳書に入力するときに、例えば
Private Sub CommandButton7_Click()　プロシージャで
Select Case number1
	Case 1 
		Set targetCell = targetCell.Offset(0, 1)
			targetCell.Value = item1
		Set targetCell = targetCell.Offset(0, 1)
			argetCell.Value = penny1
と書きましたが、ここへ
		subject = "会費"

と書き加えます。これで変数 subject に科目が代入されますので、収入内訳・支出内訳の入力で次のように科目に入力します。

Set targetCell = targetCell.Offset(0, 1)
	targetCell.Value = subject  

残っている問題

気持ちの持ち方にもよりますが、次々と直したり追加したくなります。

だから進歩があるのだと思っています。永遠のベータ版です。

現金出納と振替口座を纏める

会計の出入りが現金出納と振替口座の2つに分かれているのは、この会計の内容を一覧するのには不都合です。会計全体の出納がわかる表が欲しくなります。

今までマクロを作ってきた方法でワークシートを一つ作り入出力のプロシージャに少し書き足せば実現出来そうです。

ワークシートは現金出納のシートを流用できます。現金出納のワークシートをアクティブにして、メニューの［編集］［シートの移動またはコピー］をクリック、［移動先］に［（末尾に移動）］を選択して、［コピーを作成する］にチックを入れて、［OK］ボタンをクリックすると現金出納（2）のシートが出来ます。

シート名とセルA1を「金銭出納簿」に変更してワークシートは追加されました。

マクロの方はAlt + F11 でVBEを起動して各入力ボタンのプロシージャに、現金出納の部分のWorksheets("現金出納")をWorksheets("金銭出納簿")に変更したものを追加コピーすれば、出来上がるのですが、［会費］が同時に入金・出金を入力しますから、［項目］欄は入金と出金を分けた方がよいのでワークシートから修正しなければなりません。簡単にできると思いましたがかなり手数がかかります。

入金項目(item1)・出金項目（item2）のデータはありますが、入力するセルが相対的にセルを移動していますので、挿入した列以降のtargetCell.Offset(0,?)の引数を変更しなければなりません。

シートに保護をかけて一先ず完成

多分まだ手直しをする必要があると思いますが、一先ず完成したことにします。

この会計簿はマクロだけで操作をすることにして、入力するセル以外は入力・変更できないように保護をかけます。

保護は入力するセルのロックを外して、シートごとに保護を設定します。

ここまでのサンプルファイルです。　sampl-8.xls 186KB　圧縮sampl-8.lzh 65.2KB
　サンプルコードsamplcode.txt 41.4KB

2006/9/4　UP