如果您刚刚开始使用VBA，那么您将想要开始学习我们的VBA 初学者指南(VBA guide for beginners)。但是，如果您是一位经验丰富的VBA专家，并且您正在寻找可以在Excel中使用(Excel)VBA执行的更高级的操作，那么请继续阅读。
在Excel(Excel)中使用VBA编码的能力打开了整个自动化世界。您可以在Excel中自动执行计算、按钮，甚至发送电子邮件。使用VBA自动化日常工作的可能性比您想象的要多。
Microsoft Excel 高级 VBA 指南(Advanced VBA Guide For Microsoft Excel)
在Excel中编写(Excel)VBA代码的主要目标是，您可以从电子表格中提取信息，对其执行各种计算，然后将结果写回电子表格
以下是VBA在Excel中最常见的用法。
- 导入(Import)数据并执行计算
- 计算(Calculate)用户按下按钮的结果
- 通过电子邮件(Email)将计算结果发送给某人
通过这三个示例，您应该能够编写各种自己的高级Excel VBA代码。
导入数据并执行计算(Importing Data and Performing Calculations)
人们使用Excel最常见的事情之一是对存在于Excel之外的数据执行计算。如果您不使用VBA，这意味着您必须手动导入数据、运行计算并将这些值输出到另一个工作表或报告。
使用VBA，您可以自动化整个过程。例如，如果您每周一将新的(Monday)CSV文件下载到计算机上的目录中，则可以将VBA代码配置为在(VBA)周二(Tuesday)早上第一次打开电子表格时运行。
以下导入代码将运行并将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对象。从对象和方法下拉框中，选择Worksheet和Activate。这将在您每次打开电子表格时运行代码。
这将创建一个Sub Worksheet_Activate()函数。将上面的代码粘贴到该函数中。
这会将活动工作表设置为Sheet1 ，清除工作表，使用您使用(Sheet1)strFile变量定义的文件路径连接到文件，然后With循环遍历文件中的每一行并将数据从单元格 A1 开始放入工作表中.
如果您运行此代码，您会看到CSV文件数据已导入到空白电子表格的Sheet1 中(Sheet1)。
导入只是第一步。接下来，您要为包含计算结果的列创建一个新标题。在此示例中，假设您要计算每件商品销售时支付的 5% 税款。
您的代码应采取的操作顺序是：
- 创建名为tax(taxes)的新结果列。
- 遍历销售单位(units sold)列并计算销售税。
- 将计算结果写入工作表中的相应行。
以下代码将完成所有这些步骤。
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(VBA)代码粘贴到前面的代码下面，然后运行脚本。您将看到结果显示在 E 列中。
现在，每次打开Excel工作表时，它都会自动退出并从CSV文件中获取最新的数据副本。然后，它将执行计算并将结果写入工作表。您无需再手动执行任何操作！
按下按钮计算结果(Calculate Results From Button Press)
如果您希望更直接地控制计算的运行时间，而不是在工作表打开时自动运行，则可以使用控制按钮。
(Control)如果您想控制使用哪些计算，控制按钮很有用。例如，在与上述相同的情况下，如果您想对一个地区使用 5% 的税率，而对另一个地区使用 7% 的税率怎么办？
您可以允许相同的CSV导入代码自动运行，但在您按下相应按钮时让税款计算代码运行。
使用与上面相同的电子表格，选择“开发人员(Developer)”选项卡，然后从功能区的“控件(Controls)”组中选择“插入”。(Insert)从下拉菜单中选择按钮(push button)ActiveX 控件。(ActiveX Control)
将按钮绘制到远离任何数据所在的工作表的任何部分。
右键单击按钮，然后选择Properties。在“属性(Properties)”窗口中，将标题更改为您希望向用户显示的内容。在这种情况下，它可能是Calculate 5% Tax。
您将看到此文本反映在按钮本身上。关闭属性(properties)窗口，然后双击按钮本身。这将打开代码编辑器窗口，您的光标将位于用户按下按钮时将运行的函数内。
将上述部分的税收计算代码粘贴到此函数中，保持税率乘数为 0.05。请记住包括以下 2 行来定义活动工作表。
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
现在，再次重复该过程，创建第二个按钮。制作标题Calculate 7% Tax。
双击(Double-click)该按钮并粘贴相同的代码，但将税收乘数设为 0.07。
现在，根据您按下的按钮，将相应地计算税款列。
完成后，您的工作表上将有两个按钮。他们每个人都会启动不同的税收计算，并将不同的结果写入结果列。
为此，请选择“开发人员(Developer)”菜单，然后从功能区的“控件”组中选择“(Controls)设计模式(Design Mode)”以禁用“设计模式”(Design Mode)。这将激活按钮。
尝试选择每个按钮以查看“税”结果列如何变化。
通过电子邮件将计算结果发送给某人(Email Calculation Results to Someone)
如果您想通过电子邮件将电子表格上的结果发送给某人怎么办？
您可以使用上述相同的过程创建另一个名为“向老板发送电子邮件表”的按钮。(Email Sheet to Boss)此按钮的代码将涉及使用Excel CDO对象来配置SMTP电子邮件设置，并以用户可读的格式通过电子邮件发送结果。
要启用此功能，您需要选择Tools and References。向下滚动到Microsoft CDO for Windows 2000 Library，启用它，然后选择OK。
您需要创建用于发送电子邮件和嵌入电子表格结果的代码的三个主要部分。
首先是设置变量来保存主题、收件人和发件人(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."
当然，主体需要是动态的，具体取决于工作表中的结果，因此您需要在此处添加一个遍历范围、提取数据并一次将一行写入主体的循环。
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
用您自己的帐户详细信息替换[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
注意(Note)：如果您在尝试运行此代码时看到传输错误，可能是因为您的Google帐户阻止了“不太安全的应用程序”运行。您需要访问不太安全的应用设置页面(less secure apps settings page)并打开此功能。
启用后，您的电子邮件将被发送。这就是收到您自动生成的结果电子邮件的人的样子。
如您所见，您实际上可以使用Excel VBA实现很多自动化。尝试使用您在本文中了解的代码片段，并创建您自己独特的VBA自动化。
An Advanced VBA Guide For MS Excel
If you are just getting started with VΒA, then you’ll want tо start out studying our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.
The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.
Advanced VBA Guide For Microsoft Excel
The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet
The following are the most common uses of VBA in Excel.
- Import data and perform calculations
- Calculate results from a user pressing a button
- Email calculation results to someone
With these three examples, you should be able to write a variety of your own advanced Excel VBA code.
Importing Data and Performing Calculations
One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.
With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.
The following import code will run and import the CSV file into your Excel spreadsheet.
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
Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.
This will create a Sub Worksheet_Activate() function. Paste the code above into that function.
This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.
If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.
Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.
The order of actions your code should take is:
- Create new results column called taxes.
- Loop through the units sold column and calculate the sales tax.
- Write the calculate results to the appropriate row in the sheet.
The following code will accomplish all of these steps.
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
This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).
Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.
Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!
Calculate Results From Button Press
If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.
Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?
You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.
Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.
Draw the pushbutton onto any part of the sheet away from where any data will go.
Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.
You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.
Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.
Double-click that button and paste the same code, but make the tax multiplier 0.07.
Now, depending which button you press, the taxes column will be calculated accordingly.
Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column.
To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons.
Try selecting each push button to see how the “taxes” result column changes.
Email Calculation Results to Someone
What if you want to send the results on the spreadsheet to someone via email?
You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.
To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.
There are three main sections to the code you need to create to send out an email and embed spreadsheet results.
The first is setting up variables to hold the subject, To and From addresses, and the email body.
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."
Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.
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
The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (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] and password with your own account details.
Finally, to initiate the email send, insert the following code.
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
Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.
After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.
As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.
