Giáo trình Excel nâng cao

phím tắt cho Macro thông qua trình quản lý Macro. Chi tiết tham khảo phần “Quản lý 
 Macro” ở phần trên. 
 b. Thực thi Macro thông qua trình quản lý Macro 
 Nhằm tạo điều kiện thuận lợi cho người sử dụng trong khi làm việc với Macro, 
 chương trìnhExcel đã tích hợp sẵn một trình quản lý Macro. Để hiển thị trình 
 quản lý Macro, chọn trình đơn Tools Macro Macros hoặc nhấn tổ hợp phím 
 ALT+F8. (Xem Hình 6.9) 
 Trang 77/ 89 
 Trong cửa sổ Macro, các Macro được tạo theo kịch bản hoặc bằng VBAIDE có 
trong phiên làm việc hiện tại của Excel sẽ được hiển thị trong một danh sách. Tất cả 
các thao tác quản lý Macro sẽ được thực hiện dễ dàng. Để bắt đầu một thao tác nào đó, 
trước hết cần phải chọn Macro có trong danh sách: 
 Để thực thi Macro (chạy Macro): kích chuột vào nút Run. 
 Để hiệu chỉnh Macro: kích chuột vào nút Edit, cửa sổ lệnh trong VBAIDE chứa các 
mã lệnh của Macro được chọn sẽ được hiển thị để người sử dụng có thay đổi mã lệnh 
trong Macro đó. Kích chuột vào nút Options sẽ hiển thị hộp thoại lựa chọn, cho 
phép người sử dụng thiết lập lại phí Hình: Hộp thoại Macro Options. 
 Hình 6.4 
 c. Thực thi Macro trực tiếp từ VIBAIDE 
Cách thực thi Macro trực tiếp từ VBAIDE rất thích hợp khi người sử dụng muốn thử 
nghiệm ngay Macro trong quá trình xây dựng nó. 
Để thực thi Macro nào đó trong VBAIDE, cần thực hiện như sau: 
1. Trong cửa sổ mã lệnh của VBAIDE, đặt con trỏ vào giữa khối Sub  End Sub. 
2. Nhấn phím F5 hoặc chọn biểu tượng trên thanh công cụ. 
 Trong trường hợp người sử dụng không đặt con trỏ giữa, một đanh sách các 
Macro hiện lên để người dùng lựa chọn để thực thi Macro cần thiết. 
 Trang 78/ 89 
 Hình 6.5 
 Khi Macro được tạo ra chưa đáp ứng đủ nhu cầu thì người sử dụng thường có 
 nhu cầu thay đổi mã lệnh của Macro đó. Quá trình hiệu chỉnh Macro được thực hiện 
 thông qua VBAIDE. 
IV. XÂY DỰNG HÀM MỚI TRONG EXCEL BẰNG VBA 
 1. Khái niệm về hàm trong Excel 
 a. Khái niệm về hàm trong Excel 
 Hàm là những công thức đã được định nghĩa sẵn trong Excel thực hiện tính toán 
 dựa trên các số liệu đầu vào, gọi là tham số, theo một trình tự đã được lập trình sẵn 
 nhằm thực hiện các phép tính từ đơn giản đến phức tạp. 
 Để hiểu rõ hơn về cấu trúc của một hàm, ta tìm hiểu về hàm ROUND có sẵn trong 
 Excel, là hàm dùng để làm tròn số: 
 Hình 6.6 
  Cấu trúc. Một hàm bắt đầu bằng dấu bằng “=”, tiếp sau là tên hàm, dấu ngoặc 
 đơn “(“, danh sách các tham số cách nhau bằng dấu phẩy “,” và cuối cùng là dấu ngoặc 
 đơn “)”. 
  Tên hàm. Ấn phím SHIFT+F3 để hiển thị danh sách tất cả các hàm trong 
 Excel. 
  Các tham số. Tham số có thể là số, chữ, giá trị logic như TRUE hoặc FALSE, 
 Trang 79/ 89 
mảng, giá trị lỗi như #NA, hoặc tham chiếu đến một ô khác. Tham số truyền vào phải 
có kiểu thích hợp với kiểu của từng tham số tương ứng của hàm. Tham số truyền vào 
có thể là một hằng số, công thức, hoặc là một hàm bất kỳ. 
  Chú thích hàm. Chú thích hàm dùng để thể hiện cấu trúc và danh sách các 
tham số của hàm, hiện lên khi ta nhập vào tên hàm. Chú thích hàm chỉ xuất hiện đối 
với những hàm được xây dựng sẵn trong Excel. 
2. Tạo hàm mới bằng VBA 
 a. Tạo hàm mới bằng VBA 
 Excel cho phép xây dựng các hàm mới bằng VBA, và đặc biệt, việc sử dụng các 
hàm mới này không khác gì so với việc sử dụng các hàm có sẵn của Excel. Hàm mới 
luôn mang đặc tính: 
  Trả về một giá trị nào đó, tương tự như hàm có sẵn trong Excel; 
  Hàm mới có thể sử dụng như một chương trình con trong VBA, nghĩa là nó vừa 
 có thể sử dụng trong bảng tính (trong các ô), đồng thời có thể sử dụng trong các 
 chương trình viết bằng VBA. 
 b. Cấu trúc hàm 
 Hàm là một chương trình con dạng Function. Khác với Macro, hàm là chương 
trình con có giá trị trả về và có thể có tham số. 
Khi tạo hàm mới, người sử dụng cần phải tuân thủ theo dạng thức khai báo như sau: 
[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] 
[Exit Function] 
[Câu_lệnh] 
[Tên_hàm = biểu_thức] 
End Function 
 Trong đó: 
 Public: (tuỳ chọn) là từ khoá biểu thị phạm vi của hàm, hàm có thể được sử 
 dụng ở bất kỳ đâu trong tất cả các dự án VBA hiện có. Khi có từ khoá Public, 
 tên hàm sẽ được hiển thị trong danh sách hàm của Excel. 
 Private: (tuỳ chọn) là từ khoá biểu thị phạm vi của hàm, hàm chỉ có thể được 
 sử dụng bên trong mô-đun có chứa hàm đó. Khi có từ khoá Private, tên hàm sẽ 
 Trang 80/ 89 
 không được hiển thị trong danh sách hàm của Excel, nhưng người sử dụng vẫn 
 có thể dùng hàm này trong bảng tính một cách bình thường. 
 Chú ý Nếu không khai báo phạm vi cho hàm (từ khoá Public/Private), thì mặc 
định, hàm sẽ có phạm vi là Public. 
 Function: (bắt buộc) là từ khoá báo hiệu bắt đầu một hàm. 
 Tên_hàm: (bắt buộc) là tên của hàm, cách đặt tên hàm tương tự như cách đặt 
 tên của biến. 
 Tên_hàm sẽ được sử dụng như là biến trong toàn bộ hàm, khi hàm kết thúc giá trị 
 trả về của hàm chính là giá trị đã gán cho biến Tên_hàm cuối cùng. 
 Danh_sách_tham_số: (tuỳ chọn) là danh sách các tham số đầu vào của hàm. 
 Các tham số được phân cách với nhau bằng dấu phẩy. 
 Kiểu_dữ_liệu: (tuỳ chọn) quy định kiểu giá trị trả về của hàm. Nếu không quy 
 định 
 kiểu dữ liệu, hàm sẽ có kiểu dữ liệu mặc định là Variant. 
 Exit Function: (tuỳ chọn) là câu lệnh dùng để kết thúc hàm ngay lập tức (cho 
 dù phía sau câu lệnh này vẫn còn các khối lệnh khác). 
 End Function: (bắt buộc) là từ khoá báo hiệu kết thúc một hàm. 
 c. Tạo hàm mới 
 Để tạo một hàm mới, thực hiện theo các bước sau: 
 1. Khởi động VBAIDE. Trong trình đơn Tools, chọn mục Macro/Visual Basic 
Editor; 
 2. Trong trình đơn Insert, chọn mục Module để tạo một mô-đun mới, nơi sẽ 
chứa hàm do người dùng định nghĩa. 
 3. Trong trình đơn Insert, chọn mục Procedure để hiển thị hộp thoại Add 
Procedure. Sau đó điền tên hàm vào mục Name, chọn kiểu chương trình con là 
Function và phạm vi là Public. Cuối cùng chọn OK; 
 Hình 6.6 
 Trang 81/ 89 
