8 Ứng Dụng VBA Trong Excel 12

Video hướng dẫn

Mở cửa sổ lập trình VBA

Đầu tiên các bạn sẽ tải file thực hành của mình xuống trước. Sau đó ấn tổ hợp phím Alt + F11 để mở cửa sổ lập trình Visual Basic. Trong đó nếu các bạn không thấy cửa sổ project thì có thể mở nó lên bằng cách ấn vô biểu tượng của project hoặc dung tổ hợp phím Ctrl + R.

Các bạn sẽ nhấn phải chuột vào 1 Sheet bất kỳ rồi chọn Insert tiếp đó chọn Module để tạo ra một Module để chúng ta sẽ viết code vào đó.

Sau đó ở dưới đây mình có 1 file code. Các bạn copy file code này rồi bỏ vô cái Module vừa tạo như hình vẽ.

Bạn tải file code tại đây .

Chúng ta sẽ bắt đầu vừa làm vừa giải thích về 8 ứng dụng VBA này.

Copy vùng dữ liệu từ 1 file ra file mới

Chúng ta sẽ cùng nhau đi làm ví dụ thứ nhất. Mình sẽ viết lại và lý giải hàm code của ví dụ 1 cho những bạn cùng hiểu .

Trong đó phần dấu nháy đơn là phần ghi chú mà mình ghi chú ra cho các bạn dễ hiểu, nó sẽ không ảnh hưởng gì đến chương trình. Sub và End Sub là mở đầu hay kết thúc của 1 chương trình.

Sub CopyFiletoAnotherWorkbook() là lệnh tạo tên cho chương trình.

Sheets(“Vd 1”).Range(“B3:C10”).Copy là lệnh copy vùng dữ liệu, như các bạn đã thấy thì lệnh này lấy dữ liệu từ Sheet “Vd 1” từ B3 đến C10.

Workbooks.Add là lệnh tạo thêm 1 file mới.

ActiveSheet.Paste là lệnh paste dữ liệu vừa copy sang file vừa mới tạo.

Application.DisplayAlerts = False là lệnh tắt cảnh báo.

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & “\File moi.xlsx” là lệnh lưu file này lại với tên File moi.

ActiveWorkbook.Close là lệnh đóng file sau khi lưu.

Application.DisplayAlerts = True là lệnh để bật lại cảnh báo.. Lệnh cảnh báo này chỉ xuất hiện khi tên file các bạn lưu bị trùng với tên đã tạo.

Như vậy là những bạn đã code xong. Tiếp đó để chạy code, tiên phong những bạn sẽ tích vào phần bên trái code sau đó ấn vô hình tượng chạy code lần lượt để chạy từng lệnh code .
Đoạn code :

‘Vi du 1: Copy vung du lieu tu 1 file ra file moi
Sub CopyFiletoAnotherWorkbook()
    ‘Copy vung du lieu
        Sheets(“Vd 1”).Range(“B3:C10”).Copy
    ‘Tao file moi
        Workbooks.Add
    ‘Paste du lieu
        ActiveSheet.Paste
    ‘Tat canh bao
        Application.DisplayAlerts = False
    ‘Luu file
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & “\File moi.xlsx”
        ActiveWorkbook.Close
    ‘Bat lai canh bao
        Application.DisplayAlerts = True
End Sub

Như vậy là các bạn đã copy dữ liệu sang 1 file mới 1 cách nhanh chóng hơn so với việc làm thủ công.

Bỏ ẩn toàn bộ hàng và cột

Đầu tiên khi các bạn có 1 vùng dữ liệu mà đã bị ẩn đi hàng hoặc cột. Để hiện lên thì các bạn sẽ áp dụng code ở ví dụ 2 như sau.

Đoạn code :

‘Vi du 2: Bo an toan bo hang cot
Sub ShowHiddenRows()
    ‘Bo an cot
    Columns.EntireColumn.Hidden = False
    
    ‘Bo an hang
    Rows.EntireRow.Hidden = False
End Sub

Trong đó: Columns.EntireColumn.Hidden = False là lệnh để bỏ ẩn cột đi. Hidden là lệnh đã ẩn, False sẽ là ngược lại và Columns là cột.

Rows.EntireRow.Hidden = False là lệnh bỏ ẩn hàng, Rows là hàng.

Chạy code như trên là những bạn đã hoàn toàn có thể bỏ ẩn của tổng thể những hàng cột .

Xóa hàng rỗng và cột rỗng

Khi các bạn có 1 vùng dữ liệu mà có những hàng rỗng và cột rỗng mà các bạn muốn xóa đi thì đầu tiên các bạn sẽ bôi đen nó rồi mở cửa sổ code rồi áp dụng code ở ví dụ 3 rồi chạy.

Đoạn code :

‘Vi du 3: Xoa hang rong va cot rong
Public Sub DeleteBlankRows()
    ‘Khai bao bien
    Dim SourceRange As Range
    Dim EntireRow, EntireColumn As Range
    Application.ScreenUpdating = False
    
    ‘Gan bien la vung cell duoc chon
    Set SourceRange = Application.Selection
 
    ‘Kiem tra co vung nao duoc chon
    If Not (SourceRange Is Nothing) Then
 
        For I = SourceRange.Rows.Count To 1 Step -1
            Set EntireRow = SourceRange.Cells(I, 1).EntireRow ‘Hang I cot 1
            If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
                EntireRow.Delete
            End If
        Next
 
        For I = SourceRange.Columns.Count To 1 Step -1
            Set EntireColumn = SourceRange.Cells(1, I).EntireColumn ‘Hang 1 cot I
            If Application.WorksheetFunction.CountA(EntireColumn) = 0 Then
                EntireColumn.Delete
            End If
        Next
        
        Application.ScreenUpdating = True
    End If
End Sub

Các lệnh giống bên trên mình sẽ không nói lại .

Dim SourceRange As Range là lệnh để khai báo biến, biến này chứa vùng dữ liệu các bạn chọn để xóa.

Dim EntireRow, EntireColumn As Range cũng là lệnh tạo biến, trong đó có 1 biến xóa toàn bộ hàng và 1 biến xóa toàn bộ cột.

Application.ScreenUpdating = False là lệnh giúp màn hình không bị nhấp nháy khi chạy code.

Set SourceRange = Application.Selection là lệnh gắn biến vào vùng được gắn.

If Not (SourceRange Is Nothing) Then là lệnh kiểm tra xem vùng dữ liệu có rỗng hay không.

Tiếp theo là 1 cái vòng lặp for để quét cái vùng tài liệu cần xóa. Trong đó nếu tìm thấy hàng rỗng thì xóa khi còn không rỗng thì thôi. Tương tự với vòng lặp for của cột. Sau khi chạy xong bạn sẽ thấy tổng thể những cột rỗng và hàng rỗng trong vùng tài liệu đó sẽ được xóa .

Tìm Cell rỗng

Ứng dụng này sẽ giúp tất cả chúng ta đi đến ô còn trống trong 1 cột. Để làm được điều đó tất cả chúng ta sẽ vận dụng code ở ví dụ 4 .

Đoạn code :

‘Vi du 4: tim cell rong
Sub FindEmptyCell()
    ActiveCell.Offset(1, 0).Select
       Do While Not IsEmpty(ActiveCell)
          ActiveCell.Offset(1, 0).Select
       Loop
End Sub

Trong đó ActiveCell.Offset(1, 0).Select lệnh này sẽ giúp ô trong cột đó lùi xuống 1 hàng. Còn lại là lệnh kiểm tra xem ô đó có rỗng hay không, nếu rỗng thì nó sẽ chọn còn không rỗng nó sẽ nhảy sang các ô tiếp theo.

Sau khi chạy xong code hiệu quả trả về sẽ là vị trí ô rỗng tiên phong trong cột đó .

Thay thế Cell rỗng

Cái này là lan rộng ra của ví dụ 4. Code này sẽ tìm những ô trống trong vùng tài liệu và gán cho nó 1 giá trị hay 1 thông điệp. Chúng ta sẽ vận dụng code của ví dụ 5 như sau .

Đoạn code :

‘Vi du 5: Thay the cell rong
Sub FindAndReplace()
    ‘Khai bao bien
        Dim MyRange As Range
        Dim MyCell As Range
    ‘Co luu file truoc thay doi khong?
        Select Case MsgBox(“Khong the undo hanh dong nay.  ” & _
                            “Luu workbook truoc?”, vbYesNoCancel)
            Case Is = vbYes
            ThisWorkbook.Save
            Case Is = vbCancel
            Exit Sub
        End Select
    ‘Gan bien la vung duoc chon
        Set MyRange = Selection
    ‘Bat dau vong lap.
        For Each MyCell In MyRange
    ‘Kiem tra co phai la cell rong hay khong
            If Len(MyCell.Value) = 0 Then
                MyCell = 0
            End If
    ‘Kiem tra cell tiep theo
        Next MyCell
End Sub

Mình sẽ lý giải qua về code .

Các bạn nhớ bôi đen vùng dữ liệu cần tìm trước nha để lấy dữ liệu tham chiếu. Đầu tiên mình sẽ khai báo 2 biến là MyRange và MyCell. MyRange là biến chứa vùng dữ liệu, MyCell là biến nó sẽ quét từ trái qua phải và từ trên xuống dưới.

Tiếp theo là lệnh nó sẽ hiện 1 cái cảnh báo rằng không thể undo. Nếu bạn chọn Yes nó sẽ lưu cái file trước khi mình làm, nếu như bạn chọn No hoặc Cancel nó sẽ thoát chương trình ra.

Set MyRange = Selection là lệnh gán biến với vùng dữ liệu đã chọn.

For Each MyCell In MyRange là lệnh sẽ quét tất cả cái vùng dữ liệu mới được gán ở MyRange.

Tiếp đó sẽ sử dụng hàm Len. Len là hàm độ dài. Nếu độ dài bằng 0 tức hàm rỗng thì nó sẽ gán vào đấy cái thông điệp bạn đưa vào MyCell.

Sau khi hoàn thành thì như các bạn đã thấy mình gán MyCell là “ vui long dien ” nên tất cả các ô trống sẽ được gán là “vui long dien”.

Xóa khoảng trắng thừa

Khi những bạn có 1 vùng tài liệu thừa quá nhiều khoảng chừng trắng như sau .

Để chỉnh sửa lại cho đẹp thì những bạn hoàn toàn có thể vận dụng code ở ví dụ 6 để xóa đi những khoảng chừng trắng .

Đoạn code :

‘Vi du 6: Xoa cac khoang trang thua
Sub TrimTheSpaces()
    ‘Khai bao bien
        Dim MyRange As Range
        Dim MyCell As Range
    ‘Co luu file truoc khi thuc hien thao tac khong
        Select Case MsgBox(“Khong the undo hanh dong nay.  ” & _
                            “Luu file truoc khong?”, vbYesNoCancel)
            Case Is = vbYes
            ThisWorkbook.Save
            Case Is = vbCancel
            Exit Sub
        End Select
    ‘Dinh nghia vung muc tieu
        Set MyRange = Selection
    ‘Bat dau vong lap
        For Each MyCell In MyRange
    ‘Got khoang trang.
            If Not IsEmpty(MyCell) Then
                MyCell = Excel.WorksheetFunction.Trim(MyCell)
            End If
    ‘Kiem tra cell tiep theo
        Next MyCell
End Sub

Code này cũng như bài trên nên mình không giải thích lại nữa. Ở đây các bạn chỉ cần thay đổi lại điều kiện.

If Not IsEmpty(MyCell) Then
                MyCell = Excel.WorksheetFunction.Trim(MyCell)
            End If

Đây là hàm kiểm tra Cell nào không rỗng thì nó sẽ sử dụng hàm Trim là hàm mặc định để xóa đi những khoảng trắng thừa.

Sau đó những bạn chạy thông thường và nhớ bôi đen code trước khi chạy .

Tô màu những Cell có giá trị trùng nhau

Ứng dụng này rất hay nó sẽ giúp những bạn tìm ra những giá trị trùng nhau như sau .

Các ô có giá trị giống nhau sẽ được tô màu để dễ nhận diện hơn. Để làm được điều đó tất cả chúng ta sẽ vận dụng code ở ví dụ 7 .

