Bài 8. Events, Application Object

[befaint]

Bài 8. Events, Application Object

(Danh sách các bài viết về VBA xem ở đây Index - Các bài viết về VBA)

1. Events
Một số sự kiện thường dùng.
1.1. Worksheet_Activate
Sự kiện khi Worksheet được kích hoạt hiện hành.
Ví dụ:

Private Sub Worksheet_Activate()
    'Nhảy tới ô bên dưới ô cuối cùng có dữ liệu ở cột B'
    Dim Cll As Range
    Set Cll = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    Cll.Select
End Sub

1.2. Worksheet_Change
Sự kiện thay đổi, cập nhật dữ liệu của cell trên Worksheet.
Ví dụ:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Range("B3:B100")  'Vùng nhâp du liêu.'
    If Not Intersect(Target, Rng) Is Nothing Then   'Nêu ô dang xét không phai không thuôc vùng Rng thì:'
            Target.Offset(0, 2).Value = Now         'Nhâp thoi gian hiên hành vào ô tuong ung cách 2 côt'
    End If
End Sub

Lưu ý: Nếu không chỉ định địa chỉ cho Target thì Target sẽ nhận là toàn bộ cells trên bảng tính.

1.3. Worksheet_SelectionChange
Sự kiện khi di chuyển trỏ chuột trên chọn cells trên Worksheet.
Ví dụ:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Exit Sub   '<--------- Hủy dòng này để chạy code'
   
    Dim iRow As Long, iCol As Long, i As Long, j As Long
    Application.ScreenUpdating = False
    Cells.Interior.ColorIndex = 0   'Xóa màu nên cu'
    iRow = ActiveCell.Row           'Tra vê dòng cua ô hiên hành'
    iCol = ActiveCell.Column        'Tra vê côt cua ô hiên hành'
    For i = 1 To iRow
        Cells(i, iCol).Interior.ColorIndex = 6  'Tô màu ô cùng dòng'
    Next i
    For j = 1 To iCol
        Cells(iRow, j).Interior.ColorIndex = 6  'Tô màu ô cùng côt'
    Next j
    Application.ScreenUpdating = True
End Sub

1.4. Workbook_BeforeClose
Sự kiện trước khi đóng Workbook.
Ví dụ:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Temp").Visible = xlSheetHidden
    ThisWorkbook.Save
End Sub

1.5. Workbook_BeforePrint
Sự kiện trước khi in bảng tính.
Ví dụ:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    'Luu bang tinh truoc khi in'
    ThisWorkbook.Save
End Sub

1.6. Workbook_Open
Sự kiện khi mở Workbook.
Ví dụ:

Private Sub Workbook_Open()
    'Gioi han ngay su dung file:'
    Dim dkNgay As Long
    dkNgay = Sheet1.Range("A1").Value2  'Nhâp ngày diêu kiên vào [A1] cua sheets "Data" '
    If CLng(Date) > dkNgay Then     'Nêu ngày hiên tai > ngày diêu kiên'
        MsgBox "Quá han su dung!" & vbNewLine & "Liên hê tác gia:...", , "Thông báo"
    Else
        MsgBox "Xin chào!", , "Thông báo"
    End If
End Sub

2. Application Object
2.1. ScreenUpdating
Điều khiển cập nhật màn hình: Khi điều khiển các đối tượng (sheets, range, cells), việc vô hiệu hóa cập nhật màn hình (tránh nhấp nháy) giúp tốc độ code nhanh hơn.

2.2. Calculation
Mặc định thiết lập bảng tính tự động tính toán (CalculationAutomatic), khi điều khiển các đối tượng (sheets, range, cells), ví dụ gán kết quả xuống bảng tính, các công thức thực hiện tính toán với giá trị vừa được gán, máy tính phải xử lý thêm tác vụ, làm giảm tốc độ code thực hiện lệnh. Vậy, để tăng tốc độ cho code ta sẽ chuyển thiết lập về dạng thủ công (CalculationManual).

Xét 2 ví dụ để so sánh:

Sub ScreenAndCal_ON()
    'Vô hiêu hóa câp nhât màn hình'
    Application.ScreenUpdating = False
    'Thiêt lâp tinh toan vê dang thu công'
    Application.Calculation = xlCalculationManual
    Dim i As Long, T As Double
    T = Timer
   
    'Vòng lap gan sô thu tu: 1 - 100,000'
    For i = 1 To 100000
        Sheet1.Range("A1").Offset(i, 0).Value = i
    Next i
   
    'Câp nhât màn hình'
    Application.ScreenUpdating = True
    'Thiêt lâp tinh toan vê dang tu dông'
    Application.Calculation = xlCalculationAutomatic
   
    MsgBox Round(Timer - T, 2) & " giây"    'T=2.62 giây'
End Sub
'--------------'
Sub ScreenAndCal_OFF()
    Dim i As Long, T As Double
    T = Timer
   
    'Vòng lap gan sô thu tu: 1 - 100,000'
    For i = 1 To 100000
        Sheet1.Range("A1").Offset(i, 0).Value = i
    Next i
   
    MsgBox Round(Timer - T, 2) & " giây"    'T=3.12 giây'
End Sub

2.3. DisplayAlerts
Điều khiển hộp thoại thông báo khi thực thi code.
- Ví dụ, khi đóng file xuất hiện hộp thoại:

Sub Alert_Close()
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Khi đó, Workbook được đóng mà không lưu, tương ứng với lựa chọn “Don’t Save”.

2.4. WorksheetFunction
Sử dụng thuộc tính WorksheetFunction để gọi các hàm trong bảng tính.
Cấu trúc:

Application.WorksheetFunction.Formula
'Với Formula là một hàm trong bảng tính (sum, countA, Match…)'

Ví dụ:

Sub Worksheet_Function()
    Dim WF As WorksheetFunction
    Set WF = Application.WorksheetFunction
    Dim aCount As Long
    aCount = WF.CountIf(Sheet1.Range("A2:A10"), ">0")
    MsgBox aCount

    'Hoac viet gôp:'
    Dim maxValue As Long
    maxValue = Application.WorksheetFunction.Max(Sheet1.Range("A2:A100"))
    MsgBox maxValue
End Sub

2.5. GetOpenFilename
Lấy tên file từ cửa sổ chọn

Application.GetOpenFilename([FileFilter],[FilterIndex],[Title],[ButtonText],MultiSelect])

FileFilter: Chuỗi đưa ra điều kiện lọc loại tập tin trong cửa sổ chọn.
FilterIndex: Chỉ định chỉ số của loại tập tin mặc định được lọc.
Title: Tiêu đề của hộp thoại chọn tập tin, mặc định là “Open”.
ButtonText: Với MAC OS.
MultiSelect: True cho phép chọn nhiều tập tin, False (mặc định) chỉ cho chọn một.
Ví dụ:

Sub GetFileName_Any()
    Dim FilePath As String
    FilePath = Application.GetOpenFilename()
    MsgBox FilePath
End Sub
'--------------'
Sub GetFileName_Excel()
    Dim FilePath As String
    FilePath = Application.GetOpenFilename("Excel file (*.xlsx), *.xlsx")
    MsgBox FilePath
    ''Workbooks.Open (OpenFile)
End Sub

 

[tueyennhi]

Bài 8. Events, Application Object

(Danh sách các bài viết về VBA xem ở đây Index - Các bài viết về VBA)

1. Events
Một số sự kiện thường dùng.
1.1. Worksheet_Activate
Sự kiện khi Worksheet được kích hoạt hiện hành.
Ví dụ:

Private Sub Worksheet_Activate()
    'Nhảy tới ô bên dưới ô cuối cùng có dữ liệu ở cột B'
    Dim Cll As Range
    Set Cll = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    Cll.Select
End Sub

1.2. Worksheet_Change
Sự kiện thay đổi, cập nhật dữ liệu của cell trên Worksheet.
Ví dụ:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Set Rng = Range("B3:B100")  'Vùng nhâp du liêu.'
    If Not Intersect(Target, Rng) Is Nothing Then   'Nêu ô dang xét không phai không thuôc vùng Rng thì:'
            Target.Offset(0, 2).Value = Now         'Nhâp thoi gian hiên hành vào ô tuong ung cách 2 côt'
    End If
End Sub

Lưu ý: Nếu không chỉ định địa chỉ cho Target thì Target sẽ nhận là toàn bộ cells trên bảng tính.

1.3. Worksheet_SelectionChange
Sự kiện khi di chuyển trỏ chuột trên chọn cells trên Worksheet.
Ví dụ:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Exit Sub   '<--------- Hủy dòng này để chạy code'
   
    Dim iRow As Long, iCol As Long, i As Long, j As Long
    Application.ScreenUpdating = False
    Cells.Interior.ColorIndex = 0   'Xóa màu nên cu'
    iRow = ActiveCell.Row           'Tra vê dòng cua ô hiên hành'
    iCol = ActiveCell.Column        'Tra vê côt cua ô hiên hành'
    For i = 1 To iRow
        Cells(i, iCol).Interior.ColorIndex = 6  'Tô màu ô cùng dòng'
    Next i
    For j = 1 To iCol
        Cells(iRow, j).Interior.ColorIndex = 6  'Tô màu ô cùng côt'
    Next j
    Application.ScreenUpdating = True
End Sub

1.4. Workbook_BeforeClose
Sự kiện trước khi đóng Workbook.
Ví dụ:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Temp").Visible = xlSheetHidden
    ThisWorkbook.Save
End Sub

1.5. Workbook_BeforePrint
Sự kiện trước khi in bảng tính.
Ví dụ:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    'Luu bang tinh truoc khi in'
    ThisWorkbook.Save
End Sub

1.6. Workbook_Open
Sự kiện khi mở Workbook.
Ví dụ:

Private Sub Workbook_Open()
    'Gioi han ngay su dung file:'
    Dim dkNgay As Long
    dkNgay = Sheet1.Range("A1").Value2  'Nhâp ngày diêu kiên vào [A1] cua sheets "Data" '
    If CLng(Date) > dkNgay Then     'Nêu ngày hiên tai > ngày diêu kiên'
        MsgBox "Quá han su dung!" & vbNewLine & "Liên hê tác gia:...", , "Thông báo"
    Else
        MsgBox "Xin chào!", , "Thông báo"
    End If
End Sub

2. Application Object
2.1. ScreenUpdating
Điều khiển cập nhật màn hình: Khi điều khiển các đối tượng (sheets, range, cells), việc vô hiệu hóa cập nhật màn hình (tránh nhấp nháy) giúp tốc độ code nhanh hơn.

2.2. Calculation
Mặc định thiết lập bảng tính tự động tính toán (CalculationAutomatic), khi điều khiển các đối tượng (sheets, range, cells), ví dụ gán kết quả xuống bảng tính, các công thức thực hiện tính toán với giá trị vừa được gán, máy tính phải xử lý thêm tác vụ, làm giảm tốc độ code thực hiện lệnh. Vậy, để tăng tốc độ cho code ta sẽ chuyển thiết lập về dạng thủ công (CalculationManual).

Xét 2 ví dụ để so sánh:

Sub ScreenAndCal_ON()
    'Vô hiêu hóa câp nhât màn hình'
    Application.ScreenUpdating = False
    'Thiêt lâp tinh toan vê dang thu công'
    Application.Calculation = xlCalculationManual
    Dim i As Long, T As Double
    T = Timer
   
    'Vòng lap gan sô thu tu: 1 - 100,000'
    For i = 1 To 100000
        Sheet1.Range("A1").Offset(i, 0).Value = i
    Next i
   
    'Câp nhât màn hình'
    Application.ScreenUpdating = True
    'Thiêt lâp tinh toan vê dang tu dông'
    Application.Calculation = xlCalculationAutomatic
   
    MsgBox Round(Timer - T, 2) & " giây"    'T=2.62 giây'