4. Chương trình sẽ tự động phát sinh đoạn mã lệnh như sau: 
Public Function Dien_Tich() 
End Function 
5. Thay đoạn mã lệnh trên bằng đoạn mã lệnh thích hợp. 
Public Function Dien_Tich(Rong As Double, Cao As Double) As Double 
‘Ham tinh dien tich hinh chu nhat 
Dien_Tich = Rong * Cao 
End Function 
6. Chọn mục Close and Return to Microsoft Excel để quay trở về màn hình chính 
của Excel. 
7. Lúc này, hàm mà ta vừa xây dựng, có tên là Dien_Tich, đã có thể được sử dụng 
bình 
thường như các hàm khác của Excel. 
 d. Hàm trả về lỗi và cách khắc phục 
 Hàm trả về lỗi: Một số giá trị lỗi cũng như các hằng số tương ứng trong VBA 
 GIÁ TRỊ HẰNG GIẢI THÍCH 
 LỖI SỐ 
 #DIV/0! xlErrDiv0 Công thức có chia một số cho 0. Lỗi này cũng phát sinh 
 khi chia cho một ô trống. 
 #N/A xlErrNA Lỗi này biểu thị dữ liệu không có. 
 #NAME? xlErrName Hàm có tên mà Excel không thể nhận dạng được. Thường 
 xảy ra khi nhập tên hàm sai, hoặc đã thay đổi tên hàm 
 nhưng chưa cập nhật trong bảng tính. 
 #NULL! xlErrNull Giá trị rỗng, chẳng hạn như tìm giao của hai vùng không 
 giao nhau. 
 #NUM! xlErrNum Có vấn đề với giá trị nào đó. Ví dụ như người dùng nhập 
 vào số âm, trong khi chỉ chấp nhận số dương. 
 #REF! xlErrRef Tham chiếu đến ô không tồn tại. Điều này thường xảy ra 
 khi ô đã bị xoá khỏi bảng tính. 
 Trang 82/ 89 
 #VALUE! xlErrValue Hàm có chứa tham số hoặc công thức không phù hợp về 
 kiểu dữ 
 liệu 
 Trong quá trình xây dựng một dự án phần mềm, việc gặp các lỗi là không thể 
tránh khỏi. Vì vậy, việc tìm và xử lý lỗi là điều tất yếu. Trình tự của công việc này như 
sau: 
 1. Tìm và phân loại lỗi. 
 2. Tìm kiếm vị trí mã lệnh phát sinh lỗi. 
 3. Sửa lỗi. 
 4. Ngăn chặn lỗi có thể xảy ra trong tương lai. 
3. Ví dụ đơn giản với VBA 
 Ví dụ này được trình bày với mục đích giúp người dùng làm quen với VBA 
IDE trong Excel. 
 Kết quả của ví dụ là hiển thị nội dung ô A1 trong Sheet1 của bảng tính lên tiêu 
đề của một hộp thoại người dùng (UserForm). 
 Trình tự thực hiện như sau: 
 1. Mở ứng dụng Excel, nhấn tổ hợp phím ALT+F11 để vào VBA IDE. 
 2. Trong VBA IDE, chọn menu Insert / UserForm để thêm một hộp thoại người 
dùng vào 
trong dự án. 
 3. Chọn tiếp menu Insert /Module để thêm một mô-đun chuẩn vào trong dự án. 
 4. Chọn Module1 và soạn thảo mã lệnh trong mô-đun đó như sau: 
Public Sub FirstPro() 
UserForm1.Show 
UserForm1.Caption = Sheets("Sheet1").Range("A1").Value 
End Sub 
 Hình 6.7 
 Trang 83/ 89 
Sau đó quay trở lại Excel, và chạy chương trình theo trình tự: 
1. Gõ vào ô A1 của Sheet1 nội dung “tin hoc ung dung”. 
2. Chọn menu Tools / Macro / Macros (hoặc nhấn tổ hợp phím ALT+ F8). 
3. Trong hộp thoại Macro, chọn macro có tên FirstPro rồi nhấn nút Run. 
 Hình 6.5 
