Microsoft excel là một công cụ mạnh dùng
trong việc phân tích & trình bày các thông tin. Thế mạnh của bảng tính
excel, ngoài các công thức sẵn có, còn là macro của nó.
Kể từ khi xuất hiện version 5.0 đến nay, ngôn ngữ dùng trong excel là Visual Basic for Applications (VBA). Đó là ngôn ngữ lập trình dùng chung cho nhiều phần mềm trong windows.
Quyễn ghi chép này sẽ nhằm giới thiệu cách thức làm quen với macro, và cách thức tuần tự chinh phục ngôn ngữ VBA trong excel.
Bài 1: Những macro đơn giản nhất
Để xem Video hướng dẫn chi tiết bạn có thể tham gia khóa học VBA Excel 2010 bằng cách nhấn vào link sau:
1. Bộ thu macro
Ngôn ngữ macro là một ngôn ngữ lập trình thật sự. Nhưng mặt trái của sự mạnh mẽ này là làm cho ta không dễ đọc & hiểu ngay được.
Để bước đầu làm quen với nó ta nhờ bộ thu macro, bộ thu này luôn có sẵn trong excel. Nó giúp bạn tạo ra những tác phẩm đầu tay và rất thực tế. Để rồi sau đó ta sẽ tạo ra những macro phức tạp, thực hiện những công việc theo sở thích của bạn!
2. Tạo macro đầu tay
Khi thực hành với excel, chúng ta đôi lúc cũng phải định dạng dẫy ô nào đó. Muốn dẫy biểu thị dạng tiền VNĐ không có số lẽ, ta chọn dãy ô; vô menu Format -> Cells; ->Number ta chọn Currency trong hộp thoại Category; trong hộp Decimal places ta chọn giá trị 0; trong hộp Symbol ta chọn None; cuối cùng trong hộp Negative numbers ta chọn hàng thứ ba (đang là (1.2340)) và nhấp vô nút OK để trở về trang tính
Excel mặc định dạng thức tiền có hai số lẽ. Trong thực tế nhiều khi không cần nhiều số lẽ đến vậy!. Chúng ta tạo một macro để định dạng ô theo kiểu tiền không có số lẽ.
a./ Chuẩn bị:
Ta quét chọn các ô từ A2 đến A9; bấn chuột lên thanh công thức và nhập các hàm sau:
= INT(9 * RAND()) ^ 8 và kết thúc bằng tổ hợp hai fím CTRL+ENTER
b./ Tạo macro
Vô menu Tool -> Macro ->Record New Macro. . .; cửa sổ New Macro xuất hiên; Trong hộp Macro Name ta nhập FormatCurrency và nhấp nút OK.
Trên màn hình sẽ hiện thêm thành phần thanh Toolbar của macro. Chúng ta bắt đầu thực hiện việc ghi vô bộ thu macro:
(Với các ô A2:A9 đã chọn), vô menu Format -> Cells. . . và chọn ngăn Number; Tiếp theo chọn Currency trong hộp thoại Category; trong hộp Decimal places ta chọn giá trị 0; trong hộp Symbol ta chọn None; cuối cùng trong hộp Negative numbers ta chọn hàng thứ ba (đang là (1.2340)) và nhấp vô nút OK để trở về trang tính.
Kết thúc ghi macro bằng cách nhấp vô nút close trên thanh Toolbar của macro vừa xuất hiện
Chú ý: Một số thao tác có thể có nhiều cách để đạt được; thí dụ tổ hợp phím CTRL +1 sẽ nhanh chóng đưa ta đến hộp thoại Format Cells; Ta cũng có thể kết thúc ghi macro khi chọn trên menu Tool -> macro -> Stop Recording; Trong phần ghi chép này chỉ ghi một cách thức (còn các cách khác ta tự áp dụng)
Tác phẩm đầu tay đã xong; Chúng ta nóng ruột muốn biết đứa con tinh thần nó hình hài ra sao & sẽ chạy nhảy như thế nào!
Để xem hình hài ta vô menu Tool -> Macro; trong danh sách liệt kê nó là tên duy nhất mà ta đã ấn định: FormatCurrency; Nội dung nó như sau:
Ngôn ngữ macro là một ngôn ngữ lập trình thật sự. Nhưng mặt trái của sự mạnh mẽ này là làm cho ta không dễ đọc & hiểu ngay được.
Để bước đầu làm quen với nó ta nhờ bộ thu macro, bộ thu này luôn có sẵn trong excel. Nó giúp bạn tạo ra những tác phẩm đầu tay và rất thực tế. Để rồi sau đó ta sẽ tạo ra những macro phức tạp, thực hiện những công việc theo sở thích của bạn!
2. Tạo macro đầu tay
Khi thực hành với excel, chúng ta đôi lúc cũng phải định dạng dẫy ô nào đó. Muốn dẫy biểu thị dạng tiền VNĐ không có số lẽ, ta chọn dãy ô; vô menu Format -> Cells; ->Number ta chọn Currency trong hộp thoại Category; trong hộp Decimal places ta chọn giá trị 0; trong hộp Symbol ta chọn None; cuối cùng trong hộp Negative numbers ta chọn hàng thứ ba (đang là (1.2340)) và nhấp vô nút OK để trở về trang tính
Excel mặc định dạng thức tiền có hai số lẽ. Trong thực tế nhiều khi không cần nhiều số lẽ đến vậy!. Chúng ta tạo một macro để định dạng ô theo kiểu tiền không có số lẽ.
a./ Chuẩn bị:
Ta quét chọn các ô từ A2 đến A9; bấn chuột lên thanh công thức và nhập các hàm sau:
= INT(9 * RAND()) ^ 8 và kết thúc bằng tổ hợp hai fím CTRL+ENTER
b./ Tạo macro
Vô menu Tool -> Macro ->Record New Macro. . .; cửa sổ New Macro xuất hiên; Trong hộp Macro Name ta nhập FormatCurrency và nhấp nút OK.
Trên màn hình sẽ hiện thêm thành phần thanh Toolbar của macro. Chúng ta bắt đầu thực hiện việc ghi vô bộ thu macro:
(Với các ô A2:A9 đã chọn), vô menu Format -> Cells. . . và chọn ngăn Number; Tiếp theo chọn Currency trong hộp thoại Category; trong hộp Decimal places ta chọn giá trị 0; trong hộp Symbol ta chọn None; cuối cùng trong hộp Negative numbers ta chọn hàng thứ ba (đang là (1.2340)) và nhấp vô nút OK để trở về trang tính.
Kết thúc ghi macro bằng cách nhấp vô nút close trên thanh Toolbar của macro vừa xuất hiện
Chú ý: Một số thao tác có thể có nhiều cách để đạt được; thí dụ tổ hợp phím CTRL +1 sẽ nhanh chóng đưa ta đến hộp thoại Format Cells; Ta cũng có thể kết thúc ghi macro khi chọn trên menu Tool -> macro -> Stop Recording; Trong phần ghi chép này chỉ ghi một cách thức (còn các cách khác ta tự áp dụng)
Tác phẩm đầu tay đã xong; Chúng ta nóng ruột muốn biết đứa con tinh thần nó hình hài ra sao & sẽ chạy nhảy như thế nào!
Để xem hình hài ta vô menu Tool -> Macro; trong danh sách liệt kê nó là tên duy nhất mà ta đã ấn định: FormatCurrency; Nội dung nó như sau:
Code:
Sub FormatCurrency()
' FormatCurrency Macro
' Macro recorded 10/10/2006 by
Quai Dang
'
Selection.NumberFormat =
"#,##0_);(#,##0)"
End Sub
Thực ra chỉ có ba dòng lệnh trong macro là quan trọng thôi; các dòng có dấu nháy đầu dòng là những ghi chú, nhắc chúng ta fút nó chào đời như: tên, tuổi , fím nóng để vận hành . . .
c./ Chạy macro
Để biết nó đi đứng ra sao ta quét chọn vài ô dữ liệu số trên trang tính; vô menu Tool -> Macro -> Macros; chọn tên macro và ấn nút Run
d./ Gán tổ hợp phím tắt cho macro
Vô menu Tool -> Macro -> Macros; cửa sổ Macro xuất hiện trở lại; Phía phải của cửa sô (CS), ngoài nút Run mới quen, còn có các nút khác mà ta sẽ dần làm quen. Và nút kế tiếp ta làm quen là Option; Ta nhấp vô nó để hiện lên CS Macro Option; Ta bấm chuột vô ô Shortcut key; ấn giữ phím Shift và nhấn tiếp phím 'F'. (Như vậy là ta gán cho macro FormatCurrency tổ hợp 3 fím CTRL+SHIFT+F
Chú ý: Tất nhiên ta cũng có thể không giữ fím Shift; nhưng việc đó lợi bất cập hại vì dễ gây xung đột!
Chú ý: Ta cũng có thể gán tổ hợp phím nóng ngay từ đầu, sau khi đặt tên: Khi đó ta cũng đã thấy CS Shortcut key trong hộp thoại Record Macro
3. Macro thay đổi nhiều thuộc tính
a./ Chuẩn bị:
Nhập vô ô B2 chuỗi sau: 'Tổng hợp số liệu tháng 10/06'. Chọn ô B2 này;
b./ Thu macro:
Vô menu Tool -> Macro ->Macros . . . Trong CS Macro name nhập tên: 'DinhDangTieuDe'; Trong CS Shortcut key ta giữ phím Shift & nhấn phím 'D' và nhấn nút OK để ghi macro mới..
Khi ô B2 vẫn được chọn, vô menu Format -> Cells. . .Tiếp theo vô ngăn Aligment; Trong CS Text aligment ta chọn tại cả hai CS Horizontal & Vertical đều là Center; Sau đó ấn nút OK để trở về trang tính; Ta quét chọn các ô từ B2 đến F2; Sau đó nhấp vô nút Merge and Center để tiêu đề canh giữa của các ô liệt kê. Kết thúc việc ghi macro DinhDangTieuDe bằng 1 trong các cách nêu trên.
Để xem nội dung macro ta nhấn đồng thời hai phím ALT + F6 để mở CS Macro và chọn tên DinhDangTieuDe; xong rồi ấn chọn nút Edit để hiện màn hình Microsoft Visual Basic có chứa đoạn code sau:
Code:
Sub
DinhDangTieuDe()
'
'
DinhDangTieuDe Macro
' Macro recorded 10/10/2006 by
Quang Duc
' Keyboard Shortcut: Ctrl+Shift+D
Range("B2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
End Sub
Cái macro trước thực tế chỉ có một dòng lệnh; nhưng macro này rất nhiều dòng lệnh.
c./ Bước đầu tác động lên macro:
Trước tiên , ta biết các dòng có dấu nháy đơn đầu dòng lá những dòng chú thích; ta bỏ hẵn 5 dòng. (Chỉ để dòng ' Keyboard Shortcut: Ctrl+Shift+D). Nhưng cắt nó dán sau 2 dấu ngoặc đơn; của dòng Sub DinhDangTieuDe()
Ta vô menu Debug và chọn thanh Compile VBAProject; Nếu chúng ta không thấy CS thông báo lỗi nào thì ta biết rằng VBA chấp nhận những thay đổi của chúng ta (chứng tỏ không có lỗi biên dịch.)
Hơn nữa ta lại biết thêm rằng, các chú thích có thể ngay sau câu lệnh, miễn trước nó có dấu nháy đơn;
Tiếp theo ta thực hiện đánh số vô các dòng lệnh và sau đó mạnh dạn bỏ các dòng lệnh từ 5 đến 11. Ta lại thực hiện kiểm lỗi biên dịch nhờ menu Debug như nêu trên; Nếu không được báo lỗi nào, ta yên tâm bấm nút lưu lại
Tiếp một bước xa hơn, ta đặt dấu nháy trước các số của các dòng lệnh từ 14 đến 23. Tức là ta vô hiệu các dòng lệnh này một cách tạm thời;
Thực hiện phép thử macro lần cuối, như sau:
Xoá nguyên dòng 2 chứa định dạng mà macro đã thực hiện; Nhập dòng khác thay thế (VD: 'BÁO CÁO TÌNH HÌNH THÁNG TRƯỚC.' và thử chạy macro DinhDanhTieuDe; Nếu nó vẫn làm việc theo đúng như lúc chưa sửa gì là ô kê!
Code:
Sub DinhDangTieuDe() ' Keyboard Shortcut: Ctrl+Shift+D
1 Range("B2").Select
2 With Selection
3 .HorizontalAlignment = xlCenter
4 .VerticalAlignment = xlCenter
12 End With
13 Range("B2:F2").Select
14 With Selection
15 .HorizontalAlignment = xlCenter
16 .WrapText = False
17 .Orientation = 0
18 .AddIndent = False
19 .IndentLevel = 0
20 .ShrinkToFit = False
21 .ReadingOrder = xlContext
22 .MergeCells = False
23 End With
24 Selection.Merge
End Sub
Như vậy, macro của chúng ta chỉ còn là:
Code:
Sub DinhDangTieuDe() ' Keyboard
Shortcut: Ctrl+Shift+D
Range("B2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("B2:F2").Select
Selection.Merge
End Sub
d./ Đọc & diễn dịch nội dung các câu lệnh:
Macro được bắt đầu bằng từ Sub (tạm hiểu là từ viết tắt của Subroutine); & dòng cuối của macro là End Sub;
Macro luôn có tên, (VD ta đã có DinhDangTieuDe & FormatCurrency); Hơn nữa tên phải tuân thủ một quy luật nghiêm:
Không gồm các khoảng trắng, không là các ký số ở đầu tên.. . ( việc này VBA sẽ truyền đạt cho bạn rõ, một khi bạn không tuân thủ!)
Macro DinhDangTieuDe thực ra làm hai công việc:
- Chọn ô B2 & định dạng canh giữa theo chiều ngang & chiều dọc;
- Canh giữa đoạn văn bản trên toàn bộ các ô "B2:F2"
Vì một lý do nào đó, chúng ta có thể viết hai dòng lệnh trên một hàng, nhưng phải cách nhau bằng dấu hai chấm ':'
Range("B2:F2").Select: Selection.Merge
Ngược lại, với câu lệnh quá dài, chúng ta có thể bố trí trên nhiều dòng; lúc này dấu ngang dưới '_' được nhập cuối dòng trên để báo cho VBA biết rằng dòng lệnh còn tiếp theo ở dòng dưới. Ta xem xét đến ví dụ macro dùng để xếp danh sách theo tên ("C1"), như sau
Macro được bắt đầu bằng từ Sub (tạm hiểu là từ viết tắt của Subroutine); & dòng cuối của macro là End Sub;
Macro luôn có tên, (VD ta đã có DinhDangTieuDe & FormatCurrency); Hơn nữa tên phải tuân thủ một quy luật nghiêm:
Không gồm các khoảng trắng, không là các ký số ở đầu tên.. . ( việc này VBA sẽ truyền đạt cho bạn rõ, một khi bạn không tuân thủ!)
Macro DinhDangTieuDe thực ra làm hai công việc:
- Chọn ô B2 & định dạng canh giữa theo chiều ngang & chiều dọc;
- Canh giữa đoạn văn bản trên toàn bộ các ô "B2:F2"
Vì một lý do nào đó, chúng ta có thể viết hai dòng lệnh trên một hàng, nhưng phải cách nhau bằng dấu hai chấm ':'
Range("B2:F2").Select: Selection.Merge
Ngược lại, với câu lệnh quá dài, chúng ta có thể bố trí trên nhiều dòng; lúc này dấu ngang dưới '_' được nhập cuối dòng trên để báo cho VBA biết rằng dòng lệnh còn tiếp theo ở dòng dưới. Ta xem xét đến ví dụ macro dùng để xếp danh sách theo tên ("C1"), như sau
Code:
Sub SortByName()
Columns("A:C").Select
Selection.Sort Key1:=Range("C1"),
Order1:=xlAscending, Key2:=Range("A1") _
, Order2:=xlAscending,
Key3:=Range("B1"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub
Câu lệnh này được hiểu như sau:
Tại ba cột A-C đã chọn (ở câu lệnh trước đó), hãy xếp theo hướng tăng dần giá trị tại cột C;
Tiếp theo là xếp theo thứ tự tăng dần các giá trị tại cột A;
Và cuối cùng là xếp theo cột B cũng theo chiều tăng dần ;
Bài tập của bài 1:
+ Tạo macro để xoá thanh trang bảng tính (sheet tab): Thường thanh này nằm trên thanh cuộn ngang, Ta có thể vô menu Tool-> Option -> View rồi bỏ chọn hộp Sheet Tabs
+ Chuyển công thức thành giá trị: Tại trang tính để chuẩn bị ghi macro đầu tiên ta có công thức = INT(9 * RAND()) ^ 8 tại các ô cột A; Bạn thử lập macro chuyển các ô từ A2 đến A9 này thành giá trị!
+ Tại cột D, bắt đầu từ D1, D2 ta nhập tương ứng 1 & 2
Tại cột E kế bên ta nhập 1 & 4; Tại cột F ta nhập F1 là 36; F2 là 100
Hãy tạo macro để thực hiên các hành động sau: Chọn vùng từ D1:F2; vô menu Edit -> Copy; Sau đó chọn ô D4 và cũng vô menu Edit -> Paste Special; tại CS Paste Special ta đánh dấu hộp kiểm Transpose; sau khi dán xong ta nhấn phím ESC để bỏ vùng chọn.
+ Tại trang bảng tính của VD 3; thiết lập macro ghi lại những hành động sau:
Chọn ô A1, vô menu Edit -> Go to; Trong hộp thoại
Để xem Video hướng dẫn chi tiết bạn có thể tham gia khóa học VBA Excel 2010 bằng cách nhấn vào link sau:
