Khi dùng VBA trong excel bạn cần nhớ 4 đối tượng này:
Application Objects
Workbook Objects
Worksheet Objects
Range Objects
Đối tượng application trong Excel VBA là đối tượng được sử dụng thường xuyên nhất khi thực hiện bất kỳ tác vụ nào với VBA.
Các thuộc tính cơ bản trong VBA: Các phương thức (Events) cơ bản trong VBA:
– 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.
– 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:
Ví dụ 1:
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 SubVí dụ 2:
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– 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:
Code:
Sub Alert_Close() Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True End Subkhi đó, Excel đóng mà không lưu bảng tính, tương ứng với chọn “Don’t Save”
– WorksheetFunction
Sử dụng thuộc tính WorksheetFunction để gọi các hàm trong bảng tính.
Cú pháp:
Application.WorksheetFunction.FormulaVớ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 MsgBox aCount 'Hoac viet gôp: Dim maxValue As Long maxValue = Application.WorksheetFunction.Max(Sheet1.Range("A2:A100")) MsgBox maxValue End Sub– GetOpenFilename
Cú pháp:
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 Sub2.Workbook Objects
Đối tượng Workbook trong Excel VBA là một trong những đối tượng được sử dụng thường xuyên nhất trong khi tự động hóa bất kỳ tác vụ nào với VBA. Bài này cung cấp các phương thức hay sử dụng của đối tượng Workbook.
Mỗi đối tượng Workbook tương ứng với một file excel.
Các phương thức của Workbook giúp chúng ta thực hiện các hành động khác nhau với Excel Workbooks. Ví dụ, chúng ta có thể Kích hoạt một Workbook và Xóa một Workbook hoặc Move Workbook. Và chúng ta cũng có thể Protect và UnProtect Workbooks.
Các phương thức (Events) cơ bản trong VBA :
Ví dụ cách khai báo:
'Ví dụ 1 : Đóng workbooks Workbooks.Close 'Ví dụ 2 : Thêm workbook mới Workbooks.Add 'Ví dụ 3 : Mở workbook Workbooks.Open FileName:="Test.xls", ReadOnly:=True 'Ví dụ 4 : Activate workbook Workbooks("Test.xls").Worksheets("Sheet1").Activate 'Ví dụ 5 : Save workbook Workbooks("Workbook Name").Save 'Ví dụ 6 : Save as workbook Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Filename:="D:testSample.xlsx" 'Ví dụ 7 : SaveCopyAs Workbook Workbooks("Workbook Name").Save 'Ví dụ 8 : SaveCopyAs Workbook ThisWorkbook.SaveCopyAs chúng tôi & "" & "ver1_" & ThisWorkbook.Name3.Worksheet Objects
Đối tượng Worksheet trong Excel VBA là một trong những đối tượng được sử dụng thường xuyên nhất trong khi tự động hoá các tác vụ với VBA.
Đối tượng Worksheet đại diện cho các sheet trong Workbook, tức là mỗi Workbook chứa một hoặc nhiều Worksheet.
Các phương thức (Events) cơ bản trong VBA: Ví dụ cách khai báo trong VBA:
vd 1 : ẩn worksheet Worksheets(1).Visible = False 'vd 2 : Đặt mật khẩu cho WorkSheet Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True 'vd 3: Active sheet Worksheets("data").Activate 'Or Sheets("data").Activate 'vd 4: Copy Worksheet trong VBA với Before Worksheets("Sheet3").Copy Before:=Worksheets(1) 'vd 5: Copy Worksheet trong VBA với After Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count) 'vd 6: Xóa Worksheet Sheets("Sheet2").Delete ' or Sheet2.Delete 'vd 7: Xóa Worksheet trong VBA - không hiển thị alert Application.DisplayAlerts = False Sheets("Sheet2").Delete Application.DisplayAlerts = True4.Range Objects
Đối tượng Range là đại diện của một cell (hoặc cells) trên Worksheet, là đối tượng quan trọng nhất của Excel VBA.
Ví dụ:
'vd 1 : Đưa dữ liệu vào ô A5 Worksheets("Sheet1").Range("A5").Value = "5235" 'vd 2 : Đưa dữ liệu vào range A1:A4 Worksheets("Sheet1").Range("A1:A4").Value = 5 'vd 3 : Khai báo một range và set giá trị bằng 8 Dim rangeObj As Range Set rangeObj = Range("A1:C4") rangeObj .Value = 8Các phương thức (Events) cơ bản trong VBA: