24 ví dụ macro Excel hữu ích cho người mới bắt đầu VBA (sẵn sàng sử dụng)

Sử dụng macro Excel có thể tăng tốc công việc của bạn và tiết kiệm rất nhiều thời gian.

Một cách để lấy mã VBA là ghi macro và lấy mã mà nó tạo ra.Tuy nhiên, mã của trình ghi macro thường chứa đầy mã không thực sự cần thiết.Trình ghi macro cũng có một số hạn chế.

Vì vậy, đáng để sở hữu một bộ macro VBA hữu ích mà bạn có thể giữ trong túi sau của mình và sử dụng khi cần.

Mặc dù có thể mất một khoảng thời gian để viết mã macro VBA trong Excel ban đầu, nhưng khi bạn đã hoàn tất, bạn có thể sử dụng nó làm tài liệu tham khảo và sử dụng nó vào lần tiếp theo bạn cần.

Trong bài viết khổng lồ này, tôi sẽ liệt kê một số ví dụ macro Excel hữu ích mà tôi thường cần và giữ trong kho riêng của mình.

Tôi sẽ tiếp tục cập nhật hướng dẫn này với nhiều ví dụ macro hơn.Xin vui lòng để lại một bình luận nếu bạn nghĩ một cái gì đó nên có trong danh sách.

Bạn có thể đánh dấu trang này để tham khảo trong tương lai.

Bây giờ, trước khi tôi đi vào các ví dụ macro và cung cấp cho bạn mã VBA, trước tiên hãy để tôi hướng dẫn bạn cách sử dụng mã ví dụ.

Sử dụng mã từ ví dụ macro Excel

Dưới đây là các bước cần làm theo để sử dụng mã từ bất kỳ ví dụ nào:

• Mở sổ làm việc mà bạn muốn sử dụng macro.
• Giữ phím ALT và nhấn F11.Điều này sẽ mở raBiên tập viên VB.
• Nhấp chuột phải vào bất kỳ đối tượng nào trong Project Explorer.
• Đi tới Chèn -> Mô-đun.
• Sao chép và dán mã vào cửa sổ mã mô-đun.

Nếu ví dụ cho biết bạn cần dán mã vào cửa sổ mã trang tính, hãy bấm đúp vào đối tượng trang tính và sao chép-dán mã vào cửa sổ mã.

Sau khi chèn mã vào sổ làm việc, bạn cần lưu nó với phần mở rộng .XLSM hoặc .XLS.

Cách chạy macro

Sau khi sao chép mã trong trình soạn thảo VB, các bước để chạy macro như sau:

• Chuyển đến tab nhà phát triển.
• Nhấp vào Macro.

• Trong hộp thoại Macro, hãy chọn macro để chạy.
• Nhấp vào nút Chạy.

Nếu bạn không thể tìm thấy tab nhà phát triển trong ruy-băng, hãy đọc hướng dẫn này để tìm hiểu cách lấy nó.

Nếu mã được dán trong cửa sổ mã trang tính, bạn không cần phải lo lắng về việc chạy mã.Nó chạy tự động khi hành động được chỉ định xảy ra.

Bây giờ, hãy đi vào các ví dụ macro hữu ích có thể giúp bạn tự động hóa công việc và tiết kiệm thời gian.