4. Bài tâp ứng dụng 
 Sử dụng công cụ VBA: Dịch số tiền về chữ (Chuyển số thành chữ) 
 Bước 1. Mở tập tin cần chuyển >> Nhấn tổ hợp phím Alt + F11 để mở trình soạn thảo 
 VBA của Excel. 
 Bước 2. Nhấp chuột phải lên VBA Project >> Insert >> Module >> và dán đoạn 
 mã bên dưới vào cửa sổ của Module mới chèn 
 Function ConvertCurrencyToVietnamese(ByVal MyNumber) 
 Dim Temp 
 Dim Dollars, Cents 
 Dim DecimalPlace, Count ReDim Place(9) As 
 String Place(2) = " Nghin " 
 Place(3) = " Trieu " 
 Place(4) = " Ty " 
 Place(5) = " Ngan ty " 
 ' Convert MyNumber to a string, trimming extra 
 spaces. MyNumber = Trim(Str(MyNumber)) 
 ' Find decimal place. 
 DecimalPlace = InStr(MyNumber, ".") 
 ' f we fin ecimal place 
 If DecimalPlace > 0 Then 
 ' Convert cents 
 Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2) 
 Cents = ConvertTens(Temp) 
 ' Strip off cents from remainder to convert. 
 MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) 
 Trang 84/ 89 
End If 
Count = 1 
Do While MyNumber "" 
' Convert last 3 digits of MyNumber to English 
dollars. 
Temp = ConvertHundreds(Right(MyNumber, 3)) 
If Temp "" Then Dollars = Temp & Place(Count) & Dollars 
If Len(MyNumber) > 3 Then 
' Remove last 3 converted digits from MyNumber. 
 MyNumber = Left(MyNumber, Len(MyNumber) - 3) 
 Else 
MyNumber = "" 
End If 
Count = Count + 1 
Loop 
' Clean up dollars. Select Case Dollars Case 
"" 
Dollars = "khong Nghin" 
Case "One" 
Dollars = "Mot Nghin" 
Case Else 
Dollars = Dollars & " Nghin" 
End Select 
' Clean up cents. 
Select Case Cents 
Case "" 
Cents = " va khong Dong" 
Case "One" 
Cents = " va mot Dong" 
Case Else 
Cents = " va " & Cents & " Dong" 
End Select 
ConvertCurrencyToVietnamese = Dollars & Cents 
End Function 
Private Function ConvertHundreds(ByVal MyNumber) 
Dim Result As String 
' Exit if there is nothing to convert. 
If Val(MyNumber) = 0 Then Exit Function 
' Append leading zeros to number. 
MyNumber = Right("000" & MyNumber, 3) 
 Trang 85/ 89 
' Do we have a hundreds place digit to 
convert? 
If Left(MyNumber, 1) "0" Then 
Result = ConvertDigit(Left(MyNumber, 1)) & " Tram " 
End If 
' Do we have a tens place digit to convert? 
If Mid(MyNumber, 2, 1) "0" Then 
Result = Result & ConvertTens(Mid(MyNumber, 2)) 
Else 
' If not, then convert the ones place digit. 
Result = Result & 
ConvertDigit(Mid(MyNumber, 3)) 
End If 
ConvertHundreds = Trim(Result) 
End Function 
Private Function ConvertTens(ByVal 
MyTens) 
Dim Result As String 
' Is value between 10 and 19? 
If Val(Left(MyTens, 1)) = 1 Then 
Select Case Val(MyTens) 
Case 10: Result = "Muoi" 
Case 11: Result = "Muoi mot" 
Case 12: Result = "Muoi hai" 
Case 13: Result = "Muoi ba" 
Case 14: Result = "Muoi bon" 
Case 15: Result = "Muoi lam" 
Case 16: Result = "Moi sau" 
Case 17: Result = "Muoi bay" 
Case 18: Result = "Muoi tam" 
Case 19: Result = "Muoi chin" 
Case Else 
End Select 
Else 
' .. otherwise it’s between 20 an 99. 
Select Case Val(Left(MyTens, 1)) Case 2: Result 
= "Hai muoi " 
Case 3: Result = "Ba muoi " 
Case 4: Result = "Bon muoi " 
Case 5: Result = "Nam muoi " 
Case 6: Result = "Sau muoi " 
Case 7: Result = "Bay muoi " 
 Trang 86/ 89 
 Case 8: Result = "Tam muoi " 
 Case 9: Result = "Chin muoi " 
 Case Else 
 End Select 
 ' Convert ones place digit. 
 Result = Result & ConvertDigit(Right(MyTens, 1)) End If 
 ConvertTens = Result 
 End Function 
 Private Function ConvertDigit(ByVal MyDigit) 
 Select Case Val(MyDigit) 
 Case 1: ConvertDigit = "Mot" 
 Case 2: ConvertDigit = "Hai" 
 Case 3: ConvertDigit = "Ba" 
 Case 4: ConvertDigit = "Bon" 
 Case 5: ConvertDigit = "Nam" 
 Case 6: ConvertDigit = "Sau" 
 Case 7: ConvertDigit = "Bay" 
 Case 8: ConvertDigit = "Tam" 
 Case 9: ConvertDigit = "Chin" 
 Case Else: ConvertDigit = "" End Select 
 End Function 
 Bước 3. Nhấn phím Alt + F11 một lần nữa và nhấn Ctrl + S để save lại toàn bộ 
 tài liệu. 
 Bước 4. Sử dụng công thưc =ConvertCurrencyToVietnamese(B3) để 
 chuyển đổi tiền tệ từ số về chữ (với B3 là số tiền bằng chữ số) 
 Ví dụ: B3 có giá trị là: 123456 thì kết quả =ConvertCurrencyToVietnamese(B3) 
 trả về là Mot Tram Hai muoi Ba Nghin Bon Tram Nam muoi Sau Nghin va 
 khong Dong 
