「匠への技」VOL-2 パソコンレジスターマクロ解剖!


Excel VBAあらかると

「匠への技」vol-2 パソコンレジスターのマクロは3つのボタンとリンクしています。

 画面デザインと機能の検証

@ 売上入力欄で明細を打鍵します。
A Pushボタンをクリックしレシートセルへ転記します。
  商品入力が完了するまで@Aを繰り返します。
B 全ての商品の打鍵を完了したらRegiボタンをクリックします。
Cイレギュラー入力を行った場合は、Cancelボタンでレシート明細を初期化します。

以上が匠への技vol-2で作成したパソコンレジスターの機能です。
 設計の心得 
  プログラムを組む場合は、画面デザインと機能の検証で書き記した内容のようなものを頭で描いたり紙に書いたりします。その内容がプログラムをコードする上での仕様書になりマクロコードに書き換えされます。
まず、何をしたいのかを明確にすることが先決であり、そこからプロトタイプを始めます。フローチャートを書くことを覚えるとマクロのコード化はとても簡単に書くことができます。

@入力内容を決める。  (インプットをデザインする。)
A結果何を求めるかを明確にする。 (アウトプットをデザインする。)
Bインプットからアウトプットに至るまでのブラックボックスを決める。 (更新処理(プロセス)をデザインする。)

システムを設計する場合に正確には概要設計から始まり、5つの工程を経て製造が開始されますが、現在はプロトタイピングの時代であり、まず作って試行を繰り返し完全なシステムを完成させる。といった手法をとります。
作り方は、人によって千差万別ですので自分が納得いくやり方を身につけて取り組めば問題はないと思います。
ここは、余計なことを書き記してしまったようです。


 プログラムの構成 
 

3つのマクロで構成されており各々が3つのボタンとリンクされています。
機能ボタン リンクマクロ 機能
data_catalog 売上入力セルに打鍵したデータをレシート明細に転記する。
db_catalog レシート明細を累積用シート「DB」へ転記する。
cancel_regi 誤って売上入力セルに打鍵したレシート明細を消去する。


@図形とマクロのリンク方法
   関連づけさせたい図形をマウスでポイント、右クリックしショートカットメニューから「マクロの登録」を選択します。


Aマクロ登録パネルから対象となるマクロをクリックする。

「OK」ボタンをクリックして関連づけの完了です。

Bボタンをクリックしてマクロを実行させる。


 パソコンレジスターのマクロ解説 
  変数定義は、メモリー効率の悪い暗黙の変数宣言を行っています。
この場合は、バリアント型(変形)と見なされ DIM 変数 AS Integer 宣言よりメモリー消費が多くなります。
その点前段でお断り申し上げます。
詳細は、VBAパーツNo.3 セルの移動 その2 を参照下さい。

@レシート明細への転記マクロ


Sub data_catalog()

'スクリーンは、静止させませんので注記にします。
'    Application.ScreenUpdating = False

'データのチェック
   For i = 2 To 6                           '列セルの位置2から6をループ
     If Cells(7, i).Value = "" Then                '入力項目が無記入かどうかの判断
        MsgBox ("レジデータが不備です。")
        Exit Sub
     End If
   Next i
セルB7からF7までの入力チェックです。 全項目必須入力が条件であり "" 空欄はデータ不備により MsgBox ("レジデータが不備です。") のエラーメッセージを促します。後続の Exit Sub はSub プロシージャを直ちに終了させます。 Cells(7, i) の意味は(行,列)ですので7行目、そして列は For〜Nextのループ文 → i は2からはじまり6で終了と構文で定義しているので セルA列が1、2番目のB列から6番目のF列の7行目の入力値を IF で判断し無記入の場合はエラーメッセージを表示してSubプロシージャを終了させます。もちろんエラーの場合はレシートへの明細転記は中断です。 

'レシート明細へセット
   b_row = Cells(Rows.Count, 5).End(xlUp).Row + 1       'レジキー列(E列)の末尾行+1を得る
レシート明細の最終行を変数 b_row に代入します。 (Rows.Count, 5).End(xlUp).Row 5列目のレシート項目「レジキー」の最終行を取得しその求めた行に + 1 した値(最終行の次の空行)を代入します。求めた値は後続の最大明細数のチェックとレシート明細への転記に使います。
※値をチェックしたいのであれば Msgbox (b_row) を追記し、次の行に Exit Sub も追記してブロックごとにステートメントの動き、内容、値を確認する方法もVBA早わかりへの近道です。
Basicの優れているところはインタプリタといって途中の記述だけでも機械語に翻訳しながら実行してくれるところ。(但し、コンパイラーを必要とする言語よりも翻訳しながら動作するので実行速度が遅いのが欠点。昔と比べてマシンの性能が格段と違うので気にする問題ではないかも知れません。 ^^)

   If b_row > 44 Then
      MsgBox ("エラーです。30明細を超えました。登録することはできません。")
      Exit Sub
   End If
If b_row > 44 は レジキーの最終行を代入した変数 b_row  の値が 44を超えているかどうかを判断しています。
44とは、レシート明細行の最大セル行です。44は含まれないので45以上の場合にエラーとして処理を行っています。

   MsgBox ("レシート明細へ追加します。")
動作確認のための Msgbox処理です。意味はありませんので削除しても問題はありません。

   Cells(b_row, 5).Value = Range("h7").Value           'レジキー (商品番号)
   Cells(b_row, 6).Value = Range("e7").Value           '商品分類
   Cells(b_row, 7).Value = Range("f7").Value           '金額
ここは説明は不用と思います。わからないときは、Q&Aへ ポイント−1 がわからない と明記してください。

'入力セルの初期化
   Range("h7").Value = ""
   Range("f7").Value = ""
