24个有用的Excel 宏示例给VBA 初学者（即用型）

使用Excel 宏可以加快工作速度并节省大量时间。

获取VBA 代码的一种方法是记录宏并获取它生成的代码。但是，宏记录器的代码通常充满了并不真正需要的代码。宏记录器也有一些限制。

因此，拥有一组有用的VBA 宏代码是值得的，您可以将它们放在您的后兜里，并在需要时使用它。

虽然最初编写Excel VBA 宏代码可能需要一些时间，但一旦完成，您可以将其作为参考，并在下次需要时使用它。

在这篇庞大的文章中，我将列出一些我经常需要的有用的Excel 宏示例，并保存在我的私人保管库中。

我将继续使用更多宏示例更新本教程。如果您认为某些内容应该在列表中，请发表评论。

您可以将此页面添加为书签以供将来参考。

现在，在我进入宏示例并为您提供VBA 代码之前，让我首先向您展示如何使用这些示例代码。

使用Excel 宏示例中的代码

以下是使用任何示例中的代码需要遵循的步骤：

• 打开要在其中使用宏的工作簿。
• 按住ALT 键并按F11。这将打开VB 编辑器。
• 右键单击项目资源管理器中的任何对象。
• 转到插入-> 模块。
• 复制并粘贴模块代码窗口中的代码。

如果示例说您需要将代码粘贴到工作表代码窗口中，请双击工作表对象并将代码复制粘贴到代码窗口中。

在工作簿中插入代码后，您需要使用.XLSM 或.XLS 扩展名保存它。

如何运行宏

在VB 编辑器中复制代码后，运行宏的步骤如下：

• 转到开发人员选项卡。
• 单击宏。

• 在“宏”对话框中，选择要运行的宏。
• 单击运行按钮。

如果您在功能区中找不到开发人员选项卡，请阅读本教程以了解如何获取它。

如果代码粘贴在工作表代码窗口中，您无需担心运行代码。当指定的动作发生时它会自动运行。

现在，让我们进入有用的宏示例，这些示例可以帮助您自动化工作并节省时间。

注意：您会发现很多撇号(') 后跟一两行。这些是在运行代码时被忽略的注释，并作为自我/读者的注释放置。

如果您在文章或代码中发现任何错误，请大神告诉我。

Excel 宏示例

本文介绍了以下宏示例：

一次性取消隐藏所有工作表

如果您正在处理具有多个隐藏工作表的工作簿，则需要逐个取消隐藏这些工作表。如果有很多隐藏的工作表，这可能需要一些时间。

这是取消隐藏工作簿中所有工作表的代码。

'This code will unhide all sheets in the workbook Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub

上面的代码使用VBA 循环（For Each）来遍历工作簿中的每个工作表。然后它将工作表的可见属性更改为可见。

这是有关如何使用各种方法在Excel 中取消隐藏工作表的详细教程。

隐藏除活动工作表之外的所有工作表

如果您正在处理报表或仪表板，并且想要隐藏除具有报表/仪表板的工作表之外的所有工作表，则可以使用此宏代码。

'This macro will hide all the worksheet except the active sheet Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

使用VBA 按字母顺序对工作表进行排序

如果您有一个包含许多工作表的工作簿，并且您想按字母顺序对这些工作表进行排序，那么此宏代码可以派上用场。如果您将工作表名称作为年份或员工姓名或产品名称，则可能会出现这种情况。

'This code will sort the worksheets alphabetically Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

一次性保护所有工作表

如果工作簿中有很多工作表，并且想要保护所有工作表，则可以使用此宏代码。

它允许您在代码中指定密码。您将需要此密码来取消对工作表的保护。

'This code will protect all the sheets at one go Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'replace Test123 with the password you want For Each ws In Worksheets ws.Protect password:=password Next ws End Sub

一次性取消保护所有工作表

如果您保护了部分或全部工作表，则只需对用于保护工作表的代码稍作修改即可取消保护。

'This code will protect all the sheets at one go Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'replace Test123 with the password you want For Each ws In Worksheets ws.Unprotect password:=password Next ws End Sub

请注意，密码需要与用于锁定工作表的密码相同。如果不是，您将看到一个错误。

取消隐藏所有行和列

此宏代码将取消隐藏所有隐藏的行和列。

如果您从其他人那里获取文件并希望确保没有隐藏的行/列，这可能会非常有用。

'This code will unhide all the rows and columns in the Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

取消合并所有合并的单元格

将单元格合并为一个是一种常见的做法。虽然它可以工作，但当单元格合并时，您将无法对数据进行排序。

如果您正在使用包含合并单元格的工作表，请使用下面的代码一次性取消合并所有合并的单元格。

'This code will unmerge all the merged cells Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

请注意，我建议使用“跨选区居中”选项，而不是“合并和居中”选项。

保存名称中带有时间戳的工作簿

很多时候，您可能需要创建工作的版本。这些对于随着时间的推移处理文件的长期项目非常有用。

一个好的做法是使用时间戳保存文件。

使用时间戳将允许您返回到某个文件以查看进行了哪些更改或使用了哪些数据。

这是自动将工作簿保存在指定文件夹中并在保存时添加时间戳的代码。

'This code will Save the File With a Timestamp in its name Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp End Sub

您需要指定文件夹位置和文件名。

在上面的代码中，“C:UsersUsernameDesktop 是我使用的文件夹位置。您需要指定要保存文件的文件夹位置。此外，我使用了通用名称“WorkbookName”作为文件名前缀。您可以指定与您的项目或公司相关的内容。

将每个工作表另存为单独的PDF

如果您使用不同年份或部门或产品的数据，您可能需要将不同的工作表另存为PDF 文件。

虽然如果手动完成这可能是一个耗时的过程，但VBA 确实可以加快速度。

这是一个VBA 代码，它将每个工作表保存为单独的PDF。

'This code will save each worsheet as a separate PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

在上面的代码中，我指定了要保存PDF 的文件夹位置的地址。此外，每个PDF 将获得与工作表相同的名称。您将不得不修改此文件夹位置（除非您的名字也是Sumit 并且您将其保存在桌面上的测试文件夹中）。

请注意，此代码仅适用于工作表（而不适用于图表表）。

将每个工作表另存为单独的PDF

这是将整个工作簿保存为指定文件夹中的PDF 的代码。

'This code will save the entire workbook as PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

您必须更改文件夹位置才能使用此代码。

将所有公式转换为值

当您有一个包含大量公式的工作表并且想要将这些公式转换为值时，请使用此代码。

'This code will convert all formulas into values Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

此代码自动识别使用的单元格并将其转换为值。

使用公式保护/锁定单元格

当您有大量计算并且不想意外删除或更改它时，您可能希望使用公式锁定单元格。

这是将锁定所有具有公式的单元格的代码，而所有其他单元格均未锁定。

'This macro code will lock all the cells with formulas Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

相关教程：如何在Excel 中锁定单元格。

保护工作簿中的所有工作表

使用以下代码一次性保护工作簿中的所有工作表。

'This code will protect all sheets in the workbook Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub

此代码将一一遍历所有工作表并对其进行保护。

如果您想取消保护所有工作表，请在代码中使用ws.Unprotect 而不是ws.Protect。

在所选内容的每一行之后插入一行

当您想在所选范围内的每一行之后插入一个空白行时，请使用此代码。

'This code will insert a row after every row in the selection Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next i End Sub

同样，您可以修改此代码以在所选范围内的每一列之后插入一个空白列。

在相邻单元格中自动插入日期和时间戳

时间戳是您想要跟踪活动时使用的东西。

例如，您可能想要跟踪活动，例如特定费用的发生时间、销售发票的创建时间、单元格中的数据输入时间、报告的最后更新时间等。

输入或编辑现有内容时，使用此代码在相邻单元格中插入日期和时间戳。

'This code will insert a timestamp in the adjacent cell Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

请注意，您需要将此代码插入工作表代码窗口中（而不是像我们迄今为止在其他Excel 宏示例中所做的那样在模块代码窗口中插入）。为此，在VB 编辑器中，双击您想要此功能的工作表名称。然后将此代码复制并粘贴到该工作表的代码窗口中。

此外，当在A 列中完成数据输入时，此代码可以工作（请注意，代码具有Target.Column = 1 行）。您可以相应地更改它。

突出显示选择中的备用行

突出显示交替行可以极大地提高数据的可读性。当您需要打印出来并浏览数据时，这可能很有用。

这是一个代码，它将立即突出显示选择中的备用行。

'This code would highlight alternate rows in the selection Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

请注意，我在代码中将颜色指定为vbCyan。您也可以指定其他颜色（例如vbRed、vbGreen、vbBlue）。

突出显示拼写错误的单元格

Excel 没有Word 或PowerPoint 中的拼写检查。虽然您可以通过按F7 键来运行拼写检查，但在出现拼写错误时没有视觉提示。

使用此代码可立即突出显示所有有拼写错误的单元格。

'This code will highlight the cells that have misspelled words Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub

请注意，突出显示的单元格是具有Excel 认为是拼写错误的文本的单元格。在许多情况下，它还会突出显示它不理解的名称或品牌术语。

刷新工作簿中的所有数据透视表

如果工作簿中有多个数据透视表，则可以使用此代码一次刷新所有这些数据透视表。

'This code will refresh all the Pivot Table in the Workbook Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

您可以在此处阅读有关刷新数据透视表的更多信息。

将所选单元格的字母大小写更改为大写

虽然Excel 具有更改文本字母大小写的公式，但它使您可以在另一组单元格中执行此操作。

使用此代码可立即更改所选文本中文本的字母大小写。

'This code will change the Selection to Upper Case Sub ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

请注意，在这种情况下，我使用UCase 将文本大小写设置为大写。您可以将LCase 用于小写。

用注释突出显示所有单元格

使用以下代码突出显示其中包含注释的所有单元格。

'This code will highlight cells that have comments` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

在这种情况下，我使用vbBlue 为单元格赋予蓝色。如果需要，您可以将其更改为其他颜色。

使用VBA 突出显示空白单元格

虽然您可以使用条件格式或使用“转到特殊”对话框突出显示空白单元格，但如果您必须经常这样做，最好使用宏。

创建后，您可以在快速访问工具栏中拥有此宏或将其保存在您的个人宏工作簿中。

这是VBA宏代码：

'This code will highlight all the blank cells in the dataset Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

在这段代码中，我指定了以红色突出显示的空白单元格。您可以选择其他颜色，例如蓝色、黄色、青色等。

如何按单列对数据进行排序

您可以使用以下代码按指定列对数据进行排序。

Sub SortDataHeader() 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes 
END SUB

请注意，我创建了一个名为“DataRange”的命名范围，并使用它代替了单元格引用。

这里还使用了三个关键参数：

• Key1 – 这是您要对数据集进行排序的键。在上面的示例代码中，数据将根据A 列中的值进行排序。
• Order1 – 在这里您需要指定是否要按升序或降序对数据进行排序。
• Header – 在这里您需要指定您的数据是否有标头。

阅读有关如何使用VBA 在Excel 中对数据进行排序的更多信息。

如何按多列对数据进行排序

假设您有一个如下所示的数据集：

下面是基于多列对数据进行排序的代码：

Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1:C13") .Header = xlYes .Apply End With End Sub

请注意，这里我已指定首先基于A 列排序，然后基于B 列排序。

输出将如下所示：

如何从Excel 中的字符串中仅获取数字部分

如果您只想从字符串中提取数字部分或文本部分，您可以在VBA 中创建自定义函数。

然后，您可以在工作表中使用此VBA 函数（就像常规Excel 函数一样），它将仅从字符串中提取数字或文本部分。

如下图所示：

下面是VBA 代码，它将创建一个从字符串中提取数字部分的函数：

'This VBA code will create a function to get the numeric part from a string Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

您需要将代码放入模块中，然后您可以在工作表中使用函数=GetNumeric。

此函数将只接受一个参数，即要从中获取数字部分的单元格的单元格引用。

同样，下面的函数将只从Excel 中的字符串中获取文本部分：

'This VBA code will create a function to get the text part from a string Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function

因此，这些是一些有用的Excel 宏代码，您可以在日常工作中使用它们来自动执行任务并提高工作效率。