エクセル顧客管理 | 第7回.本格的なプログラムへ | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第7回.本格的なプログラムへ


エクセルで顧客管理を作ります、


前回までのプログラムは、説明の為のコーディングをした部分があります、


本格的なプログラムを目指す為に、いろいろ手直ししようと思います。


コーディングは好みの部分も大きいのですが、


最も気を付けるべき事は、

1.可読性

2.保守性

3.処理スピード

になるかと思います。


そして、私は、上記を満たした上で、美しいプログラムを書きたいと思っています。


なかなか難しく、毎回反省していますが。(笑)


では、モジュール「Mod共通」を全面取り換えします。

<><><><><><><><><>

Option Explicit

Sub マクロ開始処理()
  Application.Cursor = xlWait           'カーソルを砂時計に
  Application.ScreenUpdating = False       '画面描画を停止
  Application.EnableEvents = False        'イベントを抑止
  Application.DisplayAlerts = False       '確認メッセージを抑止
  Application.Calculation = xlCalculationManual '計算を手動に

  Application.Calculate              'ブック全体を再計算する
End Sub

Sub マクロ終了処理()
  Application.Calculation = xlCalculationAutomatic '計算を自動に
  Application.DisplayAlerts = True        '確認メッセージを開始
  Application.EnableEvents = True         'イベントを開始
  Application.ScreenUpdating = True       '画面描画を開始
  Application.Cursor = xlDefault         'カーソルを標準にに
End Sub

Function シート取得(ByVal strSht As String) As Worksheet
  On Error Resume Next 'エラーを無視
  Select Case strSht
    Case "顧客登録"
      Set シート取得 = Worksheets(Range("顧客登録シート名").Value)
    Case "顧客一覧"
      Set シート取得 = Worksheets(Range("顧客一覧シート名").Value)
    Case "項目名"
      Set シート取得 = Worksheets(Range("項目名シート名").Value)
    Case "設定"
      Set シート取得 = Worksheets(Range("設定シート名").Value)
    Case Else
      MsgBox "【シート取得】シート名が不正です。", vbOKOnly
      End
  End Select
  On Error GoTo 0 'エラーを有効化
  If シート取得 Is Nothing Then
    MsgBox "【シート取得】名前定義が不正です。", vbOKOnly
    End
  End If
End Function

Function 開始セル取得(ByVal strSht As String) As Range
  On Error Resume Next 'エラーを無視
  Select Case strSht
    Case "顧客登録"
      Set 開始セル取得 = Range("顧客登録開始")
    Case "顧客一覧"
      Set 開始セル取得 = Range("顧客一覧開始")
    Case Else
      MsgBox "【開始セル取得】シート名が不正です。", vbOKOnly
      End
  End Select
  On Error GoTo 0 'エラーを有効化
  If 開始セル取得 Is Nothing Then
    MsgBox "【開始セル取得】名前定義が不正です。", vbOKOnly
    End
  End If
End Function


前回とは全く違う物になっています。


前回は、Public変数を使っていたのですが、どうもスマートではないと感じます。


従って、Public変数を取り止め、Functionに変更しました。


おそらく、処理スピードは、前回の方が早いだろうと思われますが、


スピードといっても体感できる違いはありません。


それよりも、可読性・保守性を優先しました。


マクロ開始処理

マクロを開始する時に、まずやるべきことを纏めています。

特に、

Application.ScreenUpdating = False

は必須だとお考え下さい、これを指定することで、スピードが格段に早くなります。

指定しない場合は、マクロのシート操作が目で見れます。目が良ければ(笑)

1つ1つの説明は、コード横のコメントを見て下さい。

詳細については、ネットで調べればすぐに探せるはずです。

まあ、最低限、これらを指定すれば良いと考えておいて下さい。


マクロ終了処理

マクロ開始処理で停止したものを、再開させます。


Function シート取得(ByVal strSht As String) As Worksheet

Function プロシージャ名(引数の渡し方 引数 As データ型) As 戻り値のデータ型
プロシージャ名 = 戻り値
End Function

引数の渡し方は省略可能で、省略すると、ByRefになります。

ByValは、値渡し

ByRefは、参照渡し

細かい話ですが、ByRefの場合は、Functionの終了時に、元の変数に戻す処理が行われます。

従って、必要が無い場合は、ByValを指定します。

ByRefを指定してFunctionを作成すれば、呼び出し側に、引数を通じて値を戻すことが出来ます。

初心者の方のプログラムでは省略していることの方が多いようです、できれば指定する位で良いとは思います。

戻り値のデータ型に、Worksheetを指定しています。


つまり、このFunctionの呼び出し元に、Worksheetを返します。

Worksheetはオブジェクトです、オブジェクトの型は難しいので、不明な場合は、objectを指定します。

On Error Resume Next

これ以降はエラーが発生してもマクロを停止させず、次ステップへ進みます。

このように、エラーを補足する命令をエラートラップといいます。


On Error GoTo 0

エラートラップを無効にし、エラー発生でマクロが停止するようにします。


Select Case strSht

Select Case 変数
Case 値1
変数=値1の場合の処理
Case 値2
変数=値2の場合の処理
Case Else
上記以外の場合の処理
End Select

複数の条件で処理を分岐させる場合に使用します。

If文は、真か偽の判定のみの場合に使用します。

条件が3通り以上になる場合は、Select Caseを使用します。


Set シート取得 = Worksheets(Range("顧客登録シート名").Value)

Functionの戻り値を設定しています。

ここは、Worksheetオブジェクトなので、Setを使用します。

Range("顧客登録シート名").Valueで、以前は、.Valueを省略していましたが、ここではあえて指定しています。

省略できますが、Rangeというオブジェクトなのか、そのValueプロパティなのかを明示しています。

つまり、可読性を上げています、読み手に誤解を与えない為です。


MsgBox "【シート取得】シート名が不正です。", vbOKOnly
「OK」ボタンのみのメッセージボックスを表示します。

処理途中で、使用者に対し、メッセージ表示します。

プログラム完成後は、このメッセージが表示されることはほぼ無いはずです。

作成中のデバッグの補助が主な目的です。


If シート取得 Is Nothing

名前定義が間違っている場合(実際の名前定義とVBA内での名前が違う)は、

On Error Resume Nextにより、ここに処理が移ってきます。

その場合は、Functionの戻り値である、シート取得に何も設定されていません。

この場合は、Nothingが入っています。



End
マクロを停止します。

ここでは、シート名が取得できないので、これ以上処理が不可能なので、停止させます。


Function 開始セル取得(ByVal strSht As String) As Range

Function シート取得(ByVal strSht As String) As Worksheetとほぼ同じですが、

戻り値の型が、Range、つまりセル範囲を指定しています。


Set 開始セル取得 = Range("顧客登録開始")
Functionの戻り値を設定しています。

ここは、Rangeオブジェクトなので、Setを使用します。


かなり難しくなってきてしまったようです。


理解できない部分は、まんま受け入れて、先に進んでも問題ありません。


いっぷく


続いて「Mod共通」を使うように、モジュール「Mod顧客登録」の変更です。

Option Explicit

Sub 顧客登録シート作成()
  Dim r1 As Long, c1 As Long '顧客一覧の見出しの行,列位置
  Dim r2 As Long, c2 As Long '顧客登録の行,列位置
  Dim intW As Integer     '列数計算用
  
  Call マクロ開始処理

  r1 = 開始セル取得("顧客一覧").Row  '顧客一覧の開始行位置を取得
  c1 = 開始セル取得("顧客一覧").Column '顧客一覧の開始列位置を取得

  r2 = 開始セル取得("顧客登録").Row  '顧客登録の開始行位置を取得
  c2 = 開始セル取得("顧客登録").Column '顧客登録の開始列位置を取得

  With シート取得("顧客登録") 'Worksheets("顧客登録")を省略可能とする
    .UsedRange.Clear '顧客登録の使用セルを全てクリア
    
    '顧客一覧の3行目を2列目から右に進み、空白セルになるまで繰り返す
    Do Until IsEmpty(シート取得("顧客一覧").Cells(r1, c1))
      '顧客一覧の見出しを顧客登録にコピー
      シート取得("顧客一覧").Cells(r1, c1).Copy .Cells(r2, c2)
      '顧客一覧のデータ部を顧客登録にコピーし、データは消去する
      シート取得("顧客一覧").Cells(r1 + 1, c1).Copy Destination:=.Cells(r2, c2 + 1)
      .Cells(r2, c2 + 1).ClearContents
      '顧客一覧の列幅が、顧客登録の列幅の何個分かを計算し、セルを結合する。
      intW = Round(シート取得("顧客一覧").Columns(c1).Width / .Columns(c2 + 1).Width, 0)
      .Range(.Cells(r2, c2 + 1), .Cells(r2, c2 + 1 + intW)).MergeCells = True
      '罫線を引く
      .Range(.Cells(r2, c2), .Cells(r2, c2 + 1 + intW)).Borders.LineStyle = xlContinuous
      c1 = c1 + 1 '顧客一覧の列を右に
      r2 = r2 + 2 '顧客登録の行を2つ下に
    Loop
  End With
  
  シート取得("顧客登録").Select '指定シートへ移る
  開始セル取得("顧客登録").Offset(0, 1).Select '開始位置の右横のセルを選択
  
  Call マクロ終了処理
End Sub


太字の部分が変更点です。


Call マクロ開始処理

Subプロシージャー「マクロ開始処理」を実行します。


開始セル取得("顧客登録")

前回までは、Public変数のRangeオブシェクトを使っていましたが、

その部分を、Function「開始セル取得」に変更しました。

それ以外は同じです。


シート取得("顧客登録")

前回までは、Public変数のシート名を使用し、Worksheets(sht顧客登録)としていましたが、

その部分を、Function「シート取得」に変更しました。

Functionの型がWorksheetなので、全く同じことになります。

それ以外は同じです。


シート取得("顧客登録").Select

シートを選択しています。


開始セル取得("顧客登録").Offset(0, 1).Select

expression.Offset(行番号,列番号)です。

指定の行、列分移動したセルを返します。

ここでは、シート「顧客登録」の使用先頭セルの右横になります。

そして、そのセルを選択しています。



どうでしょうか?


見づらくなった


まあ、慣れの問題です。


可読性保守性を高めるための手段です。


でも、これで、シート名の変更等を気にすることなく、心置きなくプログラミングできます。


次回は、どうしましょうか・・・


「顧客一覧」からデータを取得し、「顧客登録」に表示してみましょう。



今回の復習

ScreenUpdating
Function
On Error
Select Case
If
MsgBox
End
Select
Offset





同じテーマ「エクセル顧客管理」の記事

第8回.顧客一覧より顧客データを取得
第9回.イベントを使ってマクロを起動させる
第10回.コーディングとデバッグ
第11回.顧客登録より顧客一覧へ更新
第12回.最終行の判定、Rangeオブジェクトと配列、高速化の為に
第13回.コントロールのボタンを配置
第14回.オブジェクトとプロパティの真実(GW特別号No1)

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.定数と型宣言文字(Const)|ExcelマクロVBA入門
8.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 本格的なプログラムへ

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP