VBAを使い始めたばかりの場合は、初心者向けVBAガイド の学習を始めてください。ただし、経験豊富なVBAの専門家であり、ExcelでVBAを使用してできることをさらに高度なものを探している場合は、読み続けてください。自動化の。 Excel、プッシュボタンで計算を自動化し、電子メールを送信することもできます。気づくよりも、VBAを使用して日々の作業を自動化する可能性があります。
Microsoft Excelの高度なVBAガイド
ExcelでVBAコードを記述する主な目的は、情報を抽出できるようにすることですスプレッドシートからさまざまな計算を実行し、結果をスプレッドシートに書き戻します
ExcelでのVBAの最も一般的な使用方法は次のとおりです。
これらの3つの例では、さまざまな独自の高度なExcel VBAコードを記述できる。
データのインポートと計算の実行
人々がExcelを使用する最も一般的なものの1つExcelの外部に存在するデータに対して計算を実行しています。 VBAを使用しない場合、手動でデータをインポートし、計算を実行して、それらの値を別のシートまたはレポートに出力する必要があります。
<！-In_content_1 all：[300x250] / dfp ：[640x360]->
VBAを使用すると、プロセス全体を自動化できます。たとえば、毎週月曜日にコンピューターのディレクトリに新しいCSVファイルをダウンロードした場合、火曜日の朝にスプレッドシートを最初に開いたときに実行するようにVBAコードを構成できます。
次のインポートコードはCSVファイルを実行してExcelスプレッドシートにインポートします。
Dim ws As Worksheet, strFile As StringSet 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ファイルのデータが表示されます。 Sheet1の空白のスプレッドシートにインポートされます。
インポートは最初のステップにすぎません。次に、計算結果を含む列の新しいヘッダーを作成します。この例では、各アイテムの販売に対して支払われる5％の税金を計算するとします。
コードで実行するアクションの順序は次のとおりです。
次のコードはこれらのすべての手順を実行します。
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電子メール設定を構成し、ユーザーが読み取り可能な形式で結果を電子メールで送信します。
この機能を有効にするには、ツールと参照。 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 = "[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("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 protected]"
.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
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Replace [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アカウントが「安全性の低いアプリ」の実行をブロックしている可能性があります。 安全性の低いアプリの設定ページ にアクセスして、この機能をオンにする必要があります。
有効にすると、メールが送信されます。これは、自動生成された結果メールを受信する人にはどのように見えるかです。
ご覧のとおり、Excel VBAで実際に自動化できるものはたくさんあります。この記事で学んだコードスニペットを試してみて、独自のVBAオートメーションを作成してください。