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オブジェクトを選択します。オブジェクトとメソッドのドロップダウンリストボックスから、 ワークシート و アクティブにしましょうこれにより、スプレッドシートを開くたびにコードが実行されるようになります。

これにより関数が作成されます サブワークシート_アクティブ化()上記のコードをこの関数に貼り付けます。

これにより、アクティブなワークシートが Sheet1シートをスキャンし、変数で指定したファイル パスを使用してファイルに接続します。 strファイルそしてループ 自律的AI ファイル内の各行を調べて、セル 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 インポート コードを自動的に実行できるようにしながら、適切なボタンが押されたときに税金計算コードが実行されるようにすることができます。

上記と同じデータテーブルを使用して、タブを選択します Developer、を選択します インサート グループから コントロール リボンでコントロールを選択します。 ボタンを押す ドロップダウン メニューから 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 を設定して終了します

交換 メール@website.com パスワードにはアカウントの詳細が含まれています。

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

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自動化を作成してみてください。