End Sub
'--------------'
Sub ScreenAndCal_OFF()
    Dim i As Long, T As Double
    T = Timer
   
    'Vòng lap gan sô thu tu: 1 - 100,000'
    For i = 1 To 100000
        Sheet1.Range("A1").Offset(i, 0).Value = i
    Next i
   
    MsgBox Round(Timer - T, 2) & " giây"    'T=3.12 giây'
End Sub

2.3. DisplayAlerts
Điều khiển hộp thoại thông báo khi thực thi code.
- Ví dụ, khi đóng file xuất hiện hộp thoại:
View attachment 184926

Sub Alert_Close()
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Khi đó, Workbook được đóng mà không lưu, tương ứng với lựa chọn “Don’t Save”.

2.4. WorksheetFunction
Sử dụng thuộc tính WorksheetFunction để gọi các hàm trong bảng tính.
Cấu trúc:

Application.WorksheetFunction.Formula
'Với Formula là một hàm trong bảng tính (sum, countA, Match…)'

Ví dụ:

Sub Worksheet_Function()
    Dim WF As WorksheetFunction
    Set WF = Application.WorksheetFunction
    Dim aCount As Long
    aCount = WF.CountIf(Sheet1.Range("A2:A10"), ">0")
    MsgBox aCount

    'Hoac viet gôp:'
    Dim maxValue As Long
    maxValue = Application.WorksheetFunction.Max(Sheet1.Range("A2:A100"))
    MsgBox maxValue
End Sub

2.5. GetOpenFilename
Lấy tên file từ cửa sổ chọn

Application.GetOpenFilename([FileFilter],[FilterIndex],[Title],[ButtonText],MultiSelect])

FileFilter: Chuỗi đưa ra điều kiện lọc loại tập tin trong cửa sổ chọn.
FilterIndex: Chỉ định chỉ số của loại tập tin mặc định được lọc.
Title: Tiêu đề của hộp thoại chọn tập tin, mặc định là “Open”.
ButtonText: Với MAC OS.
MultiSelect: True cho phép chọn nhiều tập tin, False (mặc định) chỉ cho chọn một.
Ví dụ:

Sub GetFileName_Any()
    Dim FilePath As String
    FilePath = Application.GetOpenFilename()
    MsgBox FilePath
End Sub
'--------------'
Sub GetFileName_Excel()
    Dim FilePath As String
    FilePath = Application.GetOpenFilename("Excel file (*.xlsx), *.xlsx")
    MsgBox FilePath
    ''Workbooks.Open (OpenFile)
End Sub

Public Private Worksheet_Activate()

    'Nh?y t?i ô bên du?i ô cu?i cùng có d? li?u ? c?t B'

    Dim Cll As Range

    Set Cll = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

    Cll.Select

End Sub

Cái này mình copy vào this book sao nó không áp dụng cho toàn bộ sheet nhỉ?

 

[HieuCD]

Public Private Worksheet_Activate()

    'Nh?y t?i ô bên du?i ô cu?i cùng có d? li?u ? c?t B'

    Dim Cll As Range

    Set Cll = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

    Cll.Select

End Sub

Cái này mình copy vào this book sao nó không áp dụng cho toàn bộ sheet nhỉ?

Đọc kỹ hướng dẫn trước khi dùng

 

[tueyennhi]

Đọc kỹ hướng dẫn trước khi dùng

Vậy là dùng sheet nào copy vào sheet đó. Trước mình có dùng một đoạn code nhưng tác dụng khác và chỉ cần copy vào this workbook là nó chạy cho tất cả các sheet hiện hành.