サンプル

電話メモ(エクセルVBA)

法律事務所では、かかってきた電話の内容などを弁護士に間違いなく伝えることが大事ですよね。
そこで、エクセルのマクロを使った「電話メモ」を紹介します。

エクセルで作った電話メモだったら、今でも使っていますよ。

マクロを使えば、エクセルからメールを自動送信したり、エクセルのファイルをみんなで同時に見たりできるようになりますよ。

そんなことができるんですか?
ぜひ、教えて下さい。

ダウンロード

まずは、サンプル・ファイルをダウンロードしてください。


ブック(ファイルに保存されたエクセルのデータ全体)を開くと自分以外のファイルからデータを読込むようにメッセージがでますが、とりあえずキャンセルしてください。

このマクロには、デジタル証明を添付しています。必要な設定を行って、マクロを使える状態にしてください。設定方法が分からない方は、こちらのサイトに詳しい説明があります。

Excel:セキュリティ設定|警告とトラストセンターのマクロ

使用方法

  • 「番号」列のセルをダブルクリックすると、新しい行が追加され、番号が自動入力されます。
  • 「日時」列のセルをダブルクリックすると、現在日時が自動入力されます。
  • 「確認日時」列のセルをダブルクリックすると、現在日時が自動入力されます。
  • 「オプション」シートに各所員のメールアドレスを登録しておけば、「メール送信日時」列のセルをダブルクリックするとメールが自動送信されます。
  • 「オプション」シートに各所員のファイル名を登録しておけば、ファイルを開いた際に他の所員のデータが読み込まれます。

細部については、ブックの「マニュアル」シートを参照してください。

なお、このサンプルは、電話メモを新たに作ったり、既に使っている電話メモを改善したりする場合の参考にしていただくためのものです。
使用にあたっては、それぞれの法律事務所に応じた電話メモに修正してお使いください。

このサンプルをそのまま使うこともできますよね。

そうなんですが、それではマクロを使う意味がありません。できあいのプログラムを使うのであれば、もっと良いソフトがいくらでもあると思います。
マクロの良いところは、自分の使いやすいプログラムを自分自身で手軽に作れることです。ぜひ、このサンプルを参考に自分の法律事務所にピッタリの電話メモを作っていただきたいと思います。

分かりました。
でも、難しそうですね。

次のページから、このプログラムの構造・機能を説明してゆきます。
プログラムのどの部分がどんな働きをしているのかが分かれば、修正するのは案外簡単だと思います。

すいませんが、忙しいので、手短に説明してくださいね。

ブックとワークシートの構成

まずは、「電話メモ」に使用されているワークシートについて、説明します。

説明して欲しいのは、マクロのことなのですが...

ワークシートをどのように作っているのかが分かっていないと、マクロがどのように働いているのかが分かりませんので、少しお付き合いください。

ブックの構成

ブックには、次の3つのシートが作られています。

  • 「メモ」シート
  • 「オプション」シート
  • 「マニュアル」シート

このうち、電話メモの本体となるのは、「メモ」シートです。

「メモ」シートの構成

「メモ」シートには、電話メモの各項目(フィールド)とそれに応じたデータ(レコード)が一覧表で入力できるようになっています。
表は必ずテーブルにするのがVBAアセットのスタイルです。

テーブルの作成方法については、こちらのサイトを参考にしてください。
テーブルを作成する:Excelの基本操作

テーブルの各セルには、ワークシートの通常機能を使って入力規則を設定しています。
可能な限りワークシートの機能を活用するのがVBAアセットのスタイルです。

例えば、「用件」のセルは、リストから入力データを選択できるようにします。

リストの「元の値」は、オプションシートに保存してあります。

「オプション」シートの構成

「オプション」シートには、「リスト設定」および「所員別設定」の定数をテーブルで記録しています。

  • 「リスト設定」には、「電話メモ」の「用件」列のリスト入力の「元の値」が入力してあります。
  • 「所員別設定」には、各所員ごとの「メールアドレス」、「ファイル名」、「ファイル更新日時」および「開始番号」を設定しています。
    「メールアドレス」には、メールを自動送信する際の宛先のアドレスを設定します。
    「ファイル名」は、各人ごとの電話メモのファイル名を設定します。各人が自分専用のファイルを開くことにより、同時に電話メモを閲覧・入力できるようになっています。(自分以外のファイルの情報は、自分のファイルを開いた時に自動的に読み込みます。)
    「ファイル更新日時」は、それぞれのファイルを開いた時のファイルのタイムスタンプを記録しています。これと現在のファイルスタンプを比較することにより、読み込み時にそのファイルを開く必要があるかどうかを判断しています。
  • 「開始番号」は、「電話メモ」の番号の初期値です。各人ごとの番号が重複しないようにして、自分以外のファイルを読み込んだ時に上書されないようにしています。

「マニュアル」シートの構成

