VBA Excel Events, Application Object

By Lê Quốc Thái

20/06/2019

2

614

Đánh giá lequocthai.com:

Voted: 0 Votes: 0

Your page rank:

Loạt các bài viết có liên quan chuyên đề VBA:
VBA Excel Hộp thoại thông báo
VBA Excel Biến trong VBA Excel
VBA Excel sử dụng Scripting Dictionary
VBA Excel Biến trong VBA Excel
VBA Excel Workbook, worksheet
VBA Excel Range, Cells
VBA Excel Hàm trong Excel VBA
VBA Excel Events, Application Object
VBA Excel Function and Sub Excel VBA
VBA Excel Array Mảng trong Excel VBA
VBA Excel FileSystemObject trong Excel VBA
VBA Excel Collection trong Excel VBA
VBA Excel Hashtable trong Excel VBA
VBA Excel Stack trong Excel VBA
VBA Excel Queue trong Excel VBA
VBA Excel SortedList Excel VBA
VBA Excel ArrayList Excel VBA
VBA Excel Ví dụ về Scripting Dictionary
Sách VBA Excel 2016 power programming with vba (pdf)

VBA Excel Function and Sub Excel 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()
    'Jump to last cell in column:'
    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)
    'Hight light row and column from activecell:'

    Exit Sub   '<---------Bo dòng này dê chay 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)
    'Hide sheets:'
    Sheets("Temp").Visible = xlSheetHidden

    'Luu bang tinh:'
    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).

Xem 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
'Lây gôc thoi gian chay code:
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

'Thoi gian hoàn thành:
MsgBox Round(Timer - T, 2) & " giây" 'T=2.62 giây
End Sub

‘————–‘

Sub ScreenAndCal_OFF()
Dim i As Long, T As Double
'Lây gôc thoi gian chay code:
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

'Thoi gian hoàn thành:
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.

– 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 đó, Excel đóng mà không lưu bảng tính, tương ứng với 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

Tải file ví dụ: Events | Application Object