Lưu ý: Bạn sẽ tìm thấy nhiều dấu nháy đơn (') theo sau bởi một hoặc hai dòng.Đây là những nhận xét bị bỏ qua khi chạy mã và được đặt dưới dạng nhận xét của bản thân / người đọc.

Nếu bạn tìm thấy bất kỳ lỗi nào trong bài viết hoặc mã, vui lòng cho tôi biết.

Ví dụ về Macro Excel

Bài viết này mô tả các ví dụ macro sau:

Hiện tất cả các trang tính cùng một lúc

Nếu bạn đang làm việc trên một sổ làm việc có nhiều trang tính bị ẩn, bạn cần phải hiện từng trang một.Việc này có thể mất một chút thời gian nếu có nhiều trang tính bị ẩn.

Đây là mã để hiện tất cả các trang tính trong sổ làm việc.

'Mã này sẽ hiện tất cả các trang tính trong sổ làm việc Sub UnhideAllWoksheets () Dim ws As Worksheet For Each ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVible Next ws End Sub

Đoạn mã trên sử dụng một vòng lặp VBA (Cho Mỗi) để lặp qua từng trang tính trong sổ làm việc.Sau đó, nó thay đổi thuộc tính hiển thị của trang tính thành hiển thị.

Dưới đây là hướng dẫn chi tiết về cách hiện trang tính trong Excel bằng các phương pháp khác nhau.

Ẩn tất cả các trang tính ngoại trừ trang tính đang hoạt động

Bạn có thể sử dụng mã macro này nếu bạn đang làm việc trên một báo cáo hoặc trang tổng quan và muốn ẩn tất cả các trang tính ngoại trừ trang tính có báo cáo / trang tổng quan.

'Macro này sẽ ẩn tất cả trang tính ngoại trừ trang hiện hoạt Sub HideAllExceptActiveSheet () Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Vible = xlSheetHidden Next ws End Sub

Sắp xếp trang tính theo thứ tự bảng chữ cái bằng VBA

Mã macro này có thể hữu ích nếu bạn có một sổ làm việc có nhiều trang tính và bạn muốn sắp xếp các trang tính đó theo thứ tự bảng chữ cái.Điều này có thể xảy ra nếu bạn có tên trang tính là năm hoặc tên nhân viên hoặc tên sản phẩm.

'Đoạn mã này sẽ sắp xếp các trang tính theo thứ tự bảng chữ cái 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). Di chuyển trước: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Bảo vệ tất cả các trang tính cùng một lúc

Nếu bạn có nhiều trang tính trong sổ làm việc của mình và bạn muốn bảo vệ tất cả các trang tính, bạn có thể sử dụng mã macro này.

Nó cho phép bạn chỉ định mật khẩu trong mã.Bạn sẽ cần mật khẩu này để bỏ bảo vệ trang tính.

'Mã này sẽ bảo vệ tất cả các trang cùng một lúc Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' thay thế Test123 bằng mật khẩu bạn muốn Đối với mỗi trang Trong Worksheets ws.Protect password: = password Next ws Kết thúc Sub

Bỏ bảo vệ tất cả các trang tính cùng một lúc

Nếu bạn đã bảo vệ một số hoặc tất cả các trang tính của mình, bạn có thể bỏ bảo vệ chúng chỉ với một sửa đổi nhỏ đối với mã được sử dụng để bảo vệ trang tính.

'Mã này sẽ bảo vệ tất cả các trang cùng một lúc Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' thay thế Test123 bằng mật khẩu bạn muốn Đối với mỗi trang Trong Worksheets ws.Unprotect password: = password Next ws Kết thúc Sub

Lưu ý rằng mật khẩu cần phải giống với mật khẩu được sử dụng để khóa trang tính.Nếu không, bạn sẽ thấy một lỗi.

Hiện tất cả các hàng và cột

Mã macro này sẽ hiện tất cả các hàng và cột bị ẩn.

Điều này có thể hữu ích nếu bạn đang lấy tệp từ người khác và muốn đảm bảo không có hàng / cột bị ẩn.

'Mã này sẽ hiện tất cả các hàng và cột trong Worksheet Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Hủy hợp nhất tất cả các ô đã hợp nhất

Hợp nhất các ô thành một là một thực tế phổ biến.Trong khi nó hoạt động, bạn sẽ không thể sắp xếp dữ liệu khi các ô được hợp nhất.

Nếu bạn đang sử dụng trang tính có các ô đã hợp nhất, hãy sử dụng mã bên dưới để hủy hợp nhất tất cả các ô đã hợp nhất cùng một lúc.

'Mã này sẽ hủy hợp nhất tất cả các ô đã hợp nhất Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub

Lưu ý rằng tôi khuyên bạn nên sử dụng tùy chọn "Lựa chọn chéo giữa" thay vì tùy chọn "Hợp nhất và chính giữa".

Lưu sổ làm việc với dấu thời gian trong tên

Có nhiều lúc bạn có thể cần tạo một phiên bản làm việc.Đây là những điều tuyệt vời cho các dự án dài hạn xử lý các tệp theo thời gian.

Một phương pháp hay là lưu tệp với dấu thời gian.

Sử dụng dấu thời gian sẽ cho phép bạn quay lại tệp để xem những thay đổi nào đã được thực hiện hoặc dữ liệu nào đã được sử dụng.

Đây là mã tự động lưu sổ làm việc trong thư mục được chỉ định và thêm dấu thời gian khi lưu.

'Mã này sẽ Lưu tệp với Dấu thời gian trong tên của nó Sub SaveWorkbookWithTimeStamp () Dim dấu thời gian As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & dấu thời gian End Sub

Bạn cần chỉ định vị trí thư mục và tên tệp.

Trong đoạn mã trên, "C: UsersUsernameDesktop là vị trí thư mục tôi sử dụng.Bạn cần chỉ định vị trí thư mục mà bạn muốn lưu tệp.Ngoài ra, tôi đã sử dụng tên chung "WorkbookName" làm tiền tố tên tệp.Bạn có thể chỉ định nội dung liên quan đến dự án hoặc công ty của bạn.

Lưu từng trang tính dưới dạng PDF riêng biệt

Nếu bạn sử dụng dữ liệu từ các năm hoặc các phòng ban hoặc sản phẩm khác nhau, bạn có thể cần lưu các trang tính khác nhau dưới dạng tệp PDF.

Mặc dù đây có thể là một quá trình tốn thời gian nếu được thực hiện thủ công, nhưng VBA thực hiện đẩy nhanh tiến độ.

Đây là mã VBA lưu từng trang tính dưới dạng PDF riêng biệt.

'Đoạn mã này sẽ lưu từng biểu tượng dưới dạng PDF riêng biệt.

Trong đoạn mã trên, tôi đã chỉ định địa chỉ của vị trí thư mục mà tôi muốn lưu tệp PDF.Ngoài ra, mỗi tệp PDF sẽ có cùng tên với trang tính.Bạn sẽ phải sửa đổi vị trí thư mục này (trừ khi tên của bạn cũng là Sumit và bạn đã lưu nó trong một thư mục thử nghiệm trên màn hình của mình).

Lưu ý rằng mã này chỉ hoạt động cho trang tính (không cho trang tính biểu đồ).

Lưu từng trang tính dưới dạng PDF riêng biệt

Đây là mã để lưu toàn bộ sổ làm việc dưới dạng PDF trong thư mục được chỉ định.

'Mã này sẽ lưu toàn bộ sổ làm việc dưới dạng PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Bạn phải thay đổi vị trí thư mục để sử dụng mã này.

Chuyển đổi tất cả các công thức thành giá trị

Sử dụng mã này khi bạn có một trang tính có nhiều công thức và bạn muốn chuyển đổi các công thức đó thành giá trị.

'Đoạn mã này sẽ chuyển đổi tất cả các công thức thành giá trị Sub ConvertToValues ​​() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Mã này tự động nhận dạng ô được sử dụng và chuyển nó thành một giá trị.

Bảo vệ / khóa các ô bằng công thức

Khi bạn có nhiều phép tính và không muốn vô tình xóa hoặc thay đổi nó, bạn có thể muốn khóa các ô bằng công thức.

Đây là mã sẽ khóa tất cả các ô có công thức và để mở khóa tất cả các ô khác.

'Mã macro này sẽ khóa tất cả các ô có công thức Sub LockCellsWithFormulas () With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub

Hướng dẫn liên quan: Cách khóa ô trong Excel.

Bảo vệ tất cả các trang tính trong sổ làm việc

Sử dụng mã sau để bảo vệ tất cả các trang tính trong sổ làm việc cùng một lúc.

'Mã này sẽ bảo vệ tất cả các trang tính trong sổ làm việc Sub ProtectAllSheets () Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

Mã này sẽ lần lượt đi qua tất cả các trang tính và bảo vệ chúng.

Nếu bạn muốn bỏ bảo vệ tất cả các trang tính, hãy sử dụng ws.Unprotect thay vì ws.Protect trong mã của bạn.

Chèn một hàng sau mỗi hàng lựa chọn

Sử dụng mã này khi bạn muốn chèn một hàng trống sau mỗi hàng trong phạm vi đã chọn.

'Đoạn mã này sẽ chèn một hàng sau mỗi hàng trong vùng chọn 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. Chèn ActiveCell.Offset (2, 0). Chọn Tiếp theo i Kết thúc Sub

Một lần nữa, bạn có thể sửa đổi mã này để chèn một cột trống sau mỗi cột trong phạm vi đã chọn.

Tự động chèn ngày tháng và dấu thời gian vào các ô liền kề

Dấu thời gian là những gì bạn sử dụng khi muốn theo dõi hoạt động.

Ví dụ: bạn có thể muốn theo dõi các hoạt động như thời điểm phát sinh một khoản chi phí cụ thể, thời điểm tạo hóa đơn bán hàng, khi dữ liệu được nhập vào ô, thời điểm báo cáo được cập nhật lần cuối, v.v.

Sử dụng mã này để chèn dấu ngày và giờ vào các ô liền kề khi nhập hoặc chỉnh sửa nội dung hiện có.

'Mã này sẽ chèn một dấu thời gian trong ô liền kề Private Sub Worksheet_Change (ByVal Target As Range) Trên Lỗi GoTo Xử lý 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

Lưu ý rằng bạn cần chèn mã này vào cửa sổ mã trang tính (không phải trong cửa sổ mã mô-đun như chúng tôi đã thực hiện trong các ví dụ macro Excel khác cho đến nay).Để thực hiện việc này, trong trình soạn thảo VB, bấm đúp vào tên trang tính mà bạn muốn có chức năng này.Sau đó sao chép và dán mã này vào cửa sổ mã của trang tính đó.

Ngoài ra, mã này hoạt động khi nhập dữ liệu được thực hiện trong cột A (lưu ý rằng mã có Target.Column = 1 dòng).Bạn có thể thay đổi nó cho phù hợp.

Đánh dấu các hàng thay thế trong lựa chọn

Đánh dấu các dòng xen kẽ có thể cải thiện đáng kể khả năng đọc dữ liệu của bạn.Điều này có thể hữu ích khi bạn cần in ra và duyệt dữ liệu.

Đây là một đoạn mã sẽ ngay lập tức đánh dấu các hàng thay thế trong vùng lựa chọn.

'Đoạn mã này sẽ đánh dấu các hàng thay thế trong vùng lựa chọn 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

Lưu ý rằng tôi đã chỉ định màu là vbCyan trong mã.Bạn cũng có thể chỉ định các màu khác (ví dụ: vbRed, vbGreen, vbBlue).

Đánh dấu các ô sai chính tả

Excel không có tính năng kiểm tra chính tả trong Word hoặc PowerPoint.Mặc dù bạn có thể chạy kiểm tra chính tả bằng cách nhấn F7, nhưng không có dấu hiệu trực quan cho lỗi chính tả.

Sử dụng mã này để đánh dấu ngay lập tức tất cả các ô sai chính tả.

'Đoạn mã này sẽ đánh dấu các ô có từ sai chính tả Sub HighlightMisspellingCells () Dim cl As Range For Each cl Trong ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub

Lưu ý rằng các ô được đánh dấu là các ô có văn bản mà Excel cho là sai chính tả.Trong nhiều trường hợp, nó cũng đánh dấu tên hoặc thuật ngữ thương hiệu mà nó không hiểu.

Làm mới tất cả các bảng tổng hợp trong sổ làm việc

Nếu bạn có nhiều bảng tổng hợp trong sổ làm việc, bạn có thể sử dụng mã này để làm mới tất cả chúng cùng một lúc.

'Mã này sẽ làm mới tất cả Pivot Table trong Workbook Sub RefreshAllPivotTables () Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Bạn có thể đọc thêm về làm mới bảng tổng hợp tại đây.

Thay đổi chữ hoa của các ô đã chọn thành chữ hoa

Mặc dù Excel có các công thức để thay đổi trường hợp của các chữ cái văn bản, nhưng nó cho phép bạn thực hiện việc này trong một tập hợp ô khác.

Sử dụng mã này để thay đổi ngay cách viết hoa chữ cái của văn bản trong văn bản đã chọn.

'Đoạn mã này sẽ thay đổi Lựa chọn thành Chữ hoa Phụ Thay đổiCase () Dim Rng Như Phạm vi Đối với Mỗi Rng Trong Lựa chọn.Cells If Rng.HasFormula = False Then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub

Lưu ý rằng trong trường hợp này, tôi sử dụng UCase để đặt chữ hoa chữ thường.Bạn có thể sử dụng LCase cho chữ thường.

Đánh dấu tất cả các ô bằng nhận xét

Sử dụng mã sau để đánh dấu tất cả các ô có nhận xét trong đó.

'Mã này sẽ đánh dấu các ô có nhận xét` Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub

Trong trường hợp này, tôi sử dụng vbBlue để cung cấp cho ô màu xanh lam.Bạn có thể thay đổi nó sang màu khác nếu cần.

Đánh dấu các ô trống bằng VBA

Mặc dù bạn có thể sử dụng định dạng có điều kiện hoặc sử dụng hộp thoại Đi đến Đặc biệt để đánh dấu các ô trống, nhưng nếu bạn phải làm điều này thường xuyên, tốt nhất nên sử dụng macro.

Sau khi được tạo, bạn có thể có macro này trong Thanh công cụ Truy nhập Nhanh hoặc lưu nó trong sổ làm việc macro cá nhân của bạn.

Đây là mã macro VBA:

'Đoạn mã này sẽ đánh dấu tất cả các ô trống trong tập dữ liệu Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub

Trong mã này, tôi đã chỉ định các ô trống được đánh dấu màu đỏ.Bạn có thể chọn các màu khác như xanh lam, vàng, lục lam, v.v.

Cách sắp xếp dữ liệu theo một cột

Bạn có thể sử dụng mã sau để sắp xếp dữ liệu theo một cột được chỉ định.

Sub SortDataHeader () 
Range ("DataRange"). Sắp xếp Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes 
End Sub

Lưu ý rằng tôi đã tạo một phạm vi được đặt tên là "DataRange" và sử dụng nó thay cho tham chiếu ô.

Ngoài ra còn có ba tham số chính được sử dụng ở đây:

• Key1 - Đây là khóa mà bạn muốn sắp xếp tập dữ liệu.Trong đoạn mã mẫu ở trên, dữ liệu sẽ được sắp xếp dựa trên các giá trị trong cột A.
• Order1 - Ở đây bạn cần xác định xem bạn muốn sắp xếp dữ liệu theo thứ tự tăng dần hay giảm dần.
• Header - Ở đây bạn cần xác định xem dữ liệu của bạn có tiêu đề hay không.

Đọc thêm về cách sắp xếp dữ liệu trong Excel bằng VBA.

Cách sắp xếp dữ liệu theo nhiều cột

Giả sử bạn có một tập dữ liệu giống như sau:

Đây là mã để sắp xếp dữ liệu dựa trên nhiều cột:

Sub SortMultipleColumns () Với 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

Lưu ý rằng ở đây tôi đã chỉ định sắp xếp trên cột A trước rồi đến cột B.

Đầu ra sẽ như thế này:

Cách chỉ lấy phần số từ một chuỗi trong Excel

Nếu bạn chỉ muốn trích xuất phần số hoặc phần văn bản từ chuỗi, bạn có thể tạo một hàm tùy chỉnh trong VBA.

Sau đó, bạn có thể sử dụng hàm VBA này trong trang tính của mình (giống như một hàm Excel thông thường) và nó sẽ chỉ trích xuất số hoặc phần văn bản từ chuỗi.

Như hình bên dưới:

Đây là mã VBA sẽ tạo một hàm trích xuất phần số từ một chuỗi:

'Mã VBA này sẽ tạo ra một hàm để lấy phần số từ một chuỗi Hàm 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

Bạn cần đặt mã trong một mô-đun và sau đó bạn có thể sử dụng hàm = GetNumeric trong trang tính.

Hàm này sẽ chỉ nhận một tham số, tham chiếu ô của ô mà từ đó lấy phần số.

Tương tự như vậy, hàm bên dưới sẽ chỉ lấy phần văn bản từ một chuỗi trong Excel:

'Mã VBA này sẽ tạo ra một hàm để lấy phần văn bản từ một chuỗi Hàm 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

Vì vậy, đây là một số mã macro Excel hữu ích mà bạn có thể sử dụng trong công việc hàng ngày của mình để tự động hóa các tác vụ và tăng năng suất của bạn.