MS Excelの高度なVBAガイド：自動化をマスターする

VBAを始めたばかりの方は、まずVBA入門ガイドを学習することをお勧めします。しかし、VBAの経験豊富なユーザーで、ExcelでVBAを使ってより高度な操作を行いたいと考えている方は、このまま読み進めてください。

ExcelでVBAマークアップを使えるようになると、自動化の世界が一気に広がります。Excelの計算、ボタン操作、さらにはメール送信まで自動化できます。VBAを使えば、日々の業務を自動化できる可能性は想像以上に広がります。

Microsoft Excel の高度な VBA ガイド

Excel で VBA コードを記述する主な目的は、スプレッドシートから情報を抽出し、それに対してさまざまな計算を実行し、結果をスプレッドシートに書き戻すことです。

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

• データのインポートと計算の実行
• ユーザーのボタンクリックに基づいて結果を計算する
• 計算結果をメールで誰かに送信する

これら 3 つの例を使用すると、さまざまな独自の高度な Excel VBA コードを記述できるようになります。

データのインポートと計算の実行

Excelの最も一般的な用途の一つは、Excel外部のデータに対して計算を実行することです。VBAを使用していない場合、手動でデータをインポートし、計算を実行し、その結果を別のシートまたはレポートに出力する必要があります。

VBAを使えば、プロセス全体を自動化できます。例えば、毎週月曜日に新しいCSVファイルをコンピューターのディレクトリにダウンロードする場合、火曜日の朝にスプレッドシートを初めて開いたときにVBAコードが実行されるように設定できます。

次のインポート コードが実行され、CSV ファイルが Excel スプレッドシートにインポートされます。

Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With

Excel VBAエディターを開き、Sheet1オブジェクトを選択します。[オブジェクト]と[メソッド]のドロップダウンメニューから、[ワークシート]と[アクティブ化]を選択します。これにより、スプレッドシートを開くたびにコードが実行されます。

これにより、Sub Worksheet_Activate()関数が作成されます。上記のコードをこの関数に貼り付けてください。

これにより、アクティブなワークシートがSheet1に設定され、シートがスキャンされ、strFile変数で指定したファイルパスを使用してファイルに接続され、その後、Withループがファイルの各行を繰り返し処理し、セル A1 から始まるシートにデータを配置します。

このコードを実行すると、CSV ファイルのデータが空のスプレッドシートのSheet1にインポートされることがわかります。

インポートは最初のステップに過ぎません。次に、計算結果を格納する新しい列ヘッダーを作成します。この例では、各商品の売上に対して支払われる5%の税金を計算したいとします。

コードが実行する必要があるアクションの順序は次のとおりです。

1. 新しい結果列を作成します 税.
2. 列全体で繰り返す 販売台数 そして消費税を計算します。
3. 計算結果を紙の適切な行に記入してください。

次のコードはこれらすべてのステップを実行します。

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") '最後の行と列を検索します 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 次のセル

このコードはデータシートの最終行を見つけ、最初の行と最後の行のデータに基づいてセル範囲（販売価格を含む列）を設定します。その後、各セルを反復処理し、税額を計算し、結果を新しい列（列5）に書き込みます。

上記のVBAコードを前のコードの下に貼り付け、スクリプトを実行します。結果がE列に表示されます。

これで、Excelワークシートを開くたびに、プログラムが自動的にCSVファイルから最新のデータを取得し、計算を実行して結果をワークシートに書き込むようになります。もう手動で何かを行う必要はありません！

ボタンを押して結果を計算します

ワークシートを開いたときに計算を自動的に実行するのではなく、計算の実行タイミングを直接制御したい場合は、代わりにコントロール ボタンを使用できます。

コントロールボタンは、計算方法を制御したいという場合に便利です。例えば、上記と同じシナリオで、ある地域では5%の税率を使用し、別の地域では7%の税率を使用したい場合、どうすればよいでしょうか？

同じ CSV インポート コードを自動的に実行できるようにしながら、適切なボタンが押されたときに税金計算コードが実行されるようにすることができます。

上記と同じスプレッドシートを使用して、[開発]タブを選択し、リボンの[コントロール]グループから[挿入]を選択します。ドロップダウン メニューから[ActiveXプッシュ ボタンコントロール]を選択します。

データが入る場所から離れた紙の任意の部分にプッシュ ボタンを描きます。

ボタンを右クリックして「プロパティ」を選択します。プロパティウィンドウで、「キャプション」をユーザーに表示したい内容に変更します。この場合は、「5%の税金を計算」とします。

このテキストはボタン自体にも反映されます。プロパティウィンドウを閉じて、ボタンをダブルクリックしてください。するとコードエディタウィンドウが開き、カーソルはユーザーがボタンをクリックしたときに実行される関数内に移動します。

上記のセクションの税計算コードをこの関数に貼り付けます。税率の乗数は0.05のままにしてください。アクティブシートを識別するために、次の2行を忘れずに含めてください。

Dim ws As Worksheet、strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")

次に、同じ手順を繰り返して、2つ目のボタンを作成します。そのボタンには「7%税額計算」というラベルを付けます。

このボタンをダブルクリックして同じコードを貼り付けますが、税額乗数を 0.07 にします。

これで、どのボタンを押したかに応じて、税金の列が計算されます。

完了すると、シートに両方のボタンが表示されます。それぞれ異なる税金計算が開始され、結果列に異なる結果が表示されます。

これをテストするには、[開発者]メニューを選択し、リボンのコントロール グループから[デザイン モード]を選択して[デザイン モード]を無効にします。これにより、ボタンが有効になります。

各ボタンを選択して、「税金」の結果列がどのように変化するかを確認してください。

アカウントの結果を誰かにメールで送信する

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

上記の手順と同じ方法で、 「シートを上司にメールで送信」という別のボタンを作成できます。このボタンのコードは、Excel CDOオブジェクトを使用してSMTPメール設定を構成し、結果をユーザーフレンドリーな形式でメールで送信します。

この機能を有効にするには、 「ツール」と「参照設定」を選択します。 「Microsoft CDO for Windows 2000 ライブラリ」までスクロールダウンし、有効にして「OK」を選択します。

電子メールを送信し、スプレッドシートの結果を含めるには、作成する必要がある 3 つの主要なコード セクションがあります。

1 つ目は、件名、宛先アドレス、送信元アドレス、メール テキストを保持するための変数を設定することです。

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 = "今四半期に支払った税金" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "以下は、今四半期の売上に対して支払われた税金の内訳です。"

もちろん、論文の結果に応じてテキストは動的である必要があるため、ここでは範囲を調べてデータを抽出し、テキストに毎回 1 行書き込むループを追加する必要があります。

StartCell = Range("A1").vbCrLf For Each cell In rng strBody = strBody & vbCrLf strBody = strBody & "「& Cells(rowCounter, 3).Value & "/" & Cells(rowCounter, 1).Value _ & "を「& Cells(rowCounter, 4).Value & "で販売し、「& Cells(rowCounter, 5).Value & "の税金を支払いました。" rowCounter = rowCounter + 1 次のセル

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

CDO_Mail を CreateObject("CDO.Message") に設定し、エラーの場合は Error_Handling を実行します。CDO_Config を CreateObject("CDO.Configuration") に設定し、CDO_Config.Load -1 を実行します。SMTP_Config を CDO_Config.Fields に設定します。SMTP_Config を使用します。.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True .Configuration = CDO_Config を設定して終了します

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アカウントで「安全性の低いアプリ」オプションがブロックされている可能性があります。その場合は、「安全性の低いアプリ」の設定ページにアクセスして、このオプションを有効にする必要があります。

これを有効にすると、メールが送信されます。自動生成されたメールの受信者には、このように表示されます。

ご覧の通り、Excel VBAを使えば自動化できるものがたくさんあります。この記事で紹介したコードスニペットを試してみて、自分だけのVBA自動化を作成してみてください。