Những mã VBA cơ bản và thường dùng nhất trong Excel

XEM NHANH BÀI VIẾT

1 Tạo mã VBA theo các thao tác Excel phổ biến
2 Các mã VBA để làm việc với worksheet
3 Các mã VBA để làm việc với các workbook
4 Tệp và thư mục
5 Các tính năng hữu ích khác của Excel

Tạo mã VBA theo các thao tác Excel phổ biến

Các mã VBA này sẽ giúp bạn thực hiện các tác vụ Excel phổ biến một cách nhanh chóng.

Thay đổi chiều rộng cột AutoFit

Mã này sẽ tự động điều chỉnh điều rộng của cột cho tất cả các cột của worksheet.

Sub AutofitAllColumns()
Cells.EntireColumn.AutoFit
End Sub

Trong khi đoạn mã dưới đây sẽ tự động điều chỉnh chiều rộng của các cột cụ thể phù hợp với dữ liệu trên worksheet. Trong ví dụ này, đó là cột D và F, nhưng nếu bạn sử dụng thì có thể chỉ định bất kỳ cột nào trong worksheet mà mình muốn.

Sub AutofitSpecificColumns()
Range("D:D,F:F").EntireColumn.AutoFit
End Sub

Copy và paste

Sao chép và dán là một trong những tác vụ phổ biến nhất trong Excel. Nó có thể được viết chỉ bằng một dòng mã VBA cực kỳ đơn giản.

Như đoạn mã dưới đây sẽ sao chép dữ liệu trong phạm vi A1:B6 vào ô A1 của một worksheet khác.

Sub CopyAndPaste()
Range("A1:B6").Copy Worksheets("Sheet2").Range("A1")
End Sub

Bạn có thể không có một phạm vi cụ thể để dán nội dung vào và thường thì bạn sẽ nối dữ liệu đã sao chép vào cuối danh sách khác.

Mã này sẽ sao chép phạm vi đã sử dụng xung quanh ô A2 và dán nó vào ô trống đầu tiên ở cuối cột A của sheet có tên Archive như trong ví dụ của mã dưới đây.

Sub CopyAndPaste()
Range("A2").CurrentRegion.Copy Worksheets("Archive").Range("A1").End(xlDown).Offset(1, 0)
End Sub

Cuối cùng, bạn có thể muốn sử dụng một số tùy chọn dán đặc biệt (Paste Special) có sẵn trong Excel. Để truy cập chúng trong VBA, Gitiho sẽ tách hoạt động sao chép và dán thành hai câu lệnh.

Mã này sử dụng phương thức PasteSpecial để chỉ dán các giá trị.

Sub CopyAndPasteValues()
Range("A1:B6").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

Xóa tất cả các siêu liên kết trên một worksheet

Đoạn mã macro sau sẽ xóa tất cả các siêu liên kết trên một worksheet.

Sub ClearHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

Xóa công thức giữ nguyên giá trị Excel

Đoạn mã dưới đây sẽ loại bỏ các công thức trong Excel nhưng vẫn giữ nguyên các giá trị (kết quả tính toán) của các ô:

Sub XoaCongThuc_GiuKetQua()
'Copy cong thuc tu dong 5 den dong 26
Sheet1.Range("E5:H26").FillDown
'Xoa cong thuc, giu lai ket qua tu dong 6 den dong 26
Sheet1.Range("E6:H26").Value = Sheet1.Range("E6:H26").Value
'Dong 5 van giu nguyen cong thuc
End Sub

Định dạng ô bằng công thức

Định dạng ô có chứa công thức giúp chúng dễ dàng xác định trên worksheet. Mã macro này sẽ định dạng các ô có công thức bằng màu vàng (ColorIndex = 6 ).

Nó khai báo một biến phạm vi có tên rng và sử dụng nó để lặp qua mỗi ô có chứa công thức.

Sub FormatFormulas()
Dim rng As Range
For Each rng In Cells.SpecialCells(xlCellTypeFormulas)
rng.Interior.ColorIndex = 6
Next rng
End Sub

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

Một tác vụ vô cùng phổ biến khác với công thức là chuyển đổi chúng thành giá trị. Mã VBA này sẽ thực hiện việc này cho tất cả các công thức trong một worksheet.

Sub ConvertFormulastoValues()
Dim rng As Range
For Each rng In Cells.SpecialCells(xlCellTypeFormulas)
rng.Formula = rng.Value
Next rng
End Sub

Các mã VBA để làm việc với worksheet

Các mã sau đây sẽ thực hiện một số tác vụ điển hình trên worksheet.

Hiện tất cả các cột

Ẩn các cột không còn cần thiết trong các worksheet sẽ giúp giảm sự lộn xộn của worksheet và bảo vệ dữ liệu. Nhưng nếu cần hiển thị tất cả các cột thì mã macro dưới đây sẽ giúp bạn chỉ bằng một nút bấm.

Sub UnhideAllColumns()
Columns.EntireColumn.Hidden = False
End Sub

Bảo vệ worksheet

Đoạn mã sau sẽ bảo vệ các worksheet đang hoạt động để ngăn những con mắt tò mò nhòm ngó bảng dữ liệu quan trọng của bạn.

Sub ProtectWS()
ActiveSheet.Protect
End Sub

Bạn có thể muốn gán mật khẩu hoặc chỉ định các hành động được phép đối với các worksheet. Mã macro dưới đây sẽ chỉ định mật khẩu bảo vệ tệp Excel và chỉ cho phép chèn các hàng.

Nếu bạn biết rõ về bảo vệ bảng tính Excel thì sẽ biết rằng có nhiều hành động khác nhau có thể cho phép hoặc ngăn chặn. Tất cả đều có thể thực hiện dễ dàng thông qua các mã VBA.

Sub ProtectWS()
ActiveSheet.Protect Password:=”Excel”, AllowInsertingRows:=True
End Sub

Một lý do phổ biến để bảo vệ worksheet là để lưu các công thức của bạn không bị hỏng một cách ngẫu nhiên. Mã VBA dưới đây sẽ giúp bạn bảo vệ tất cả các ô trên worksheet có chứa các công thức.

Mã VBA dưới đây bắt đầu bằng cách không bảo vệ worksheet và mở khóa tất cả các ô. Sau đó, nó khóa những ô có chứa công thức trước khi áp dụng biện pháp bảo vệ.

Sub ProtectFormulas()
With ActiveSheet
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect
End With
End Sub

Lặp qua tất cả các worksheets của workbook

Rất đơn giản khi cần thiết lập một vòng lặp để thực hiện một hành động đối với tất cả các worksheet của một của workbook. Bạn có thể chèn bất kỳ hành động nào mà mình yêu cầu.

Trong ví dụ này, Gitiho sẽ thực hiện bảo vệ các worksheet. Bằng cách thay thế dòng ws.Protect bằng các hành động mà bạn yêu cầu và sử dụng biến ws khi tham chiếu bảng tính Excel.

Sub LoopAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect
Next ws
End Sub

Các mã VBA để làm việc với các workbook

Các mã sau sẽ thực hiện một số tác vụ phổ biến khi làm việc với các workbook.

Hiện tất cả các worksheet

Trong Excel , bạn chỉ có thể hiện một worksheet tại một thời điểm và điều này sẽ rất mất thời gian nếu workbook đó có rất nhiều worksheet cần hiển thị. Vì vậy, mã VBA dưới đây sẽ giúp bạn hiển thị tất cả các worksheet chỉ với một cú nhấp chuột.

Sub UnhideAllWorksheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Bảo vệ workbook

Đoạn mã sau sẽ bảo vệ workbook có các dữ liệu quan trọng để ngăn việc thay đổi cấu trúc bằng cách đặt mật khẩu, nhưng điều này là tùy chọn và có thể bị loại trừ.

Sub ProtectWorkbook()
ThisWorkbook.Protect Password:="Excel"
End Sub

Mở và đóng workbook

Đoạn mã sau đây tuân theo quy trình sao chép dữ liệu từ workbook hiện đang hoạt động sang một workbook mới có tên là North .

Nó chỉ định workbook đang hoạt động là một biến, sau đó mở workbook có tên North để sao chép dữ liệu từ phạm vi ô A1:C250 sang. Sau đó workbook này được lưu và đóng lại.

Sub OpenCloseWorkbooks()
Dim wbk As Workbook
Set wbk = ActiveWorkbook
Workbooks.Open "C:\Users\Admin\OneDrive\Desktop\Sales\North.xlsx"
wbk.Sheets("Sheet1").Range("A1:C250").Copy Destination:=Range("A1")
ActiveWorkbook.Close SaveChanges:=True
End Sub

Gửi email với workbook đang hoạt động đính kèm vào Outlook

