【保存版】Microsoft ExcelのVBAを極める！データ自動化からメール送信までできる高度な活用ガイド

VBAをこれから学び始めるという方は、まず初心者向けのVBA入門ガイドから読むことをおすすめします。一方、すでにVBAに慣れ親しんでいて、さらに一歩進んだ活用法を探している方には、本記事がぴったりです。

ExcelでVBAコードを使えるようになると、業務の自動化の可能性が一気に広がります。計算処理の自動化はもちろん、ボタン操作の追加や、メールの自動送信まで実現できます。VBAで日々の作業を自動化できる場面は、想像以上に多いのです。

【保存版】Microsoft ExcelのVBAを極める！データ自動化からメール送信までできる高度な活用ガイド

Microsoft Excelのための高度なVBAガイド

ExcelでVBAコードを書く最大の目的は、スプレッドシートから情報を抽出し、さまざまな計算を加え、その結果を再びシートに書き戻すことです。この基本フローを押さえておけば、応用の幅が大きく広がります。

以下は、ExcelにおけるVBAの最も一般的な活用例です。

外部データのインポートと計算処理
ユーザーがボタンを押したときの計算実行
計算結果のメール送信

この3つの例をマスターすれば、自分専用の高度なExcel VBAコードを自在に組み立てられるようになるでしょう。

1. データのインポートと計算処理

Excelの代表的な用途のひとつが、Excelの外にあるデータに対する計算処理です。VBAを使わない場合、データの手動インポート、計算の実行、別シートやレポートへの出力といった一連の作業を、すべて手作業で行う必要があります。

VBAを使えば、この流れ全体を完全に自動化できます。たとえば、毎週月曜日にPCの特定フォルダへ新しいCSVファイルがダウンロードされる運用なら、火曜日の朝にスプレッドシートを開いた瞬間にVBAコードが走るよう設定できます。

次のコードを実行すると、CSVファイルがExcelシートにインポートされます。

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:\temp\purchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

ExcelのVBAエディターを開き、Sheet1オブジェクトを選択します。オブジェクトとメソッドのドロップダウンから「Worksheet」と「Activate」を選びましょう。こうすることで、スプレッドシートを開くたびにコードが実行されます。

すると「Sub Worksheet_Activate()」関数が生成されるので、上記のコードをその関数内に貼り付けてください。

このコードは、アクティブなワークシートをSheet1に設定し、シートの内容をクリアしたうえで、strFile変数で定義したファイルパスを使ってファイルに接続します。続くWithループがファイルの各行を読み込み、セルA1を起点としてデータをシートに書き込んでいきます。

コードを実行すると、CSVファイルのデータが空のシート（Sheet1）にインポートされているのが確認できます。

インポートはあくまで第一歩です。次に、計算結果を格納する新しい列のヘッダーを作成しましょう。ここでは、各商品の売上に対する5%の税額を計算する例を考えます。

コードが実行すべき処理の順序は以下のとおりです。

「taxes」という名前の新しい結果列を作成する
販売価格の列をループして税額を計算する
計算結果をシートの該当行に書き込む

以下のコードで、これらのステップをすべて実現できます。

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

このコードは、まずデータが存在する最終行を特定し、最初の行と最後の行をもとにセル範囲（販売価格の列）を設定します。その後、範囲内の各セルを順にループしながら税額を計算し、結果を新しい列（5列目）に書き込んでいきます。

先ほどのコードの下に上記のVBAコードを貼り付けてスクリプトを実行すると、E列に計算結果が表示されます。

これで、Excelワークシートを開くたびに、CSVファイルから最新のデータが自動的に取得され、計算が実行され、結果がシートに書き込まれるようになります。もう手作業は一切不要です！

2. ボタン操作で計算を実行する

シートを開いたときに自動実行されるのではなく、計算のタイミングをもっと直接的に制御したい場合は、コントロールボタンを活用しましょう。

コントロールボタンは、どの計算を使うかを選択したい場合に特に便利です。たとえば先ほどの例で、地域によって税率を5%と7%で使い分けたい場合はどうでしょうか？

CSVのインポートコードは自動実行のままにしておき、税額計算のコードだけを、対応するボタンが押されたときに実行するようにできます。

同じスプレッドシートで、「開発」タブを選択し、リボンの「コントロール」グループから「挿入」を選択します。ドロップダウンメニューからコマンドボタン（ActiveX コントロール）を選びましょう。

ボタンを、データが入る範囲を避けたシート上の任意の場所に描画します。

ボタンを右クリックして「プロパティ」を選択します。プロパティウィンドウでCaption（キャプション）を、ユーザーに表示したい任意の文字列に変更してください。ここでは「5%の税額を計算」などがよいでしょう。

