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 宏代碼，您可以在日常工作中使用它們來自動執行任務並提高工作效率。