「マニュアル」シートには、このブックの使用法と変更履歴を記載しています。


以上でブックとワークシートに関する説明を終わります。

テーブルを使うということが重要なんですね。

そうなんです。VBAの参考書では、後ろの方にならないと出てこないですが、データベースとしてエクセルを使うことが多い法律事務所の業務においては、非常に重要なテクニックです。

マクロの構造

それでは、「電話メモ」のマクロの全体構造を説明しましょう。

いよいよマクロに関する説明ですね。

エクセルの初期設定では、「開発」タブが表示されていないので、VBAのエディターを開くことができません。
まだ、設定が終わっていない方は、こちらのサイトを参考に設定を行ってください。

VBEへの入力位置

「開発」-「Visual Basic」をクリックして、VBEを起動してください。

左上の「プロジェクト・エクスプローラー」部で各モジュールをクリックすると、そこに記載されているコードが真ん中の「コード・エディター」部に表示されます。

見ていただくとお分かりのとおり、ほとんどのコードがシートモジュールに記載されています。標準モジュールには、ごくわずかしかコードが記載されていません。
コードは、シートモジュールに書くというのがVBAアセットのスタイルです。

エーッ!
私の持っている教科書では、マクロは「標準モジュール」に書くのが基本だと書いてありますよ。

そうですね。
でも、実は、シートに関係するコードは、そのシートに書いた方が、プログラムを構造化できますし、コードの記述も簡単になるんです。

プロジェクトの構成

「電話メモ」プロジェクトの構成は、次の図のようになっています。

「電話メモ」プロジェクト

マクロは、イベントプロシージャで起動するというのがVBAアセットのスタイルです。

これも教科書とは全然違いますよ。
マクロを実行するためには、シート上にボタンを配置したり、ショートカットキーに登録したりするのが基本的なやり方だとされています。

そうですね。イベントプロシージャは、教科書の後ろの方に応用的なテクニックとして記載されていることが多いようですね。
でも、最もエクセルらしい直感的な操作ができますし、特別なボタンを作成したりする手間も省けるので、積極的に使うべきだと考えています。

「ThisWorkbook」モジュールの機能

ThisWorkbook」モジュールには、ブックを開いた時に起動する「Workbook_Open」プロ―シージャが組み込まれています。

Workbook_Open」プロシージャは、「DsblEvents」、「DataImport」、「EnblEvents」プロシージャ―を順次に呼び出します。

DsblEvents」プロシージャは、イベントの発生を停止させて、マクロ実行中に他のイベント発生してイベントが連鎖するのを防止します。
EnblEvents」プロシージャは、「DattaImport」プロシージャが終了した後に呼び出され、停止されていたイベントの発生を再開します。

DataImport」プロシージャは、オプションに設定されたファイルを順次に開き、「Sheet1」モジュールの「DataCopy」プロシージャと「DataSort」プロシージャを呼び出します。
DataCopy」プロシージャは、Sheet1に新規・更新データをコピーします。
DataSort」プロシージャは、Sheet1のデータを日付順に並び替えます。

「Sheet1」モジュールの機能

Sheet1」モジュールには、シートをダブルクリックした時に起動する「Worksheet_BeforeDoubleClick」プロシージャと、シートに変更が加えられた時に起動する「Worksheet_Change」プロシージャが組み込まれています。

Worksheet_BeforeDoubleClick」プロシージャは、「番号」列のセルがダブルクリックされた場合は「InputNbr」プロシージャを、「メール送信日時」列のセルがダブルクリックされた場合は「 SendMail」プロシージャをそれぞれ呼び出します。
InputNbr」プロシージャは、既に入力されている番号の最大値を求めます。
SentMail」プロシージャは、Sheet1の内容でメールを作成して送信します。

「Sheet2」モジュールの機能

「Sheet2」モジュールには、シートをダブルクリックした時に起動する「Worksheet_BeforeDoubleClick」プロシージャが組み込まれています。

Worksheet_BeforeDoubleClick」プロシージャは、「データ読込」セルがダブルクリックされた場合は「DataImport」プロシージャを、「ファイル作成」セルがダブルクリックされた場合には「MakeFiles」プロシージャをそれぞれ呼び出します。
DataImport」プロシージャは、「ThisWorkbook」モジュールから呼び出された場合と同じ働きをします。
MakeFiles」プロシージャは、自分自身のファイルをコピーして、「オプション」シートに記載された各人用ファイルを作成します。

「Module1」の機能

標準モジュールである「Module1」モジュールには、イベントの抑止およびその解除などを行う「DsblEvents」プロシージャと「EnblEvents」プロシージャが組み込まれています。

DsblEvents 」プロシージャは、マクロ実行中のイベントの発生を抑止して、イベントが連鎖するのを防止します。併せて、画面の描画などを停止して、マクロの実行速度の向上を図っています。
EnblEvents」プロシージャは、「DsblEvents」が行った設定を解除するプロシージャです。