Mã VBA sau tạo một email mới trong Microsoft Outlook với địa chỉ email của người nhận, dòng chủ đề và nội dung đều sẽ được điền sẵn. Workbook đang làm việc sẽ được thêm vào email dưới dạng tệp đính kèm.

Email được hiển thị để có thể kiểm tra nội dung và thực hiện các thay đổi trước khi chính thức gửi đi. Bạn có thể thay đổi .display thành .send để gửi email với workbook đính kèm chỉ bằng một cú kích chuột.

Sub AttachToEmail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = "admin@computergaga.com"
.Subject = "A Fabulous Spreadsheet"
.Body = "Hello, I hope you enjoy the fabulous spreadsheet that is attached to this email."
.Attachments.Add ActiveWorkbook.FullName
.display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Tệp và thư mục

Sử dụng mã VBA để tự động làm việc với các tệp tin và thư mục trong một quá trình là rất hữu ích.

Xuất từng worksheet thành tệp PDF riêng lẻ

Mã VBA dưới đây sẽ xuất tất cả các worksheet thành các tệp PDF riêng biệt. Tên của mỗi worksheet sẽ được sử dụng làm tên tệp PDF tương ứng và chúng sẽ được lưu vào thư mục được gán cho tên là FolderPath.

Khi bạn sử dụng, chỉ cần thay đổi đường dẫn FolderPath này thành đường dẫn đến thư mục mà bạn muốn lưu các tệp tin đó trên máy tính của mình là xong.

Sub ExportAsPDF()
Dim FolderPath As String
Dim ws As Worksheet
FolderPath = "C:\Users\Computergaga\Desktop\Sales"
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" &
ws.Name, openafterpublish:=False
Next
MsgBox "All PDF's have been successfully exported."
End Sub

Xuất worksheet đang làm việc sang tệp PDF

Nếu bạn chỉ cần xuất worksheet đang làm việc sang tệp PDF thì chỉ cần vài dòng mã đơn giản dưới đây. Lưu ý thay FolderPath bằng đường dẫn đến thư mục mà bạn muốn lưu trên máy tính của mình.

Sub ExportAsPDF()
FolderPath = "C:\Users\Computergaga\Desktop\Sales"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" &
Activesheet.Name, openafterpublish:=False
End Sub

Xuất nhiều worksheet vào một tệp PDF duy nhất

Trong nhiều trường hợp, bạn có thể muốn xuất các worksheet thành một tệp PDF duy nhất. Mã VBA dưới đây sẽ xuất các các worksheet có tên London và Berlin thành một tệp PDF duy nhất.

Các các worksheet được tham chiếu theo tên của chúng trong ví dụ này, nhưng bạn cũng có thể tham chiếu chúng bằng cách sử dụng số chỉ mục. Ví dụ: Sheets(Array(3, 6)).Select