5. Bài tập 
 Bài tập 1: 
 1) Tạo Macro có tên "Dinhdang" ghi lại một hành động Format (màu, loại font, 
cỡ,..) Gán Macro "Dinhdang" lên thanh Toolbar (không yêu cầu dùng VBA), dùng với 
phương pháp "ustomize...". Định dạng một số ô bằng Macro "Dinhdang" 
 2) Đọc hiểu Macro Mở xem nội dung của Macro "Dinhdang" (ALT+F11) Giải 
thích từng dòng lệnh. Bản chất của một Macro là một Sub (thủ tục)? 
 3) Có mấy cách để chạy được một Macro? Nội dung của các cách? 
 4) Tạo một Macro định dạng bảng, giải thích từng dòng lệnh của Macro đó. 
 Trang 87/ 89 
 5) Tạo một Macro để dán giá trị (Paste Value) 
 Ứng dụng: Khi cần copy giá trị của một công thức, bạn chọn lệnh Copy sau đó 
đặt con trỏ vào địa chỉ cần dán dữ liệu và chạy Macro trên. 
 6) Tạo một Macro định dạng số tiền về dạng "#,##0" 
 7) Tạo một Macro định dạng ngày tháng về dạng "dd/MM/yy" 
 8) Tạo một Macro dán định dạng (Paste Formats) 
 9) Tạo một Macro để xoay chiều giá trị 
 Ứng dụng: Khi cần xoay các giá trị nằm theo hàng thành theo cột. 
 10) Tạo một thanh công cụ (Toolbar) có tên "Các lệnh của tôi". Trên thanh 
toolbar gán tất cả các Macro đã tạo từ câu 1-9 đặt tên rõ ràng theo nội dung công việc. 
 Bài tập 2: 
 1) Viết hàmMax2So nhận đầu vào là 2 giá trị số thực, giá trị trả về của hàm trả về 
là giá trị lớn nhất trong 2 đối số. 
 2) Viết hàmMax3So nhận đầu vào là 3 giá trị số thực, giá trị trả về của hàm trả về 
là giá trị lớn nhất trong 3 đối số. 
 Trang 88/ 89 
 TÀI LIỆU THAM KHẢO 
1. Nguyễn Quỳnh, Giáo trình Microsoft Excel nâng cao,  
2. Đinh Thế Hiển, Excel Ứng Dụng - Phân Tích Hoạt Động Kinh Doanh & Tài Chính 
 Kế Toán, NXB Thống kê, 2007; 
3. Nguyễn Ngọc Hiển, Dẫn Thực Hành Sổ Sách Kế Toán Lập Báo Cáo Tài Chính Và 
 Báo Cáo Thuế GTGT Trên Excel, NXB Tài chính, 2007. 
4. Cao Bá Thành, Giáo trình Excel & Ứng dụng Kế toán, NXB Thanh niên; 
5. Giáo trình Excel 2010, Microsoft Office-Việt Nam 
 Trang 89/ 89