Bài giảng Tin học văn phòng - Bài 13: Lập trình VBA trong excel - Nguyễn Thị Phương Thảo

Nội dung text: Bài giảng Tin học văn phòng - Bài 13: Lập trình VBA trong excel - Nguyễn Thị Phương Thảo

1. BÀI 13 LẬP TRÌNH VBA TRONG EXCEL Môn : Tin học văn phòng Giảng viên : Nguyễn Thị Phương Thảo Khoa Công nghệ thông tin – ĐH Thủy Lợi Email : thaont@tlu.edu.vn Bài giảng : 1
2. NỘI DUNG • GIỚI THIỆU VBA • MỘT SỐ KIẾN THỨC CƠ BẢN VBA • MACRO • XÂY DỰNG HÀM MỚI TRONG EXCEL • MỘT SỐ VÍ DỤ CƠ BẢN VỀ MARCO 2
3. GIỚI THIỆU VBA • VBA viết tắt của Visual basic for application • Sử dụng trên tất cả các ứng dụng Microsoft Office • Bật menu VBA – Vào File Chọn Options Chọn Customize Ribbon – Bên phần Customize the Ribbon, chọn Main Tabs – Chọn Developer – Nhấn OK 3
4. VISUAL BASIC EDITOR • Là chương trình đi kèm với excel cho phép giao tiếp với excel. • Khởi động VBE: – Ấn Alt + F11 – Vào ribbon Developer, chọn Visual basic 4
5. VISUAL BASIC EDITOR 5
6. BIẾN • Là vùng nhớ để máy tính lưu trữ dữ liệu. Mỗi biến có một tên. • Các đặt tên của biến: – Phải nhỏ hơn 255 ký tự – Không chứa khoảng trắng – Không bắt đầu bằng ký tự số – Không chứa các ký tự đặc biệt như &,%, 6
7. BIẾN • Ví dụ: Tên biến My_Car He&HisFather My.Car Long_Name_Can_beUse ThisYear Group88 1NewBoy Student ID 7
8. BIẾN • Khai báo nhiều biến trên cùng 1 dòng – Dim variableName as DataType • Dim: Từ khóa chỉ phạm vi sử dụng của biến. – Dim: biến sử dụng trong thủ tục con hoặc ở trong một module – Public: biến khai báo ở mức module. Có thể sử dụng trong tất cả các module nằm trong workbook – Private: biến khai báo ở mức module. Chỉ sử dụng trong module đó • VariableName: tên biến • DataType: kiểu dữ liệu – Phải khai báo biến trước khi sử dụng biến 8
9. BIẾN • Khai báo nhiều biến trên cùng một dòng – Dim password As String, firstnum As Integer • Ví dụ: Dim password As String Dim secondnum As Integer Dim yourName As String*120 Dim total As Integer Dim firstnum As Integer Dim BirthDay As Date 9
10. KIỂU DỮ LIỆU • Dữ liệu kiểu số: Type Storage Range of Values Byte 1 byte 0 to 255 Integer 2 bytes -32,768 to 32,767 Long 4 bytes -2,147,483,648 to 2,147,483,648 -3.402823E+38 to -1.401298E-45 cho số âm Single 4 bytes 1.401298E-45 to 3.402823E+38 cho số dương -1.79769313486232e+308 to -4.94065645841247E-324 cho số âm Double 8 bytes 4.94065645841247E-324 to 1.79769313486232e+308 số dương Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807 +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use Decimal 12 bytes 10 +/- 7.9228162514264337593543950335 (28 decimal places).
11. KIỂU DỮ LIỆU • Dữ liệu khác: Data Type Storage Range String(fixed length) Length of string 1 to 65,400 characters String(variable length) Length + 10 bytes 0 to 2 billion characters January 1, 100 to December Date 8 bytes 31, 9999 Boolean 2 bytes True or False Object 4 bytes Any embedded object Variant(numeric) 16 bytes Any value as large as Double Variant(text) Length+22 bytes Same as variable-length string 11
12. KIỂU DỮ LIỆU • Ví dụ: Private Sub Button1_Click() Dim YourName As String Dim BirthDay As Date Dim Income As Currency YourName = "Alex" BirthDay = "1/8/1980" Income = 1000 Range("A1") = YourName Range("A2") = BirthDay Range("A3") = Income End Sub 12
13. VÀO RA DỮ LIỆU • Nhận giá trị từ một ô bảng tính vào một biến: Var_name = Range(“cell”).Value Var_name = Cells(rowIndex, colIndex).Value • Đưa giá trị từ một biến ra một ô bảng tính: Range(“Cell”). Value= Cells(x, y). Value= 13
14. VÀO RA DỮ LIỆU • Ví dụ lấy giá trị từ ô bảng tính: hoten = Range(“A1”).Value tuoi = Cell(2, 1).Value • Ví dụ đưa giá trị ra ô bảng tính: Range(“A1”).Value = “Nguyễn Văn A” Dim r as Double r = 2 Cell(4, 1).Value = Excel.WorksheetFunction.Pi()*r^2 14
15. CÂU LỆNH LỰA CHỌN IF • Cú pháp dạng 1: If then Khối_lệnh End If • Cú pháp dạng 2: If then Khối_lệnh_1 Else Khối_lệnh_2 End If 15
16. CÂU LỆNH LỰA CHỌN IF • Ví dụ Cú pháp dạng 1: If LRegion ="N" Then LRegionName = "North" End If • Ví dụ Cú pháp dạng 2: If Range(“A1”).Value =“Saturday” or Range(“A1”).Value = “Sunday” then Range(“A2”).Value = “Yes” Else Range(“A2”).Value = “No” End If 16
17. CÂU LỆNH LỰA CHỌN IF • Cú pháp dạng 3: If Then [Khối_lệnh_1] ElseIf Then [khối_lệnh_n] Else [Khối_lệnh_2]] End If 17
18. CÂU LỆNH LỰA CHỌN IF • Ví dụ Cú pháp dạng 3: If LRegion ="N" Then LRegionName = "North" ElseIf LRegion = "S" Then LRegionName = "South" ElseIf LRegion = "E" Then LRegionName = "East" Else LRegionName = "West" End If 18
19. CÂU LỆNH LỰA CHỌN CASE • Lệnh lựa chon Case sử dụng khi có nhiều giá trị có thể xảy ra • Cú pháp: Select Case [Case điều_kiện_1] [khối_lệnh_1] [Case điều_kiện_n] [khối_lệnh_n] [Case Else] [khối_lệnh_else] End Select 19
20. CÂU LỆNH LỰA CHỌN CASE • Ví dụ 1: Select Case LRegion Case "N" LRegionName = "North" Case "S" LRegionName = "South" Case "E“ LRegionName = "East" Case "W" LRegionName = "West" End Select 20
21. CÂU LỆNH LỰA CHỌN CASE • Ví dụ 2: Select Case LNumber Case 1 To 10 LRegionName = "North" Case 11 To 20 LRegionName = "South" Case 21 To 30 LRegionName = "East" Case Else LRegionName = "West" End Select 21
22. CÂU LỆNH LỰA CHỌN CASE • Ví dụ 3: Select Case LNumber Case 1, 2 LRegionName = "North" Case 3, 4, 5 LRegionName = "South" Case 6 LRegionName = "East" Case 7, 11 LRegionName = "West" End Select 22
23. LẶP VỚI SỐ LẦN XÁC ĐỊNH • Thực hiện lặp một khối lệnh theo một biến đếm với số lần lặp xác định • Cú pháp: For = To [Step ] [Khối_lệnh] Next [ ] 23
24. LẶP VỚI SỐ LẦN XÁC ĐỊNH • Ví dụ: Dim i As Integer For i = 1 To 10 Cells(i, 1).Value = i Next i Dim i As Integer, j As Integer For i = 1 To 10 For j = 1 To 2 Cells(i, j).Value = 100 Next j Next i 24
25. LẶP KHÔNG XÁC ĐỊNH • Dạng 1: Khối lệnh được thực hiện khi điều kiện còn đúng • Cú pháp: Do While [Khối_lệnh] Loop • Ví dụ: Dim i As Integer i = 1 Do While i < 6 Cells(i, 1).Value = 20 i = i + 1 Loop 25
26. LẶP KHÔNG XÁC ĐỊNH • Dạng 2: Khối lệnh được thực hiện cho đến khi điều kiện sai • Cú pháp: Do Until [Khối_lệnh] Loop • Ví dụ: Dim i As Integer i = 1 Do Until i > 5 Cells(i, 1).Value = 20 i = i + 1 Loop 26
27. MACRO • Macro Là tập hợp các lệnh và hàm được lưu trữ trong một module của VBA nhằm thực hiện một nhiệm vụ nào đó • Macro là một chương trình con dạng thủ tục (Sub) với từ khóa Public, không có tham số • Ví dụ: Sub Macro() ‘ Macro Public Sub Macro () ‘Macro Private Sub Macro() ‘Thủ tục với từ khoá Private,không phải Macro Sub Macro(Input as Double) ‘ Thủ tục có tham số, không phải Macro Public Function Macro() as Double ‘Hàm, không phải Macro 27
28. CẤU TRÚC MACRO [Public] Sub Tên_Macro() [Câu_lệnh] End Sub • Ví dụ: Sub Macro1 () For n = 1 To 5 Cells(n, 1) = n Next n End Sub 28
29. TẠO MACRO • Cách 1: Ghi lại các thao tác – Bước 1: Vào ribbon Developer, chọn Record Macro – Bước 2: Đặt tên Macro và phím tắt – Bước 3: Thực hiện các thao tác muốn ghi lại – Bước 4: Kết thúc bằng cách chọn Stop Recording 29
30. TẠO MACRO • Cách 2: Tạo Macro sử dụng VBA – Bước 1: Vào ribbon Developer, chọn Visual Basic – Bước 2: Nhấn chuột phải vào VBAProject, chọn Inset Module – Bước 3: Nhập các dòng lệnh cho Macro – Bước 4: chọn trình đơn File/Close and Return to Microsoft Excel để trở về màn hình chính của Excel 30
31. CHẠY MACRO • Cách 1: – Bước 1: Vào ribbon Developer, chọn Macro – Bước 2: Nhấn chuột phải vào VBAProject, chọn Inset Module – Bước 3: Chọn Macro cần chạy, nhấn Run • Cách 2: – Bước 1: Vào ribbon Developer, chọn Visual Basic – Bước 2: Chọn Macro đã có, nhấn nút Run hoặc ấn F5 • Cách 3: – Nhấn phím tắt đã quy ước khi tạo Macro 31
32. XÂY DỰNG HÀM MỚI • Hàm: là những công thức được định nghĩa sẵn trong excel • Cấu trúc: [Public/Private] Function Tên_hàm([DSách_tham_số]) [as kiểu dữ liệu] [Câu_lệnh] [Tên_hàm = biểu_thức] End Function 32
33. XÂY DỰNG HÀM MỚI • Hàm: là những công thức được định nghĩa sẵn trong excel • Cấu trúc: [Public/Private] Function Tên_hàm( [DSách_tham_số] ) as [kiểu dữ liệu] [Câu_lệnh] [Tên_hàm = biểu_thức] End Function • Ví dụ: Public Function Dien_Tich(Rong As Double, Cao As Double) As Double Dien_Tich = Rong * Cao ‘Hàm tính diện tích hình chữ nhật End Function 33
34. SỬ DỤNG HÀM 34
35. THỰC HÀNH 35