セルh7は、Regiボタンに隠れているセルでコンボボックスからレジキーの値が代入されます。その箇所と金額の初期化を行っています。

'入力欄セル金額をフォーカス
   Range("f7").Select
金額セルf7をアクティブにしています。

'    Application.ScreenUpdating = True
スクリーン静止を解除します。但し、先頭でコメントしているとおり静止させていませんので注記にしています。

End Sub


Aレシート明細をシート「DB」へ累積するマクロ


Sub db_catalog()

'スクリーンは、静止させませんので注記にします。
'    Application.ScreenUpdating = False

   Set sh1 = Sheets("レジ")
   Set sh2 = Sheets("DB")
Set ステートメントを使って、WorkSheetオブジェクトの参照を変数に代入します。効果としてはシート名の入力省力化やwithステートメントでの定義の容易性が上げられます。

   b1_row = Cells(Rows.Count, 5).End(xlUp).Row                'レジキー列(E列)の末尾行+1を得る  
レシート明細の最終行を変数 b1_rowへ代入します。

   If b1_row = 14 Then
      MsgBox ("明細がありません! DB登録をキャンセルします。") 
      Exit Sub
   End If
変数 b1_row の値が14の時 これはレシート明細の見出し行になるのでシート「DB」に追記する明細が無いことになりエラーとして処理を行っています。

   b2_row = sh2.Cells(Rows.Count, 2).End(xlUp).Row + 1          'シートDBの末尾行数+1を得るsh.Select
シート「DB」 sh2(SetステートメントでWorkSheetオブジェクトを代入している変数)の2列目 セルB列の取引日を指し、取引日セル列の最終行を求め +1 で追記する空行を求め変数 b2_row へ代入しています。

   MsgBox ("レシート明細の追加を観察するためシート'DB'へ移動します。")
動作確認のための Msgbox処理です。意味はありませんので削除しても問題はありません。

   sh2.Select
シート「DB」をフォーカス(アクティブ)します。

   MsgBox ("明細を追加します。")
動作確認のための Msgbox処理です。意味はありませんので削除しても問題はありません。

'最終伝票番号を取得
'明細1行目の時は伝票番号変数dへ0を代入  (見出し行から伝票番号を取得してしまう結果になるので回避策)
   If b2_row = 2 Then
      d = 1
   Else
      d = sh2.Cells(b2_row - 1, 1) + 1
   End If
If b2_row = 2 はシート「DB」の始めの行を意味し論理式が真の場合は、 d = 1 伝票番号変数 d に1を代入します。真の場合当然伝票番号は1から開始されます。 Else は ・・・でなかったら を意味しこの場合は偽の条件の d = sh2.Cells(b2_row - 1, 1) + 1 が実行されます。変数 b2_ow は最終行の次の行の値が代入されており、b2_row - 1 で最終行の値に戻したことになります。
つまり sh2.Cells(b2_row - 1, 1) はシート「DB」のセルA列最終行の伝票番号の値に +1 した値(最終の伝票番号の次の番号を採番したことになります。)を変数 d に代入しなさい。といった構文になります。追記するレシート明細に連続する伝票番号を採番するためのフェーズです。


   For i = 15 To b1_row
      With sh2
.         Cells(b2_row, 1).Value = d
.         Cells(b2_row, 2).Value = sh1.Cells(7, 2) & "/" & sh1.Cells(7, 3) & "/" & sh1.Cells(7, 4)
.         Cells(b2_row, 3).Value = sh1.Cells(i, 5)
.         Cells(b2_row, 4).Value = sh1.Cells(i, 6)
.         Cells(b2_row, 5).Value = sh1.Cells(i, 7)
         If i = 15 Then
            .Cells(b2_row, 6).Value = sh1.Cells(11, 6)
         End If

      End With
      b2_row = b2_row + 1

   Next i
With ステートメントは、指定したオブジェクトに対してオブジェクト名を再定義することなく、一連のステートメントを実行することができます。以降は、シンキングポイントとします。 わからないときは、Q&Aへ ポイント−2 がわからない と明記してください。
一カ所だけ説明をします。赤文字の箇所を見てください。 i=15 とはレシート明細の先頭行を意味し真の時に消費税を明細の先頭行に代入する構文です。


'来店者数のカウントアップ
   sh1.Range("b11").Value = sh1.Range("b11").Value + 1

   MsgBox ("レシート明細を追加しました。観察してください。OKボタンクリック後にシートレジへ移動します。")

   sh1.Select
   sh1.Range("f7").Select
フォーカスをシート「レジ」に戻します。

'データの初期化  キャンセルされた時と同じなので 初期化のプロシージャをcallする。
   Call cancel_regi
Call (フロー制御ステートメント)は、サブプロシージャの呼び出しです。イレギュラー入力したときの初期化と同じになりますのでサブプロシージャ cancel_regi を呼び出し実行します。

'    Application.ScreenUpdating = True

End Sub


Bイレギュラー打鍵時のレシート明細を初期化するマクロ


Sub cancel_regi()


'入力セルの初期化
   Range("h7").Value = ""
   Range("f7").Value = ""
売上入力欄のレジキーと金額セルに "" Null(ヌル)を代入します。

'レシート明細の初期化
   Range("e15:g44").Select
   Selection.ClearContents
レシート明細範囲セルe15からg44を選択し、 Selection.ClearContents でselection(選択範囲)から ClearContents 数式と文字を削除します。

'入力欄セル金額をフォーカス
   Range("f7").Select

End Sub

  以上、パソコンレジスターのマクロ全容です。
意見・感想・疑問点などなど、どしどしメールから、Q&Aから、掲示板から・・・・・・・