MSExcel用の高度なVBAガイド

VBAを使い始めたばかりの場合は、初心者向けのVBAガイドの学習を開始することをお勧めします。ただし、経験豊富なVBAエキスパートであり、ExcelのVBAで実行できるより高度なことを探している場合は、読み続けてください。

ExcelでVBAコーディングを使用できることで、自動化の世界全体が開かれます。 Excelやプッシュボタンで計算を自動化したり、メールを送信したりすることもできます。 VBAを使用して日常業務を自動化する可能性は、想像以上に多くあります。

MicrosoftExcel用の高度なVBAガイド

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

以下は、ExcelでのVBAの最も一般的な使用法です。

• データをインポートして計算を実行する
• ユーザーがボタンを押した結果を計算する
• 計算結果を誰かにメールで送信

これらの3つの例を使用すると、さまざまな独自の高度なExcelVBAコードを記述できるはずです。

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

人々がExcelを使用する最も一般的なことの1つは、Excelの外部に存在するデータに対して計算を実行することです。 VBAを使用しない場合は、データを手動でインポートし、計算を実行して、それらの値を別のシートまたはレポートに出力する必要があることを意味します。

VBAを使用すると、プロセス全体を自動化できます。たとえば、毎週月曜日に新しい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オブジェクトを選択します。オブジェクトとメソッドのドロップダウンボックスから、ワークシートを選択します およびアクティブ化 。これにより、スプレッドシートを開くたびにコードが実行されます。

これにより、 Sub Worksheet_Activate（）が作成されます 関数。上記のコードをその関数に貼り付けます。

これにより、アクティブなワークシートが Sheet1に設定されます 、シートをクリアし、 strFileで定義したファイルパスを使用してファイルに接続します 変数、次に With ループはファイル内のすべての行を循環し、セルA1から始まるシートにデータを配置します。

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

インポートは最初のステップにすぎません。次に、計算結果を含む列の新しいヘッダーを作成します。この例では、各アイテムの販売に対して支払われる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")

'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ファイルから最新のデータのコピーが取得されます。次に、計算を実行し、結果をシートに書き込みます。もう手動で何もする必要はありません！

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

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

コントロールボタンは、使用する計算を制御する場合に便利です。たとえば、上記と同じ場合、ある地域に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電子メール設定を構成し、結果をユーザーが読み取り可能な形式で電子メールで送信することが含まれます。

この機能を有効にするには、ツールとリファレンスを選択する必要があります 。 Windows2000ライブラリ用のMicrosoftCDOまで下にスクロールします 、有効にして、 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 = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
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 protected]"
.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 protected]とパスワードを自分のアカウントの詳細に置き換えます。

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

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アカウントが「安全性の低いアプリ」の実行をブロックしている可能性があります。安全性の低いアプリの設定ページにアクセスして、この機能をオンにする必要があります。

それが有効になると、メールが送信されます。これは、自動生成された結果の電子メールを受信する人にはどのように見えるかです。

ご覧のとおり、ExcelVBAを使用して実際に自動化できるものはたくさんあります。この記事で学習したコードスニペットを試して、独自のVBA自動化を作成してください。