このテキストがボタン表面にも反映されます。プロパティウィンドウを閉じ、ボタン自体をダブルクリックします。コードエディターウィンドウが開き、カーソルがボタン押下時に実行される関数内に置かれます。

前のセクションの税額計算コードをこの関数に貼り付け、税率の乗数は0.05のままにします。アクティブなシートを定義するために、次の2行を含めることを忘れないでください。

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

次に、同じ手順でもうひとつボタンを作成します。キャプションは「7%の税額を計算」とします。

そのボタンをダブルクリックして同じコードを貼り付けますが、税率の乗数は0.07に変更してください。

これで、どちらのボタンを押したかによって、taxes列がそれぞれの税率で計算されるようになります。

完成すると、シート上に2つのボタンが並びます。それぞれが異なる税額計算を起動し、結果列に異なる値を書き込みます。

動作を確認するには、「開発」メニューを選択し、リボンの「コントロール」グループから「デザインモード」を選択してデザインモードを無効にします。これでボタンが有効になります。

それぞれのボタンを押してみて、「taxes」の結果列がどう変化するか確認してみてください。

3. 計算結果をメールで送信する

スプレッドシート上の結果を、誰かにメールで送りたい場合はどうすればよいでしょうか？

先ほどと同じ手順で、「上司にシートをメール送信」という名前のボタンをもうひとつ作成できます。このボタン用のコードでは、ExcelのCDOオブジェクトを使ってSMTPメール設定を行い、結果を読みやすい形式でメール送信します。

この機能を有効にするには、「ツール」→「参照設定」を選択します。一覧を下にスクロールして「Microsoft CDO for Windows 2000 Library」を見つけ、チェックを有効にして「OK」を選択してください。

メール送信とスプレッドシート結果の埋め込みを実現するコードには、主に3つのセクションがあります。

まず最初は、件名、宛先（To）・差出人（From）アドレス、メール本文を保持する変数のセットアップです。

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

もちろん、本文はシート内の結果に応じて動的に生成する必要があります。そこで、範囲をループしてデータを抽出し、1行ずつ本文に書き足していく処理を追加します。

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

次のセクションでは、SMTPサーバー経由でメールを送信できるようSMTP設定を行います。Gmailを使用する場合、通常はGmailのメールアドレス、Gmailのパスワード、そしてGmailのSMTPサーバー（smtp.gmail.com）を指定します。

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

email@website.comとpasswordは、ご自身のアカウント情報に置き換えてください。

最後に、メール送信を開始するために、次のコードを挿入します。

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

注意：このコードの実行時にトランスポートエラーが発生する場合は、Googleアカウントが「安全性の低いアプリ」をブロックしている可能性があります。「安全性の低いアプリのアクセス」設定ページにアクセスし、この機能をオンにしてください。なお、現在のGoogleアカウントでは2段階認証プロセスの有効化と「アプリパスワード」の発行が必要になる場合がある点にも留意してください。

設定を有効にすると、メールが送信されます。これが、自動生成された結果メールを受信した側に表示される画面です。

このように、Excel VBAで自動化できることは実にたくさんあります。本記事で学んだコードスニペットをいろいろと試しながら、あなただけの独自のVBA自動化を作り上げてみてください。

Outlookのキャッシュをクリアする方法｜ファイルエクスプローラー・宛先候補・フォームキャッシュの削除手順

【初心者向け】VBA入門ガイド：開発タブの有効化から基本構文まで徹底解説

Excel VBAでオートフィルターが存在する場合に削除する7つの方法
Microsoft Excelでは、ワークシートやテーブルからオートフィルターを削除する方法が複数用意されています。この記事では、VBAコードを使って、Excelでオートフィルターが存在する場合にそれを削除する7つの方法を解説します。以下のリンクからサンプルのExcelファイルをダウンロードして、実際に手を動かしながら学習することもできます。 VBAでオートフィルターを削除する7つの具体例 1. アクティブなワークシートからオートフィルターを削除する次のスクリーンショットは、アクティブなワークシートに適用されているオートフィルターです。これをVBAコードで削除していきます。アクティブな
Excelで結合セルの行に交互に色を付ける方法【VBAで簡単実装】
Microsoft Excelで大量のデータを扱っていると、行ごとに交互に色を付けると、データが格段に見やすくなり、パターンや傾向も把握しやすくなります。しかし、セルが結合された表では、通常の条件付き書式だけではうまく対応できないことがあります。そこで活躍するのがVBAコードです。VBAを使えば、結合セルを含む表にも簡単に交互の行色を適用できます。この記事では、その具体的な手順をわかりやすく解説します。 Excelで結合セルの行に交互に色を付ける手順以下のサンプルデータでは、左端の「Category（カテゴリ）」列にさまざまな商品カテゴリ名が入力され、連続する行が「セルを結合」コマンドで結