Thư viện mã VBA trong Excel: Khám phá những điều thú vị (phần 2)

Trong phần 1 của thư viện mã VBA, chúng ta đã tìm hiểu về các đoạn mã VBA cho các thao tác Excel đơn giản. Trong phần 2 này, mình sẽ tiếp tục khám phá những đoạn mã VBA hữu ích để tự động hóa các công việc trên file, thư mục và sheet Excel.

Mã VBA cho bảng tính

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

Trong Excel, bạn chỉ có thể hiển thị một trang tính tại một thời điểm. Vì vậy, đoạn mã sau đây sẽ giúp bạn hiển thị tất cả các trang tính chỉ trong 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

Nếu bạn muốn bảo vệ cửa sổ làm việc của mình để ngăn các thay đổi không mong muốn, bạn có thể sử dụng đoạn mã VBA sau để đặt mật khẩu cho file làm việc của mình.

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

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

Đoạn mã VBA sau đây sẽ 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.

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

Đoạn 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 được điền sẵn. Workbook hoạt động sẽ được đính kèm vào email dưới dạng tệp đính kèm. Email sẽ hiển thị để bạn có thể kiểm tra và thực hiện các thay đổi trước khi gửi. Bạn cũng có thể thay đổi lệnh .display thành .send để gửi email với bảng tính đính kèm chỉ sau 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 = "[email protected]"
        .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 quá trình là rất hữu ích. Dưới đây là một số đoạn mã VBA để bạn khám phá.

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

Đoạn mã sau sẽ xuất tất cả các trang tính dưới dạng các tệp PDF riêng biệt. Tên trang tính được sử dụng làm tên tệp PDF và chúng sẽ được lưu vào thư mục đã chỉ định.

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 hiện đang hoạt động, bạn có thể sử dụng đoạn mã sau.

Sub ExportAsPDF()
    Dim FolderPath As String

    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, dưới đây là đoạn mã VBA đơn giản và nhanh chóng cho bạn. Đoạn mã 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. Nó cung cấp các chức năng như mở tệp, lưu tệp, chọn tệp và chọn thư mục. Dưới đây là đoạn mã VBA để bạn chỉ làm quen với nó ngay lập tức. Đoạn mã này sẽ mở workbook đã chọn và ghi số 20 vào ô A1.

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

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

Các đoạn mã VBA sau 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ẽ 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.

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

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

Mã macro sau có thể được sử dụng để kích hoạ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ố, đả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 đã áp dụng và hiển thị tất cả dữ liệu, bạn có thể 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

Tạo biểu đồ

Bạn có thể sử dụng VBA Excel để tạo biểu đồ chỉ bằng một cú nhấp chuột. Dưới đây là đoạn mã VBA để tạo biểu đồ cột 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 được thiết lập cho biểu đồ.

Sub CreateChart()
    Dim MyChart 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ể muốn thay đổi loại biểu đồ. Đoạn mã này sẽ thay đổi biểu đồ thành biểu đồ đường.

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ể thêm hoặc xóa các phần tử của biểu đồ. Đoạn mã này thêm tiêu đề biểu đồ và cả nhãn cho dữ liệu.

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

Mã VBA cho sự kiện - Events

Các mã macro sau đượ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ụ này sẽ tự động chuyển đến trang tính có tên 'Table of Contents' và ô A2 khi file làm việc Excel được mở. Để mã này được kích hoạt khi mở file làm việc Excel, 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

Sự kiện Workbook_Open chỉ là một trong nhiều sự kiện Workbook 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 nhập liệu, thiết lập cài đặt in, v.v.

Thực hiện một hành động trên ô thay đổi

Sự kiện Change được tìm thấy trên đối tượng trang tính và rất hữu ích vì nó được kích hoạt khi giá trị của ô thay đổi. Đoạn mã dưới đây sẽ được thực hiện khi bạn thay đổi giá trị của ô.

Để tạo thủ tục Worksheet_Change, bạn làm theo các bước 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ã.
2. Chọn trang tính từ danh sách các đố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 đoạn mã sau, ô đã thay đổi được kiểm tra bằng câu lệnh If, xem xét ô đó có nằm trong cột 5 và có giá trị là 'Yes' hay không. Nếu các điều kiện này được đáp ứng, giá trị hàng sẽ được sao chép vào cuối danh sách trên trang tính 2. Ô cũng sẽ được tô màu vàng.

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

Đó là những đoạn mã VBA hữu ích cho Excel. Hy vọng nó sẽ giúp bạn tối ưu hóa công việc và tiết kiệm thời gian.