Đoạn code :

‘Vi du 7: To mau nhung cell trung nhau
Sub HighlightDuplicates()
    ‘Khai bao bien
        Dim MyRange As Range
        Dim MyCell As Range
    ‘Dinh nghia bien muc tieu
        Set MyRange = Selection
    ‘Bat dau vong lap
        For Each MyCell In MyRange
    ‘Kiem tra cell co trung voi cell nao khac khong.
            If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
                MyCell.Interior.ColorIndex = 36
            End If
    ‘Kiem tra cell tiep theo
        Next MyCell
End Sub

Giống như những bài trên tiên phong mình cũng sẽ tạo 2 biến nên mình sẽ không nói lại nữa .

If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
                MyCell.Interior.ColorIndex = 36
            End If

Còn đây là điều kiện của bài này. Code trên sẽ đếm các dữ liệu trong bài nếu thấy có giá trị nào xuất hiện nhiều hơn 1 lần nó sẽ tiến hành tô màu những giá trị đó. Các bạn có thể thay số 36 bằng những giá trị khác với màu tương ứng.

Trích xuất word từ Cell

Đây mà 1 ứng dụng rất hay và mình rất thích. Nó sẽ giúp những bạn lấy ra 1 ký tự bất kể trong 1 vùng tài liệu dựa trên thứ tự của từ đó .

Như các bạn đã thấy thì các bạn chỉ cần nhập hàm =FindWord(B4,2) là có thể lấy ra ký tự thứ 2 trong ô đó rồi. Trong đó B4 là vùng chữ liệu để lấy từ và 2 là thứ tự từ muốn lấy, nếu các bạn muốn đổi thành tìm từ phải qua trái thì chỉ cần đổi thành hàm FindWordRev. Để làm được điều này các bạn chỉ cần áp dụng code của ví dụ 8.

Đoạn code :

‘Vi du 8: Trich xuat tu (word) tu cell
Function FindWord(Source As String, Position As Integer) As String
     On Error Resume Next
     FindWord = Split(WorksheetFunction.Trim(Source), ” “)(Position – 1)
     On Error GoTo 0
End Function

Function FindWordRev(Source As String, Position As Integer) As String
     Dim Arr() As String
     Arr = VBA.Split(WorksheetFunction.Trim(Source), ” “)
     On Error Resume Next
     FindWordRev = Arr(UBound(Arr) – Position + 1)
     On Error GoTo 0
End Function

Function FindWord(Source As String, Position As Integer) As String
     On Error Resume Next
     FindWord = Split(WorksheetFunction.Trim(Source), ” “)(Position – 1)
     On Error GoTo 0
End Function

Đầu tiên các bạn sẽ tạo cho mình 1 cái hàm như trên lệnh tạo hàm là Function. FindWord là tên hàm. Các bạn có thể đổi tên hàm theo ý các bạn.

Nếu các bạn đổi tên hàm thì khi các bạn ra chạy code thì các bạn sẽ nhập =Tênhàm các bạn vừa tạo.

Sau đó mình sẽ tạo ra 2 cái biến, Source là biến chứa ô mình lấy dữ liệu để chọn, Position là biến chứa thứ tự từ mình muốn lấy ra.

On Error Resume Next, On Error GoTo 0 là lệnh để tránh những trường hợp lỗi không có từ tức thứ tự bạn chọn không tồn tại ký tự nào.

FindWord = Split(WorksheetFunction.Trim(Source), ” “)(Position – 1) ở dòng code này chúng ta sẽ sử dụng hàm Split để tách cái chuỗi của các bạn ra thành những ký tự bằng cách dựa vào khoảng trắng. Còn Position là vị trí thứ tự của từ đó. Trong Excel nó sẽ hiểu từ đầu tiên là 0 nhưng mình muốn hiểu là 1 nên -1 để Excel vẫn hiểu đó là 0.

Còn hàm FindWordRev sẽ là bài về nhà cho bạn tự tìm hiểu nhé. Các bạn thử tìm hiểu xem hàm đó hoạt động như thế nào.

Chúc những bạn thành công xuất sắc .

Source: https://mindovermetal.org
Category: Ứng dụng hay