イベントは連鎖させないというのが、VBAアセットのスタイルです。

以上で、マクロの構造に関する説明を終わります。

プロジェクトとか、モジュールとか、プロシージャとか、何のことかよく分かりません。

各マクロの説明を読んでもらえれば、だんだんと分かってくると思います。
ここでは全般の流れが分かればよいので、あまり気にせずに、前に進みましょう。

番号の自動入力機能

いよいよプログラムの説明に入ります。
まずは、番号を自動入力する部分から説明しましょう。

番号を入力するだけですから、簡単そうですね。

そう思うかもしれませんが、将来の状況の変化にも対応できるしっかりとしたものを書きますので、かなり手ごわい内容です。
頑張ってついてきてください。

「Sheet1」モジュールの宣言セクション

「Sheet1」モジュールの冒頭にある宣言セクションには、次のコードが入力されています。

Option Explicit '変数の宣言を強制
Dim r As Long, s As Long                        'テーブルの行・列番

変数の宣言は、必ず行うというのがVBAアセットのスタイルです。
「Option Explicit」は、変数の宣言を強制するコードです。これがある場合は、宣言していない変数を使用するとエラーになります。
このコードは、VBEのオプションを設定することにより、自動的に記述されます。

「Dim r As Long, s As Long」は、「r」と「s」という2つの変数を宣言しています。
宣言セクションで宣言した変数は、モジュールレベルの変数と呼ばれ、モジュール内のすべてのプロシージャで共通して使用できます。

「Worksheet_BeforeDoubleClick」プロシージャ

'ダブルクリックした場合(変数Targetと変数Cancelを値渡しで受け取る)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'ターゲットのテーブル行・列番号を計算する
r = Target.Row - Range("電話メモ").Row + 1          'テーブル行番号
s = Target.Column - Range("電話メモ").Column + 1    'テーブル列番号

'テーブル行・列番号が入力範囲外の場合は終了する
If r < 1 Or Range("電話メモ").Rows.Count + 1 < r Then Exit Sub
If s < 1 Or Range("電話メモ").Columns.Count < s Then Exit Sub

'イベントなどを抑止する
Call DsblEvents

'ターゲット列の値に応じて処理を行う
Select Case Range("電話メモ")(0, s).Value
    '「番号」の場合
    Case "番号"
        '新しい行を追加する
        If Range("電話メモ[番号]")(r).Value <> "" Then
            r = Range("電話メモ").Rows.Count + 1
            Range("電話メモ[番号]")(r).EntireRow.Insert
            Range("電話メモ[番号]")(r).Activate
        End If
        
        '処理を行う
        Call InputNbr                           '番号を入力
              
        '更新日時を入力
        Range("電話メモ[更新日時]")(r).Value = Now    'データ更新日時を入力
        
        'セルの編集をキャンセルする
        Cancel = True
   
 <<省略>>
End Select

'イベントなどを再開する
Call EnblEvents

End Sub

「Worksheet_BeforeDoubleClick」は、シートがダブルクリックされた場合に起動するイベントプロシージャです。

ターゲットの行列番号は、それぞれTarget.RowとTarget.columnで求められますが、それはシート上の行列番号であって、テーブル上の行列番号ではありません。それぞれの値をRange(“メモ”)の位置を元に修正しています。

ダブルクリックしたセル(ターゲット)が入力可能な範囲(テーブルのすぐ下の行を含む)にあるかどうかの判断には、If構文を使っています。
if 構文の細部については、こちらのサイトをご覧ください。

DsblEventsは、イベントの連鎖を防止するプロシージャです。
このプロシージャは、次の内容となっていて、標準モジュールに保存されています。

Sub DsblEvents()
With Application
    .EnableEvents = False 'イベントを抑止
    .ScreenUpdating = False '画面描画を停止
    .Cursor = xlWait 'マウスポインタをウエイトカーソル
    .Calculation = xlCalculationManual '計算を手動に
End With
End Sub

Sub EnblEvents()
With Application
    .Calculation = xlCalculationAutomatic '計算を自動に
    .Cursor = xlNormal 'マウスポインタを標準カーソル
    .ScreenUpdating = True '画面描画を開始
    .EnableEvents = True 'イベントを開始
End With
End Sub

イベントの抑止に併せて、処理速度向上処理を行うのが、VBAアセットのスタイルです。

Select Case Range("電話メモ")(0, s).Value
    '「番号」の場合
    Case "番号"
        '新しい行を追加する
        If Range("電話メモ[番号]")(r).Value <> "" Then
            r = Range("電話メモ").Rows.Count + 1
            Range("電話メモ[番号]")(r).EntireRow.Insert
            Range("電話メモ[番号]")(r).Activate
        End If
        
        '処理を行う
        Call InputNbr                           '番号を入力
              
        '更新日時を入力
        Range("電話メモ[更新日時]")(r).Value = Now    'データ更新日時を入力
        
        'セルの編集をキャンセルする
        Cancel = True

