Thư viện mã VBA trong Excel hữu ích cho người mới bắt đầu (phần 2)

Các mã VBA sau sẽ thực hiện một số tác vụ trên bảng tính

Hiện tất cả các trang tính

Trong Excel, bạn chỉ có thể hiện một trang tính tại một thời điểm. Vì vậy, mã này sẽ hiện tất cả các trang tính bằng 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ệ file làm việc

Đoạn mã VBA sau sẽ bảo vệ cửa sổ làm việc để ngăn các thay đổi ngoài ý muốn bằng cách đặt mật khẩu cho nó

Sub ProtectWorkbook()

ThisWorkbook.Protect Password:="Excel"

End Sub

Mở và đóng file làm việc Workbook

Đoạn mã VBA sau đây tuân theo một quy trình để sao chép dữ liệu từ file làm việc hiện đang hoạt động sang một file làm việc có tên là Gitiho. Nó chỉ định file làm việc đang hoạt động cho một biến, sau đó mở file Gitiho để sao chép dữ liệu từ phạm vi A1:C250 vào đó. Cuối cùng lưu và đóng file làm việc này.

Có nhiều kỹ thuật để tham chiếu file làm việc và một số kỹ thuật được trình bày trong đoạn mã nhỏ này.

Sub OpenCloseWorkbooks()

Dim wbk As Workbook
Set wbk = ActiveWorkbook

Workbooks.Open "C:\Users\Admin\OneDrive\Desktop\Sales\Gitiho.xlsx"
wbk.Sheets("Sheet1").Range("A1:C250").Copy Destination:=Range("A1")

ActiveWorkbook.Close SaveChanges:=True

End Sub

Gửi email trong workbook đang hoạt động với Outlook

Mã VBA sau tạo một email mới trong Microsoft Outlook với email của người nhận, dòng chủ đề và nội dung đều được điền và workbook hoạt động được thêm vào dưới dạng tệp đính kèm.

Email được hiển thị để có thể được kiểm tra và thực hiện các thay đổi trước khi gửi. Bạn có thể thay đổi lệnh .display thành .send để gửi email với bảng tính đính kèm bằng một cú nhấp 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

Mã VBA thực hiện trên tệp và thư mục Excel

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

Xuất từng trang tính dưới dạng một tệp PDF duy nhất

Mã này xuất tất cả các trang tính dưới dạng PDF riêng biệt. Tên trang tính được sử dụng làm tên tệp PDF và chúng được lưu vào thư mục được gán cho biến FolderPath. Đơn giản chỉ cần thay đổi đường dẫn này thành tên bạn muốn.

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 trang tính hiện hoạt dưới dạng PDF

Nếu bạn chỉ cần xuất trang tính đang hoạt động, thì chỉ cần vài dòng mã đơn giản sau:

Sub ExportAsPDF()

FolderPath = "C:\Users\Computergaga\Desktop\Sales"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" &
Activesheet.Name, openafterpublish:=False

End Sub

Xuất nhiều trang tính sang một tệp PDF duy nhất

Nếu bạn muốn xuất các trang tính thành một tệp PDF duy nhất đây là cách nhanh chóng và đơn giản nhất cho bạn. Mã VBA này sẽ xuất các bảng tính có tên 'Gitiho' và 'Vietnam' thành một tệp PDF.

Sub ExportAsPDF()

Dim FolderPath As String

FolderPath = "C:\Users\Trainee1\Desktop\PDFs"

Sheets(Array("Gitiho", "Vietnam")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\Sales", openafterpublish:=False, ignoreprintareas:=False   

MsgBox "All PDF's have been successfully exported."

End Sub

Chọn tệp với FileDialog

Khi tương tác với các tệp, FileDialog trong VBA Excel rất hữu ích. Chúng là một thư viện mà người dùng có thể thành thạo ngay lập tức.

FileDialog có chức năng như mở tệp, lưu tệp, chọn tệp và chọn thư mục. Trong ví dụ này, dialog - hộp thoại đang mở được sử dụng, điều này được gọi là msoFileDialogOpen . Bây giờ, sẽ có một số hiểu lầm, vì hộp thoại sẽ không thực sự mở tệp. Nó chỉ cung cấp nơi để định vị và chọn tệp một cách dễ dàng hay bạn có thể thực hiện bất kỳ hành động nào bạn muốn.

Trong mã VBA này, các thuộc tính khác nhau của FileDialog được sử dụng để nó thao tác với cấu trúc With .

Workbook đã chọn sẽ ược mở và chúng ta thực hiện ghi số 20 vào ô A1. (Đây là thao tác mẫu và bạn có thể thay thế bằng bất kỳ hành động nào bạn muốn).

Sub UsingFileDialog()

Dim Filename As String

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Title = "Select a workbook to use"
.Show
Filename = .SelectedItems(1)
End With

Workbooks.Open Filename
Worksheets("Sheet1").Range("A1").Value = 20
ActiveWorkbook.Close SaveChanges:=True

MsgBox "Workbook updated"

End Sub

Xem thêm: Hướng dẫn sử dụng VBA để sao chép dữ liệu từ Excel sang Word

Mã VBA cho các tính năng hữu ích trong Excel

Các mã VBA nà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 dải ô A1: K250 theo một cột và giả định rằng phạm vi có tiêu đề ở hàng đầu tiên.

Đối số Key1 chỉ định cột để sắp xếp theo. Trong ví dụ này, đối số được đặt thành C1, vì vậy phạm vi được sắp xếp theo cột C và 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 là Sales được sử dụng làm phạm vi để sắp xếp. Tên được 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 và theo thứ tự tăng dần.

Sub SortSingleColumn()

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

End Sub

Đối số Key và Order có thể được thêm vào để sắp xếp theo nhiều cột.

Trong ví dụ này, tên đã xác định Sales được sử dụng làm phạm vi. Phạm vi được sắp xếp trước tiên theo cột D theo thứ tự tăng dần, sau đó theo cột J theo thứ tự giảm dần.

Sub SortMultipleColumns()

Range("Sales").Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("J1"), Order2:=xlDescending

End Sub

Cuối cùng, nếu dữ liệu của bạn được định dạng dưới dạng bảng, mã VBA sau sẽ được sử dụng. Trong ví dụ này, bảng có tên là Sales và bảng được sắp xếp theo trường 'Country' theo thứ tự tăng dần.

Sub SortTable()

With ActiveSheet.ListObjects("Sales").Sort
   .SortFields.Clear
   .SortFields.Add Key:=Range("Sales[Country]"), Order:=xlAscending
   .Apply
End With

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 tự động lọc AutoFilter. Nó áp dụng cho trang tính đang hoạt động và sử dụng vùng lọc cho dải ô A1.

Sub TurnFilterOn()

Range("A1").AutoFilter

End Sub

Để tắt tính năng AutoFilter, bạn có thể sử dụng mã nà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 ( Trường: = 4 ) để chỉ hiển thị các bản ghi cho Vietnam.

Sub FilterByText()

Range("A1").AutoFilter Field:=4, Criteria1:="Vietnam"

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 Vietnam và Vương quốc Anh.

Sub FilterByText()

Range("A1").AutoFilter Field:=4, Criteria1:="Vietnam", Operator:=xlOr, Criteria2:="UK"

End Sub

Để lọc các số, hãy đảm bảo bạn nhập các toán tử logic vào chuỗi. Đ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

Ví dụ sau, mình thực hiện lọc danh sách theo nhiều cột. Mã VBA này chỉ lọc cột D cho Vietnam và cột H cho các số lớn hơn 5 và nhỏ hơn 20.

Sub FilterByTwoColumns()

With Range("A1")

.AutoFilter Field:=4, Criteria1:="Vietnam"
.AutoFilter Field:=8, Criteria1:=">5", Operator:=xlAnd, Criteria2:="<20"

End With

End Sub

Để xóa mọi bộ lọc được áp dụng và hiển thị tất cả dữ liệu, bạn hãy sử dụng mã VBA 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ó sẽ 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

Xem thêm: Sử dụng AutoFilter hiệu quả hơn với VBA

Tạo biểu đồ

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

Mã VBA nà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 đối tượng biểu đồ, vì vậy các tham số được đặt cho kích thước và vị trí của vùng biểu đồ. Sau đó, dữ liệu sẽ đượ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ó thể muốn sử dụng một loại biểu đồ khác, vì vậy mã này sẽ thay đổi biểu đồ thành biểu đồ đường (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ử của biểu đồ. Mã này thêm tiêu đề biểu đồ và cả nhãn cho dữ liệu.

Khi sử dụng lệnh 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 mà không thể áp dụng cho biểu đồ đường vì nó có thể gây 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

Lưu ý: Các mã VBA mà mình giới thiệu ở đây là hướng dẫn cơ bản để cung cấp cho bạn nền tảng làm việc với VBA. Trên thực tế, có thể bạn cần một số chỉnh sửa nhỏ cho các bảng tính của bạn hoạt động.

Xem thêm: Hướng dẫn về đối tượng Range trong VBA cơ bản

Mã VBA cho sự kiện - Events

Các mã macro này được chạy khi các sự kiện được kích hoạt để người dùng sử dụng Excel. Những sự kiện này có thể bao gồm thay đổi giá trị ô, mở file làm việc Excel và thay đổi tab trang tính.

Chuyển đến một trang tính cụ thể khi mở Excel

Ví dụ về mã VBA này sẽ tự động xảy ra khi sổ làm việc Excel được mở. Để điều này xảy ra, chúng ta cần sử dụng quy trình Workbook Open như sau:

1. Trong Visual Basic Editor, bạn bấm đúp vào đối tượng workbook từ cửa sổ Project Explorer mà bạn muốn sử dụng mã.

2. Chọn Workbook từ danh sách các đối tượng.

3. Chọn Open từ danh sách.

4. Dán mã VBA vào quy trình được hiển thị trong cửa sổ mã.

Để mã này được kích hoạt khi file làm việc Excel được mở, bạn không chỉnh sửa tên mục. Trong ví dụ này, bạn tự động được đưa đến trang tính có tên 'Table of Contents' và ô A2.

Private Sub Workbook_Open()

Worksheets("Table of Contents").Select
Range("A2").Select

End Sub

Có nhiều workbook events khác bao gồm BeforeSave , NewSheet và SheetActivate . Những sự kiện này có thể rất hữu ích khi tự động hóa các quy trình như xác thực mục nhập dữ liệu, thiết lập cài đặt in, v.v.

Thực hiện một hành động trên Cell Change (Thay đổi ô)

Sự kiện Change được tìm thấy trên đối tượng trang tính. Nó rất hữu ích vì nó được kích hoạt khi giá trị ô bị thay đổi.

Để tạo thủ tục Worksheet_Change, bạn làm theo các bước như sau :

1. Bấm đúp vào trang tính bạn muốn sử dụng trong cửa sổ Project Explorer để mở cửa sổ mã của nó.

2. Chọn trang tính từ danh sách đối tượng, Change từ danh sách bên cạnh và quy trình sẽ xuất hiện trong cửa sổ mã.

Trong mã này, ô đã thay đổi được kiểm tra bằng câu lệnh If, xem xét ô đã thay đổi có nằm trong cột 5 hay không và kết quả của thay đổi có phải là giá trị 'Yes' hay không .

Biến Target được cung cấp bởi worksheet change event (sự kiện thay đổi trang tính) biểu diễn ô đã thay đổi.

Nếu các điều kiện này được đáp ứng, nội dung hàng được sao chép vào cuối danh sách trên trang tính 2 và ô tính chuyển thành màu vàng.

Câu lệnh Application.EnableEvents được sử dụng như một phương pháp hay nhất vì các hành động có thể kích hoạt một sự kiện thay đổi khác trong khi sự kiện này vẫn đang chạy. Trong ví dụ này, nó không cần thiết nhưng mình muốn cung cấp cho nó mã để bảo vệ các hành động bạn đặt khỏi tạo ra lỗi.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 5 And Target = "Yes" Then

Application.EnableEvents = False
Target.EntireRow.Copy Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0)
Target.Interior.ColorIndex = 6

End If

Application.EnableEvents = True

End Sub

 Xem thêm: Hướng dẫn các phép toán trong VBA: Phép nhân, phép chia, phép cộng, mô đun

Kết luận