微軟Excel高階VBA指南：掌握自動化

如果你剛開始使用VBA，你需要先學習一些基礎知識。 VBA入門指南但如果您是一位經驗豐富的 VBA 專家，並且正在尋找可以使用 VBA 在 Excel 中執行的更高級的操作，請繼續閱讀。

在 Excel 中使用 VBA 標記語言，可以開啟一個自動化的全新世界。您可以自動執行 Excel 計算、按鈕操作，甚至發送電子郵件。 VBA 在自動化日常工作方面擁有比您想像中更多的可能性。

Microsoft Excel 進階 VBA 指南

在 Excel 中編寫 VBA 程式碼的主要目的是能夠從電子表格中提取訊息，對其執行各種計算，然後將結果寫回電子表格。

以下是 VBA 在 Excel 中最常見的用途。

• 導入資料並執行計算
• 根據使用者按鈕點擊情況計算結果
• 將計算結果透過電子郵件發送給某人

透過這三個例子，你應該能夠編寫各種自己的高階 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). .TextFileCommaDelimiter = True .Refresh End With

開啟 Excel VBA 編輯器並選擇 Sheet1 物件。從「物件」和「方法」下拉列錶框中，選擇 下載學習單 和 啟用這樣一來，每次打開電子表格時，程式碼都會運行。

這將創建一個函數 子工作表啟動()將上面的程式碼貼到這個函數中。

這將活動工作表設定為 Sheet1掃描表格，並使用您在變數中指定的檔案路徑連線到該檔案。 字符串文件然後是循環 與 透過遍歷文件中的每一行，並將資料放置在工作表中，從儲存格 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.Ro 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 = cell.Value * 0.05 Cells(rowCounter, 5) extT1

這段程式碼會找到資料表的最後一行，然後根據資料的第一行和最後一行設定儲存格範圍（包含銷售價格的欄位）。接下來，程式碼會遍歷這些儲存格，計算稅額，並將結果寫入新欄位（第 5 欄）。

將上面的 VBA 程式碼貼到先前的程式碼下方，然後執行腳本。您將在 E 列看到結果。

現在，每次開啟 Excel 工作表時，程式都會自動從 CSV 檔案中取得最新版本的資料。然後，它會自動執行計算並將結果寫入工作表。您再也不用手動操作了！

按下按鈕計算結果

如果您希望直接控制何時執行計算，而不是在開啟工作表時自動執行計算，則可以改用控制按鈕。

如果您想控制所使用的計算方法，控制按鈕會很有用。例如，在上述相同場景中，如果您想對一個地區使用 5% 的稅率，對另一個地區使用 7% 的稅率，該怎麼辦？

您可以允許相同的 CSV 匯入程式碼自動執行，但讓稅金計算程式碼在按下對應的按鈕時運行。

使用上面提到的同一個資料表，選擇選項卡 開發者 ，並選擇 插入 來自團體 Controls 在功能區中，選擇一個控制項。 按鈕 從下拉式選單中選擇 ActiveX。

在紙上任何位置畫出按鈕，避開任何資料會顯示的地方。

右鍵單擊按鈕，然後選擇 產品特性在「屬性」視窗中，將「標題」變更為您希望向使用者顯示的內容。在本例中，它可能是： 計算 5% 的稅額.

您會在按鈕上看到這段文字。關閉視窗 特性雙擊同一個按鈕。這將打開一個程式碼編輯器窗口，而遊標將位於用戶單擊按鈕時要執行的函數內部。

將上面部分的稅額計算代碼貼到此函數中，並將稅率乘數保持在 0.05。請記住包含以下兩行以標識當前活動的工作表。

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

現在，重複上述步驟，建立第二個按鈕。給它命名。 計算7%的稅額.

雙擊此按鈕並貼上相同的代碼，但將稅率乘數設為 0.07。

現在，根據你按下的按鈕，稅額列將會相應地進行計算。

完成後，表格中將出現這兩個按鈕。每個按鈕都會啟動不同的稅金計算，並在結果欄位中顯示不同的結果。

若要測試此功能，請選擇列表 開發商，並選擇 設計模式 從功能區控制組中停用 設計模式這將激活按鈕。

嘗試選擇每個按鈕，看看「稅費」結果欄如何變化。

將帳戶結果透過電子郵件發送給某人

如果你想把電子表格中的結果透過電子郵件發送給其他人呢？

您可以建立另一個名為「按鈕」的按鈕 寄email給老闆表格 使用上述相同的步驟，此按鈕的程式碼將使用 Excel CDO 物件來配置 SMTP 電子郵件設置，並以使用者友好的格式透過電子郵件發送結果。

若要啟用此功能，您需要選擇 工具和參考資料. 向下滾動到 Microsoft CDO for Windows 2000 函式庫啟用它，然後選擇 OK.

要發送電子郵件並包含電子表格結果，您需要建立三個主要的程式碼部分。

首先，設定變數以保留主題、「收件者」和「寄件者」地址以及電子郵件正文。

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。 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 = "以下為明細。

當然，文本需要根據論文中的結果動態變化，因此你需要添加一個循環，遍歷該範圍，提取數據，並每次在文本中寫入一行。

設定起始儲存格 = Range("A1").vbCrLf for rng 中的每個儲存格 = Range("A1").vbCrLf for rng 中的每個儲存格 strBody = strBody & vbCrLf strBody = strBody & "我們賣出了 " & Cells(rowCounter, 3).Value & " 件，共 " & Cells(rowCounter, 1)。 " & Cells(rowCounter, 5).Value & " 筆的稅金。

下一部分將介紹如何設定 SMTP，以便您可以透過 SMTP 伺服器傳送電子郵件。如果您使用 Gmail，通常需要提供您的 Gmail 電子郵件地址、Gmail 密碼和 Gmail SMTP 伺服器位址 (smtp.gmail.com)。

設定 CDO_Mail = CreateObject("CDO.Message") On Error GoTo Error_Handling 設定 CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 設定 SMTP_Config = CDO_Config.Fiel Withds SM_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/cdo/config .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoftconfiguration/sendpassword") = "password" 。 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Set .Configuration = CDO_Config End With

代替 電子郵件@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.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send 錯誤處理：若 Err.Description

筆記如果您在嘗試執行此程式碼時看到傳輸錯誤，則可能是您的 Google 帳戶阻止了「安全性較低的應用程式」運作。您需要訪問[連結/參考]。 安全性較低的應用程式設定頁面 啟用此選項。

啟用此功能後，您的電子郵件將會傳送。以下是收件者看到的自動產生郵件內容。

如您所見，使用 Excel VBA 可以實現許多自動化操作。不妨嘗試運用本文中學到的程式碼片段，建立您自己獨特的 VBA 自動化程式。