ターゲットの列の値に応じて、処理を変える必要がありますので、Select Caseを用いて分岐させます。Select Caseについて、細部を確認したい方は、こちらをご覧ください。

ターゲットの列は、範囲(メモ)の行番号0で列番号がターゲットの列番号のセルの値で特定することができます。

この「Range(“電話メモ”)(0, s).Value」で「電話メモ」テーブルのs列のフィールド名が取得できるというのが、テーブルを使うことによる大きなメリットのひとつです。

そんな面倒なことをしなくても、「Select Case s」として、ターゲットの列番号に応じて、処理を分岐させればいいのではないですか?

そう思いますよね。でも、将来、列を追加した場合のことを考えてください。列番号で指示していた場合は、マクロが誤作動することになります。フィールド名で指定しておけば、列が追加になっても正常に作動しますし、もし、そのフィールド名自体が変更になった場合も、エラーが生じるだけで、誤作動はしません。

特定のフィールドの特定の行番号のセルの値は、構造化参照を使って「Range(“電話メモ[番号]”).value」で求めることができます。これもテーブルを使うことによる大きなメリットです。

テーブルを使わなくても、列に範囲名をつけてやれば、同じように列が追加になっても正常に作動するマクロが作れるのではないですか?

そのとおりです。でも、やってみると分かりますが、その範囲名を管理するのは、かなり面倒ですよ。特に複数のシートに同じ「番号」というフィールドがある場合などが大変です。テーブルなら、それを自動でやってくれます。

次に番号を入力するInputNbrプロシージャを呼び出します。

「InputNbr」プロシージャ

Sub InputNbr()
Dim lngBscNbr As Long, lngMaxNbr As Long                          '開始番号および最大番号
Dim x As Long, v As Long

'ターゲット行の「番号」列が空欄でない場合は終了する
If Range("電話メモ[番号]")(r).Value <> "" Then Exit Sub

'開始番号を取得する
With Sheet2
If WorksheetFunction.CountIf(.Range("所員別設定[ファイル名]"), ThisWorkbook.Name) <> 0 Then
    v = WorksheetFunction.Match(ThisWorkbook.Name, .Range("所員別設定[ファイル名]"), 0)
    lngBscNbr = .Range("所員別設定[開始番号]")(v).Value
End If
End With

'番号の最大値を取得する
lngMaxNbr = lngBscNbr
For x = 1 To Range("電話メモ").Rows.Count
    If lngMaxNbr < Range("電話メモ[番号]")(x).Value And Range("電話メモ[番号]")(x).Value < lngBscNbr + 10000 Then
        lngMaxNbr = Range("電話メモ[番号]")(x).Value
    End If
Next

'ターゲット行の「番号」列に番号の最大値+1を入力する
Range("電話メモ[番号]")(r).Value = lngMaxNbr + 1

End Sub

プロシージャの最初の部分には、変数の宣言を記入しています。ここで宣言した変数の有効期間は、このプロシージャの中だけになります。

変数の命名には、ハンガリアン法を用いるのがVBAアセットのスタイルです。

オプションに記載された開始番号の取得には、ワークシート関数を使用します。
ワークシート関数をできるだけ使うのがVBAアセットのスタイルです。

まず、Countif関数でこのブックの名前がファイル名に含まれているかどうかを確認します。含まれていることが確認できたならば、Match関数でそのテーブル行を取得します。あとはそのテーブル行の開始番号列のセルの値を開始番号として取得します。

次に、番号列の範囲内での最大値を求めます。1行目から電話メモの最終行番号までをFor~Next構文で確認して、最大番号を取得します。この際、開始番号を元にこのファイル用の番号のみについて、確認するようにします。最大値が求まれば、それを新しい行の番号列に入力して終わります。

最後にダブルクリックの本来の機能であるセルの編集をキャンセルするため、変数CancelにTrueを代入します。これにより、ダブルクリックした後、編集状態にならないまま、マクロを終了させることができます。

シートの行列番号と、テーブルの行列番号の関係がいまいちよく分かりません。
見出し行の1行分がずれていると認識するだけでは、ダメですか?

「テーブルの上や左にテーブル以外の行を作らない」ということを守れるのであれば、その考え方でも問題ありません。
とにかくシートの行列番号とテーブルの行列番号は、違うということだけはしっかり認識しておかないと、ずれたデータを参照してしまう可能性があるので気を付けてください。

日時の自動入力機能

次に日時を自動入力できるようにしましょう。

それって、「Ctrl+;」と「Ctrl+:」を使えば、マクロを使わなくても入力できますよね。

そのとおりですね。
でも、マクロを使えば、ダブルクリックするだけで日付と時刻を同時に入力することができます。

「Worksheet_BeforeDoubleClick」プロシージャ