Sub ExportAsPDF()
Dim FolderPath As String
FolderPath = "C:\Users\Trainee1\Desktop\PDFs"
Sheets(Array("London", "Berlin")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", openafterpublish:=False, ignoreprintareas:=False
MsgBox "All PDF's have been successfully exported."
End Sub

Các tính năng hữu ích khác của Excel

Các mã VBA dưới đây sẽ đơn giản hóa việc sử dụng một số tính năng hữu ích nhất của Excel.

Sắp xếp các cột

Đoạn mã sau đây sắp xếp phạm vi ô từ A1:K250 theo một cột. Nó giả định rằng phạm vi có tiêu đề ở hàng đầu tiên và đối số Key1 chỉ định cột để sắp xếp theo.

Trong ví dụ này, đối số được đặt thành C1, do đó phạm vi được sắp xếp theo cột C theo thứ tự giảm dần.

Sub SortSingleColumn()
Range("A1:K250").Sort Key1:=Range("C1"), Order1:=xlDescending
End Sub

Trong mã macro sau, tên đã xác định Sales được sử dụng làm phạm vi để sắp xếp. Tên đã xác định không bao gồm các tiêu đề. Lần này phạm vi được sắp xếp theo cột B theo thứ tự tăng dần.

Sub SortSingleColumn()
Range("Sales").Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub

Lọc dữ liệu của bạn

Mã macro sau có thể được sử dụng để bật tính năng AutoFilter. Nó áp dụng cho worksheet đang hoạt động và sử dụng vùng hiện tại cho ô A1.

Sub TurnFilterOn()
Range("A1").AutoFilter
End Sub

Để tắt tính năng AutoFilter, bạn có thể sử dụng mã dưới đây.

Sub TurnFilterOff()
ActiveSheet.AutoFilterMode = False
End Sub

Trong ví dụ này, một bộ lọc được áp dụng cho cột D (Field:=4) để chỉ hiển thị các bản ghi cho Denmark.

Sub FilterByText()
Range("A1").AutoFilter Field:=4, Criteria1:="Denmark"
End Sub

Để lọc theo nhiều giá trị văn bản, có thể sử dụng toán tử xlOr . Tại đây, dữ liệu được lọc để hiển thị các hàng cho Denmark và UK.

Sub FilterByText()
Range("A1").AutoFilter Field:=4, Criteria1:="Denmark", Operator:=xlOr, Criteria2:="UK"
End Sub

Ngoài ra còn có rất nhiều toán tử có sẵn để sử dụng như xlFilterIcon và xlTop10Items .

Để lọc các số, hãy đảm bảo bạn nhập các toán tử logic vào chuỗi tiêu chí. Đoạn mã bên dưới lọc cột H để chỉ hiển thị các hàng có số lớn hơn 5 và nhỏ hơn 20.

Sub FilterByNumber()
Range("A1").AutoFilter Field:=8, Criteria1:=">5", Operator:=xlAnd, Criteria2:="<20"
End Sub

Để xóa mọi bộ lọc được áp dụng và hiển thị tất cả dữ liệu, hãy sử dụng mã sau. Mã này kiểm tra xem có bộ lọc nào được áp dụng trước không và nếu có, hãy xóa chúng để hiển thị tất cả dữ liệu.

Sub ClearFilters()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub

Tạo biểu đồ

Bạn có thể sử dụng mã Excel VBA để tạo biểu đồ nhanh chóng chỉ với một nút bấm.

Mã VBA dưới đây sẽ tạo biểu đồ cột (loại biểu đồ mặc định) từ phạm vi C3:D8. Nó sử dụng biến là đối tượng biểu đồ, vì vậy các tham số sẽ được đặt cho kích thước và vị trí của vùng biểu đồ. Dữ liệu sau đó được thiết lập cho biểu đồ.

Sub CreateChart()
Dim MChart As ChartObject
Set MyChart = ActiveSheet.ChartObjects.Add(Top:=50, Left:=100, Width:=450, Height:=250)
MyChart.Chart.SetSourceData Range("C3:D8")
End Sub

Bạn cũng có thể sử dụng một loại biểu đồ khác, ví dụ mã dưới đây sẽ thay đổi biểu đồ thành biểu đồ đường và danh sách tất cả các loại biểu đồ sẽ xuất hiện khi bạn nhập.

Sub ChangeChartType()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(1).Chart
MyChart.Chart.ChartType = xlLine
End Sub

Cuối cùng, bạn có thể muốn thêm hoặc xóa các phần tử biểu đồ. Mã này thêm tiêu đề biểu đồ và cả nhãn dữ liệu.

Khi sử dụng phương thức SetElement , một danh sách các phần tử biểu đồ sẽ xuất hiện. Điều quan trọng là bạn phải chọn một cái phù hợp với loại biểu đồ của mình. Các msoElementDataLabelOutSideEnd được sử dụng trong ví dụ này làm việc với biểu đồ cột, nhưng không phải xếp hàng bảng xếp hạng vì thế nó sẽ tạo ra một lỗi.

Sub EditChartElements()
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(1).Chart
MyChart.HasTitle = True
MyChart.ChartTitle.Text = "Product Sales"
MyChart.SetElement msoElementDataLabelOutSideEnd
End Sub

Như vậy các đoạn mã VBA ở trên đây là những đoạn mã đơn giản dành cho những người dùng mới làm quen với VBA. Để phù hợp với từng bảng tính Excel cụ thể, bạn chỉ cần thực hiện một vài chỉnh sửa nhỏ.

Các mã VBA trên đây là một giải pháp quan trọng giúp người dùng tiết kiệm thời gian nhưng đó mới chỉ là những mã cơ bản và là bước khởi đầu. Bạn nên học Excel VBA bài bản để thực sự có thể tận dụng được những tiềm năng của nó trong việc nâng cao hiệu quả công việc khi làm việc với các bảng tính Excel.

Hãy đăng ký học VBA tại Gitiho ngay để bắt đầu hành trình của bạn: