MS Excel的高级VBA指南

如果您刚刚开始使用VBA，则需要开始研究我们的VBA初学者指南 。但是，如果您是经验丰富的VBA专家，并且正在寻找可以在Excel中使用VBA进行更高级的操作，那么请继续阅读。

在Excel中使用VBA编码的能力开辟了一个广阔的天地自动化。您可以在Excel，按钮甚至发送电子邮件中自动执行计算。用VBA自动化日常工作的可能性比您想象的要多。

Microsoft Excel的高级VBA指南

在Excel中编写VBA代码的主要目的是使您可以提取信息从电子表格中执行各种计算，然后将结果写回到电子表格中。

以下是Excel中VBA的最常用用法。

导入数据并执行计算

通过用户按下按钮来计算结果

将计算结果通过电子邮件发送给某人

使用这三个示例，您应该能够编写各种自己的高级Excel VBA代码。

导入数据和执行计算

人们使用Excel进行的最常见操作之一正在对Excel外部存在的数据执行计算。如果您不使用VBA，则意味着您必须手动导入数据，运行计算并将这些值输出到另一个工作表或报表中。

<！-

In_content_1全部：[300x250] / dfp ：[640x360]

->

使用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变量定义的文件路径连接到文件，然后使用循环在文件的每一行中循环，并将数据放置在从单元格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列中。

如果您希望直接控制何时运行计算，而不是在打开工作表时自动运行，而是可以使用控制按钮。

如果要控制使用哪些计算，则控制按钮很有用。例如，在与上述相同的情况下，如果您要对一个区域使用5％的税率，而对另一区域使用7％的税率呢？

您可以允许相同的CSV导入代码自动运行，但是当您按下相应的按钮时使税收计算代码运行。

使用与上述相同的电子表格，选择Developer标签，然后选择Insert（来自功能区中的控件组）。从下拉菜单中选择按钮ActiveX控件。

将按钮拖到工作表的任何部分上，远离任何数据。

右键单击按钮，然后选择属性。在“属性”窗口中，将标题更改为想要显示给用户的标题。在这种情况下，它可能是计算5％税。

您将看到此文本反映在按钮本身上。关闭属性窗口，然后双击按钮本身。这将打开代码编辑器窗口，并且光标将位于用户按下按钮时将运行的功能内。

将上一节中的税收计算代码粘贴到此函数中，使税率乘数保持为0.05。记住要包括以下两行来定义活动工作表。

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

现在，再次重复该过程，创建第二个按钮。将标题设为计算7％税。

双击该按钮并粘贴相同的代码，但是使税收乘数为0.07。

现在，根据您按下的按钮，税收列将

完成后，工作表上将同时具有两个按钮。他们每个人都会启动不同的税费计算，并将不同的结果写入结果列。

要为此输入文字，请选择Developer菜单，然后从功能区中的Controls组中选择Design Mode以禁用Design Mode。这将激活按钮。

尝试选择每个按钮以查看“税款”结果列的变化。

将计算结果发送给他人

如果要通过电子邮件将电子表格上的结果发送给某人？

您可以使用上面相同的步骤创建另一个名为向老板发送电子邮件表格的按钮。此按钮的代码将涉及使用Excel CDO对象配置SMTP电子邮件设置，并以用户可读的格式通过电子邮件发送结果。

要启用此功能，您需要选择工具和参考。向下滚动至用于Windows 2000库的Microsoft CDO，启用它，然后选择确定。

您需要创建三个主要部分来发送电子邮件并嵌入电子表格结果。

第一个是设置变量以保存主题，“收件人”和“发件人”地址以及电子邮件正文。

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

注意：如果您在尝试运行此代码时看到传输错误，则可能是因为您的Google帐户阻止了“安全程度较低的应用”运行。您需要访问安全性较低的应用程序设置页面 并将此功能打开。

启用后，您的电子邮件将被发送。收到自动生成的结果电子邮件的人就是这样。

您可以看到，实际上可以使用Excel VBA实现很多自动化。尝试处理您在本文中了解到的代码片段，并创建自己独特的VBA自动化。

0101 Excel简介及功能

相关文章: