Macro và VBA trong Excel

Macro và VBA trong Excel 1 Mục lục • Macro• VBA• Thực hành 2 Chú ý • Phải lưu tệp dưới dạng .xlsm thay vì .xlsx; nếu không sẽ không sửdụng được Macro hay VBA 3 I. Macro • Tại sao cần Macro ?• Tìm Macro trong menu của Excel• Các bước để tạo và sử dụng Macro 4 Macro: Tại sao cần Macro ? • Demo 5 Macro: Tại sao cần Macro ? • Demo• Nhu cầu: Gom một chuỗi các hành động/thao tác để tạo thành mộthành động/thao tác duy nhất có được cùng một kết quả• Công việc đơn giản hơn• Kết quả đồng bộ, chính xác 6 • Cách 1 : phức tạp nhất, nhưng cung cấp đầy đủ công cụ nhất Macro: Tìm Macro trong menu của Excel 7 Macro: Tìm Macro trong menu của Excel 8 Macro: Tìm Macro trong menu của Excel 9 Macro: Tìm Macro trong menu của Excel 10 Macro: Tìm Macro trong menu của Excel 11 • Cách 2 Macro: Tìm Macro trong menu của Excel 12 • Cách 3 Macro: Tìm Macro trong menu của Excel 13 Bấm vào nút phía dưới bên trái của cửa sổ Excel để ghi 1 Marco Macro: Các bước để tạo và sử dụng Macro • Tạo Macro• Hình dung như quay một bộ phim• Mọi thao tác sẽ được Excel ghi lại thành Macro• Demo 14 Macro: Các bước để tạo và sử dụng Macro 15 Macro: Các bước để tạo và sử dụng Macro 16 Đặt tên cho Macro Chọn tổ hợp phím tắt để sử dụng Macro(rất nên làm, giúp tiện sử dụng sau này) Viết mô tả cho Macro(không bắt buộc, nhưng nên cóđể có thể hiểu rõ hơnMacro này làm việc gì) Sau khi đã điền đủ thông tin, bấm nút OKQuá trình ghi Macro sẽ được khởi động Macro: Các bước để tạo và sử dụng Macro 17 Lần lượt thực hiện cácthao tác mong muốn Ví dụ: chuyển 1 cell- Thành nền màu vàng- Chữ màu đỏ- Chữ đậm- Chữ nghiêng(Có 4 thao tác để thực hiện công việc) Macro: Các bước để tạo và sử dụng Macro 18 Sau khi làm xong tất cảcác thao tác mong muốn,bấm vào nút Stop Recordingđể kết thúc việc ghi Macro Macro: Các bước để tạo và sử dụng Macro • Sử dụng Macro bằng tổ hợp phím tắt 19 Để sử dụng Macro, chọn cell (hoặc nhiều cell đồng thời),bấm tổ hợp phím tắt đã lựa chọn khi tạo Macro(Trong ví dụ này là Ctrl + Shift + N) Macro: Chú ý • Không chọn vào 1 cell cụ thể trong quá trình ghi Macro! 20 II. VBA • VBA = Visual Basic of Application• Là một ngôn ngữ lập trình!• Mục đích: cung cấp cho người sử dụng khả năng để tạo thêm• các hàm phù hợp với nhu cầu của họ khi các hàm sẵn có của Excel không đủđể đáp ứng• các.Macro ! (bằng cách lập trình chứ không đơn giản bằng cách “ghi hình” Macro) 21 VBA: Ngôn ngữ lập trình • Khi bạn học ngôn ngữ như tiếng Anh, tiếng Pháp,. bạn phải• Học các từ vựng• Tuân thủ các qui tắc ngữ pháp/cú pháp của ngôn ngữ đó• Điều này cũng hoàn toàn đúng khi bạn học 1 ngôn ngữ LẬP TRÌNH• Học các từ khóa• Phải tuyệt đối tuân thủ các cú pháp của ngôn ngữ đó• Nếu sai từ khóa, sai ngữ pháp/cú pháp, chương trình của bạn sẽ không chạyđược• Nó cũng giống như bạn nói tiếng Anh: “This student are a book”  không ai hiểu bạnnói gì cả! 22 VBA: Tạo môi trường làm việc 23 Bấm vào nút “Visual Basic” trong Menu DeveloperHoặc đơn giản hơn bấm tổ hợp phím tắt “Alt + F11” VBA: Tạo môi trường làm việc 24 Bấm chuột phải vào mục “VBA Project” Chọn menu Insert, sau đó chọn Module VBA: Tạo môi trường làm việc 25 Một cửa sổ mới hiện raSoạn thảo code trong cửa sổ này VBA: viết hàm • Cú pháp Function Tên_hàm([DSách_tham_số_đầu_vào]) [as kiểu_dữ_liệu_đầu ra] [Câu_lệnh] [Tên_hàm = biểu_thức] End Function • Ví dụ Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function 26 VBA: viết hàm • Demo Function Tên_hàm([DSách_tham_số_đầu_vào]) [as kiểu_dữ_liệu_đầu ra] [Câu_lệnh] [Tên_hàm = biểu_thức] End Function • Ví dụ Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function 27 VBA: viết hàm Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function 28 - Từ khóa của ngôn ngữ lập trình VBA- Muốn viết hàm cho VBA, bắt buộc phải sử dụng 2 từ khóa này Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function VBA: viết hàm 29 - Từ khóa của ngôn ngữ lập trình VBA- Đây là kiểu dữ liệu số thực- Còn nhiều kiểu dữ liệu khác như- số tự nhiên (Integer)- kí tự (string)- . VBA: viết hàm Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function 30 - Từ khóa của ngôn ngữ lập trình VBA, dùng để khai báo 1 biến- Dim GiaTriTrungBinh As Double : khai báo biến có tên là “GiaTriTrungBinh” với dữ liệu kiểu số thực- Tên biến GiaTriTrungBinh là do người lập trình tự đặt, có một vài ràng buộc khiđặt tên biến (sẽ xem sau) VBA: viết hàm Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function 31 - Để lấy kết quả cho hàm này trả về, bắt buộc phải lấy tên hàm, gán với giá trị kết quả- Tại sao phải làm như vậy: vì đây là CÚ PHÁP của VBA  phải tuyệt đối tuân thủ !- Demo: bỏ dòng “TrungBinhCong = GiaTriTrungBinh” và cho chạy lại chương trình VBA: viết hàm Function TrungBinhCong (so1 As Double, so2 As Double) as Double Dim GiaTriTrungBinh As Double GiaTriTrungBinh = (so1 + so2) / 2 TrungBinhCong = GiaTriTrungBinh End Function 32 - Từ khóa: Function, End, As, Double, Dim (và còn nhiều từ khóa khác)- Là những từ do ngôn ngữ lập trình VBA đã định nghĩa phải học thuộc!- Khi đặt tên biến, không được phép đặt trùng với từ khóa (Demo)- Kiểu dữ liệu: Double, .  phải học thuộc!- Cú pháp phải học thuộc!- Hàm phải bắt đầu bằng Function và kết thúc bằng End Function- Để lấy kết quả, phải sử dụng tên hàm.. VBA: viết Macro • Cú pháp Sub Tên_Macro() [Câu_lệnh] End Sub • Ví dụ Sub XinChao() MsgBox(“Xin chao cac ban”) End Sub 33 VBA: viết Macro • Demo Sub Tên_Macro() [Câu_lệnh] End Sub • Ví dụ Sub XinChao() MsgBox(“Xin chao cac ban”) End Sub 34 VBA: viết Macro • Ví dụ Sub XinChao() MsgBox(“Xin chao cac ban”) End Sub 35 - Từ khóa để khai báo 1 Macro - Tên Macro, do người lập trình tự đặt ra VBA: Hàm vs. Macro • Macro• Không lấy dữ liệu đầu vào• Không có kết quả trả ra• Hàm• Có dữ liệu đầu vào• Có kết quả trả ra 36 VBA: Vào ra dữ liệu • Nhận giá trị từ một ô bảng tính vào một biến TênBiến = Range(“tên_ô”).Value TênBiến = Cells(chỉ_số_dòng, chỉ_số_cột).Value • Đưa giá trị từ một biến ra một ô bảng tính Range(“tên_ô”).Value = Cells(chỉ_số_dòng, chỉ_số_cột).Value= 37 VBA: Vào ra dữ liệu • Nhận giá trị từ một ô bảng tính vào một biến HoTen = Range(“B5”).Value HoTen = Cells(5, 2).Value • Đưa giá trị từ một biến ra một ô bảng tính Range(“A10”).Value = “Lionel Messi” Cells(3, 4).Value= 4 * 5 / 2 • Demo 38 VBA: Các kiểu dữ liệu 39 Kiểu dữ liệu Miền giá trị Byte 0 to 255 Integer -32,768 to 32,767 Long -2,147,483,648 to 2,147,483,648 Single -3.402823E+38 to -1.401298E-45 cho số âm1.401298E-45 to 3.402823E+38 cho số dương Double -1.79769313486232e+308 to -4.94065645841247E-324 cho số âm4.94065645841247E-324 to 1.79769313486232e+308 số dương Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Decimal +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places). VBA: Các kiểu dữ liệu 40 Kiểu dữ liệu Miền giá trị String (fixed length) 1 to 65,400 characters String (variable length) 0 to 2 billion characters Date January 1, 100 to December 31, 9999 Boolean True or False Object Any embedded object Variant(numeric) Any value as large as Double Variant(text) Same as variable-length string VBA: Câu lệnh lựa chọn • Kiểu 1If Then Khối_lệnhEnd If• Kiểu 2If ThenKhối_lệnh_1Else Khối_lệnh_2 End If 41 VBA: Câu lệnh lựa chọn • Kiểu 1If HoTen = “My Tam” Then MsgBox(“Day la mot ca sy noi tieng”)End If• Kiểu 2If Tuoi > 60 ThenMsgBox(“Tren 60 tuoi, gia roi!”)Else MsgBox(“Duoi 60 tuoi, con tre lam!”)End If 42 VBA: câu lệnh nhiều lựa chọn • Lệnh lựa chọn Case sử dụng khi có nhiều giá trị có thể xảy ra 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 43 VBA: câu lệnh nhiều lựa chọn • Ví dụ Dim GiaVeMayBay As Integer Select Case DiDuLich Case Tokyo GiaVeMayBay = 1000 Case Paris GiaVeMayBay = 5000 Case NewYork GiaVeMayBay = 15000 Case Else GiaVeMayBay = 0 End Select 44 VBA: Lặp • Lặp với số lần xác định tứ trước• Lặp với số lần không xác định được tử trước 45 VBA: Lặp với số lần xác định từ trước • Cú phápFor = To [Step ][Khối_lệnh]Next • Ví dụ Dim a As IntegerFor a=1 To 5Cells(a, a).Value = aNext a 46 VBA: lặp với số lần không xác định từ trước • Cứ thực hiện cho đến khi điều kiện thỏa mãnDo Until [Khối_lệnh]Loop• Cứ thực hiện khi điều kiện còn thỏa mãnDo While [Khối_lệnh]Loop 47 VBA: lặp với số lần không xác định từ trước • Cứ thực hiện cho đến khi điều kiện thỏa mãnDim i As Integeri = 1Do Until i > 5Cells(i, 1).Value = i + 10i = i + 1Loop• Cứ thực hiện khi điều kiện còn thỏa mãnDim i As Integeri = 1Do While i < 6Cells(i, 1).Value = i + 3i = i + 1Loop 48 III. Thực hành 49