<<省略>>

'ターゲット列の値に応じて処理を行う
Select Case Range("電話メモ")(0, s).Value

<<省略>>  

    '「日時」の場合
    Case "日時"
                
        '処理を行う
        Range("電話メモ[日時]")(r).Value = Now          '日時を入力
        
        '更新日時を入力
        Range("電話メモ[更新日時]")(r).Value = Now    'データ更新日時を入力
        
        'セルの編集をキャンセルする
        Cancel = True
    
 <<省略>>
             
    '「確認日時」の場合
    Case "確認日時"
                
        '確認日時を入力する
        Range("電話メモ[確認日時]")(r).Value = Now
        
        '更新日時を入力
        Range("電話メモ[更新日時]")(r).Value = Now    'データ更新日時を入力
        
        'セルの編集をキャンセルする
        Cancel = True
                                    
End Select

<<省略>>

End Sub

「日時」と「確認日時」は、「更新日時」の入力と同じ要領で行います。

メールの自動送信機能

次は、メール送信機能について説明します。

メールの送信なんて、私の持っているVBAの参考書には、載っていません。すごく難しそうですね。

たしかに、OLEオートメーションと呼ばれる規格を利用する必要があり、それを理解しようとすると非常に難しいです。ただし、メールを送るということだけであれば、ネット上にもいろいろと情報があるので、そのとおりに設定すればとりあえず動くようにはできるはずです。

「Worksheet_BeforeDoubleClick」プロシージャ

<<省略>>

'ターゲット列の値に応じて処理を行う
Select Case Range("電話メモ")(0, s).Value
 
<<省略>>

    '「メール送信日時」の場合
    Case "メール送信日時"
                
        '処理を行う
        Call SendMail                           'メールを送信
        
        '更新日時を入力
        Range("電話メモ[更新日時]")(r).Value = Now    'データ更新日時を入力
        
        'セルの編集をキャンセルする
        Cancel = True
                                    
<<省略>>
     
End Select

<<省略>>

End Sub

「メール送信日時」列のセルがダブルクリックされた場合は、SendMailプロシージャが呼び出されます。

「SendMail」プロシージャ

Sub SendMail()
Dim objOutlook As Object                                                    'Outlookオブジェクト
Dim objMAIL As Object                                                       'メールオブジェクト
Dim strMailTo As String, strMailAddress As String, strMailBody As String    'メールの各情報
Dim v As Long                                                               '所員別設定のテーブル行

'Outlookオブジェクトの実行時バインディングを行う
Set objOutlook = CreateObject("Outlook.Application")

'olMailItemオブジェクトを作成する
Set objMAIL = objOutlook.CreateItem(0)      '.CreateItem(olMailItem)では動作しない(原因不明)

'メール宛先を把握する
strMailTo = Range("電話メモ[誰へ]")(r).Value

'メールアドレスを把握する
With Sheet2
    If WorksheetFunction.CountIf(.Range("所員別設定[所員名]"), strMailTo) > 0 Then
        v = WorksheetFunction.Match(strMailTo, .Range("所員別設定[所員名]"), 0)
    End If
    strMailAddress = .Range("所員別設定[メールアドレス]")(v).Value
End With

'メールアドレスがなかった場合は終了する
If strMailAddress = "" Then
    MsgBox "メールアドレスが設定されていません。 "
    Exit Sub
End If
   
'メール送信を確認し、キャンセルされた場合は終了する。
If MsgBox("この行の内容を次の宛先にメールで送信します。" & vbCrLf & _
        vbCrLf & _
        strMailTo & "(" & strMailAddress & ")" & vbCrLf & _
        vbCrLf & _
        "よろしいですか?", vbOKCancel) = vbCancel Then
    MsgBox "メールの送信を中止しました。 "
    Exit Sub
End If
    
'メール本文を作成する
strMailBody = _
    "番  号:" & Range("電話メモ[番号]")(r).Value & vbCrLf & _
    "日  付:" & Format(Range("電話メモ[日時]")(r).Value, "yyyy/m/d") & vbCrLf & _
    "時  間:" & Format(Range("電話メモ[日時]")(r).Value, "h:mm") & vbCrLf & _
    "誰 か ら:" & Range("電話メモ[誰から]")(r).Value & vbCrLf & _
    "内  容:" & Range("電話メモ[内容]")(r).Value & vbCrLf & _
    "入 力 者:" & Range("電話メモ[入力者]")(r).Value & vbCrLf
            
'メール情報を設定する
objMAIL.To = strMailAddress       'メール宛先
objMAIL.Subject = "電話メモ"    'メール題名
objMAIL.Body = strMailBody    'メール本文

'メールを送信する
objMAIL.Send

'オブジェクトを解放する
Set objOutlook = Nothing
Set objMAIL = Nothing

'メール送信完了時の処理を行う
Range("電話メモ[メール送信日時]")(r).Value = Now                   '送信日時を記録
MsgBox strMailTo & "へのメール送信が完了しました。"

End Sub

SendMailプロシージャでは、OLEオートメーションと呼ばれる機能を設定し、Outlookを使ってメールを送信する準備を整えます。

OLEオートメーションを利用する方法には、事前バインディングと実行時バインディングの2つがありますが、電話メモには、実行時バインディングを用いています。理由は、事前バインディングの場合は、使われるPCすべてでVBEを使った設定が必要となるからです。
次にメールを操作するオブジェクトであるMailItemを作成します。OutLookの場合は、これ以外に予定、タスクおよび連絡先のオブジェクトの作成が可能です。

続いて、送付先のメールアドレスを取得します。

まず、「メモ」テーブルに入力されている「誰へ」の値を読み取ります。これは、Range(“メモ[誰へ]”)(r)で簡単に読み取れます。

次にオプションシートの「所員別設定」テーブルからメールアドレスを読み取る必要があります。
まず、オプションシートにアクセスする必要があるのですが、その際のシートの特定には、シート名である「オプション」ではなく、コードネームである「Sheet2」を用います。

コードネームを使用することにより、ワークシートの名前が変更になっても、マクロを変更する必要がなくなります。細部は、鉄則10の解説をご覧ください。

次に「オプション」シートの「所員別設定」テーブルから、その所員のメールアドレスを取得します。いわゆる「表引き」を行うわけです。「表引き」には、たくさんの方法がありますが、以前紹介した次の鉄則がここにも適用されます。

まず、CountIf関数を使って、その「所員名」が「所員別設定」テーブルの「所員名」列に存在するかどうかを確認します。存在する場合には、さらにMatch関数を使ってその「所員名」のテーブル行を求めます。あとは、「メールアドレス」列のその行の値を求めれば、メールアドレスが取得できます。
ここで、あらかじめCountIf関数を用いるのは、その「所員名」が存在しないのにMatch関数でその行を求めようとすると、エラーとなるためです。

最後に、テーブルにメールアドレスが入力されていない場合は、メッセージを表示して終了するようにしています。

メッセージを表示して、送信するかどうかを確認します。

次に「メモ」テーブルのターゲット行(r)のデータを使って、メール本文を作成します。

「日付」と「時間」は、「日時」のデータからそれぞれFormat関数を用いて表示しています。

メールの宛先、題名および本文の各情報を設定し、メールを送信します。

最後にオブジェクトの開放と送信日時の記録、送信完了のメッセージの表示を行う。
オブジェクトの解放は、通常は、プロシージャの終了と同時に自動的に行われるので不要なのですが、何らかの理由で解放されなかった場合に備えて、強制的に開放を行うものです。

以上でメールの送信に関するマクロの説明を終わります。

これなら、弁護士へのメール送信がずっと簡単になりますね。

そうですね。
送信した内容が一覧表で残るのも便利ですよね。

データ更新日時の入力機能

「Worksheet_Change」プロシージャ

Private Sub Worksheet_Change(ByVal Target As Range)

'ターゲットのテーブル行・列番号を計算する
r = Target.Row - Range("電話メモ").Row + 1          'テーブル行番号
s = Target.Column - Range("電話メモ").Column + 1    'テーブル列番号

'テーブル行・列番号が入力範囲外の場合は終了する
If r < 1 Or Range("電話メモ").Rows.Count < r Then Exit Sub
If s < 1 Or Range("電話メモ").Columns.Count < s Then Exit Sub

If Range("電話メモ")(0, s).Value = "更新日時" Then Exit Sub

'データ更新日時の入力
Range("電話メモ[更新日時]")(r).Value = Now

End Sub

ワークシートが変更された場合に、データの更新日時を更新します。

データの読み込み機能

それでは、最後にデータを共有するためのマクロを追加しましょう。

それって、「ファイルの共有」とか「共同編集」を行うということですよね。

いいえ、違います。
「ファイルの共有」や「共同編集」ができない環境でも、マクロを使って簡易にデータを共有するということです。

そんなことって、できるのですか?

いろいろと制約や限界がありますが、データの共有が必要な人数が数人で、電話メモのように一旦入力されたデータが変更されることがない場合には、有効な方法だと思います。

データ共有の方法

VBAアセットでは、各人別ファイルを利用してデータを共有するというスタイルを採っています。

標準モジュールの宣言セクション

Option Explicit
Public strSrc As String 'ソースブック名

変数「strSrc」をパブリック変数として宣言しています。
データの読み込み機能は、複数のモジュールにまたがっていて、それぞれのプロシージャ―からソースブック名を参照できるようにしています。

「Worksheet_BeforeDoubleClick」プロシージャ

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Address = Range("データ読込").Address Then
    'イベントなどを抑止する
    Call DsblEvents
    
    '処理を実行する
    Call DataImport
    
    'イベントなどを再開する
    Call EnblEvents
    
    'セルの編集をキャンセルする
    Cancel = True
End If
<<省略>>
End Sub

オプションシートの「データ読込」セルをダブルクリックすると、データの読み込み処理が行われます。

「Private Sub Workbook_Open」プロシージャ

Private Sub Workbook_Open()
'イベントなどを抑止する
Call DsblEvents

'自分以外のファイルのデータを読み込む
Call Sheet2.DataImport

'イベントなどを再開する
Call EnblEvents

End Sub

ファイルが開かれた時にも同じ処理が行われるように設定されています。

「DataImport」プロシーシャ

Sub DataImport()
Dim datSrc As Date          'ソースブック更新日時
Dim v As Long               '「所員別設定」テーブル行
Dim varUpdateInf As Variant '更新情報

'ファイル名が設定されていない場合は終了する
If WorksheetFunction.CountA(Range("所員別設定[ファイル名]")) = 0 Then Exit Sub

'データの読み込み開始を確認する
If MsgBox("自分以外のファイルからデータを読み込みます。", vbOKCancel) = vbCancel Then Exit Sub

'更新情報を初期化する
varUpdateInf = ""

'自分以外のファイルからデータをコピーする
For v = 1 To Range("所員別設定").Rows.Count
    'ソースブック名を取得する
    strSrc = Range("所員別設定[ファイル名]")(v).Value
    
    'ソースブックの更新日時を取得する
    On Error GoTo HdlErr
    datSrc = FileDateTime(ThisWorkbook.Path & "\" & strSrc)
    On Error GoTo 0
    
    'ソースブックが自分自身の場合は更新日時を変更して次のソースブックへ
    If Application.ThisWorkbook.Name = strSrc Then
        Range("所員別設定[ファイル更新日時]")(v).Value = datSrc
        GoTo Cont
    End If
    
    'ソースブックの更新日時が「所員別設定」テーブルに記録された前回の更新日時よりも新しくない場合は次のソースブックへ
    If Range("所員別設定[ファイル更新日時]")(v).Value >= datSrc Then
        GoTo Cont
    End If
        
    DoEvents
    Application.StatusBar = strSrc & "を読み込んでいます。"
                    
    'ソースブックを読み取り専用で開く
    Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strSrc, ReadOnly:=True
                                       
    'データをコピーする
    Call Sheet1.DataCopy
    
    '「所員別設定」テーブルのソースブックの更新日時を更新する
    Range("所員別設定[ファイル更新日時]")(v).Value = datSrc

    '更新情報を追加
    varUpdateInf = varUpdateInf & strSrc & " " & datSrc & vbCr
    
    'ソースブックを閉じる
    With Workbooks(strSrc)
        .Saved = True
        .Close
    End With

Cont:
    DoEvents
    Application.StatusBar = ""
Next

'データの並び替えを行う
Application.StatusBar = "データを並び替えています。"
Call Sheet1.DataSort
Application.StatusBar = ""

'終了メッセージを表示する
If varUpdateInf = "" Then
    MsgBox "更新されたデータはありません。"
Else
    MsgBox "次のデータの読み込みが完了しました。" & vbCr & varUpdateInf
End If

Exit Sub
'ソースブックを開けなかった場合
HdlErr:
'開けなかったブックを表示する
MsgBox strSrc & "が読み込めませんでした。", vbCritical
'次のソースブックを開く
Resume Cont

End Sub

「オプション」シートの「所員別設定」テーブルに入力されている「ファイル名」について、順次、タイムスタンプを確認し、ファイルを開き、データをコピーするという処理を繰り返します。
データのコピーするプロシージャは、「電話メモ」シートモジュールに保存されているDataCopyプロシージャを呼び出します。すべてのファイルからのデータの読み込みが完了したならば、同じく「電話メモ」シートモジュールにDataSortプロシージャを呼び出して、データの並び替えを行います。

「オプション」シートに保存されている更新日時を使って、前回の読み込み以降更新されていないファイルは読み込まないようになっているんですね。

そうなんです。
データの読み込みに必要な時間を最小限にするように工夫しています。

「DataCopy」プロシージャ

Sub DataCopy()
Dim shtSrc As Worksheet, rngSrc As Range
Dim lngSrcRows As Long
Dim rngOrg As Range, rngOrgNo As Range
Dim lngSrcCase As Long, datSrcDate As Date
Dim datOrgDate As Date
Dim t As Long

'コピー元範囲を取得する
Set shtSrc = Workbooks(strSrc).Worksheets("電話メモ")
Set rngSrc = shtSrc.Range("電話メモ")
lngSrcRows = rngSrc.Rows.Count
    
'コピー
For t = 1 To lngSrcRows
    'コピー先の範囲を取得する
    Set rngOrg = Range("電話メモ")
    Set rngOrgNo = Sheet1.Range("電話メモ[番号]")
    
    'コピー元の番号および更新日時を取得する
    lngSrcCase = shtSrc.Range("電話メモ[番号]")(t).Value
    datSrcDate = shtSrc.Range("電話メモ[更新日時]")(t).Value
    
    'コピー先のテーブル行を決定する
    If Application.CountIf(rngOrgNo, lngSrcCase) > 0 Then
        r = Application.Match(lngSrcCase, rngOrgNo, 0)
    Else
        If rngOrg(1).Value = "" Then
            r = 1
        Else
            r = rngOrg.Rows.Count + 1
        End If
    End If
    
    'コピー先の更新日時を取得する
    datOrgDate = Sheet1.Range("電話メモ[更新日時]")(r).Value
            
    'コピー先よりもコピー元の更新日時が新しい場合はレコードを上書する
    If datSrcDate > datOrgDate Then
        rngOrg.Rows(r).Value = rngSrc.Rows(t).Value
    End If
Next

End Sub

データのコピーは、コピー元のテーブル行ごとに番号と更新日時を比較し、必要なデータを1行ごとコピー先にコピーしています。
コピー先の同一番号のテーブル行の取得は、ワークシート関数のCountIfとMatchを組み合わせて行っています。
セルのコピーは、セル(範囲)への代入で行うというのがVBAアセットのスタイルです。Copyメソッド(操作)は、基本的に使いません。

コピーするのにCopyを使わないなんて、そんな方法もあるんですね。

値をコピーするだけなら、この方が簡単ですよね。
実行速度もコピーするより速いようです。

それでも読み込みには、ちょっと時間がかかりますよね。
もっと、速度を向上させることはできないのですか?

ファイルを開くのにどうしてもある程度の時間がかかってしまうので、これ以上の改善は難しそうです。配列も使ってみましたが、データ数が数100件のレベルでは、ほとんど差がありませんでした。

「DataSort」プロシージャ

Sub DataSort()

'データを表示用に並び替え
Range("電話メモ").Sort _
    key1:=Range("電話メモ[日時]"), order1:=xlAscending, _
    key2:=Range("電話メモ[番号]"), order2:=xlAscending, _
    Header:=xlYes

End Sub

データのソートは、日時と番号で行っています。

並び替えって、以外に簡単にできちゃうんですね。

Excel2007以降は、新しい方式が加わっていますが、ちょっと複雑なのでExcel2003までの方式で行っています。並び替えのキーが3件までなどの制約がありますが、こっちの方が簡単でいいですよね。

ファイル作成機能

「Worksheet_BeforeDoubleClick」プロシージャ

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

<<省略>>

If Target.Address = Range("ファイル作成").Address Then
    'イベントなどを抑止する
    Call DsblEvents
    
    '処理を実行する
    Call MakeFiles
    
    'イベントなどを再開する
    Call EnblEvents
    
    'セルの編集をキャンセルする
    Cancel = True
End If
End Sub

各人用のファイルを自動的に作成する機能です。
MakeFilesプロシージャを呼び出します。

「MakeFiles」プロシージャ

Sub MakeFiles()
Dim v As Long
Dim strNewFile As String
Dim strOriginalFile As String

If MsgBox("このファイルをコピーして、各人用ファイルを作成します。" & vbCr & _
"データのバックアップを取ってから実行してください。", vbOKCancel) = vbCancel Then Exit Sub
    
If MsgBox("すべての各人用ファイルが上書きされます。" & vbCr & _
"本当にいいですか?", vbOKCancel + vbExclamation) = vbCancel Then Exit Sub
    
strOriginalFile = ThisWorkbook.Name

For v = 1 To Range("所員別設定").Rows.Count
    'ファイル名を取得
    strNewFile = Range("所員別設定[ファイル名]")(v).Value
    
    'このファイルを名前を変えて保存
    On Error GoTo HandleError
    If strOriginalFile <> strNewFile Then
        ThisWorkbook.SaveCopyAs Filename:=ThisWorkbook.Path & "\" & strNewFile  '他の人のファイルを保存する
    End If
    On Error GoTo 0
Next

MsgBox "ファイルの作成が完了しました。"
Exit Sub

HandleError:
MsgBox "ファイルの作成ができませんでした。"
End Sub

「オプション」シートの「所員別設定」テーブルに入力されているファイル名に基づいて、自分のファイルをコピーします。

お疲れさまでした。
以上で「電話メモ」の構造と機能の説明を終わります。

列の名前を変更したり、確認をした弁護士の名前の列を追加したりして、うちの事務所で使っている様式に変更してみました。マクロは、それに関係する部分をほんの少し修正しただけで、問題なく動いているようです。

それは良かったです。
ただし、今後、さらに大きな改善を加えたり、別なシステムを作る場合には、やはり基礎からちゃんと勉強しなければならないかも知れません。

そうですね。
でも、とりあえず一歩前に進めたのは良かったと思います。

コメント

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