Tài liệu Lập trình cơ bản VBA trong Excel

Chia sẻ: Pham Tuan Minh | Ngày: | Loại File: PDF | Số trang:0

Thêm vào BST

Báo xấu

679
lượt xem 194
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tài liệu Lập trình cơ bản VBA trong Excel giới thiệu đến các bạn nội dung như: Tạo Macro, MsgBox, Workbooks và Worksheets, đối tượng Range, biến, câu lệnh If Then, vòng lặp,... Mời các bạn cùng tìm hiểu.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Tài liệu Lập trình cơ bản VBA trong Excel

8/18/2016 VBA TRONG EXCEL Lập trình cơ bản trong excel PHẠM TUẤN MINH HTTP://DEV4LIFES.NET
1 LỜI NÓI ĐẦU Lập trình đang ngày càng chiếm vị trí quan trọng trong công việc hằng ngày. Đặc biệt với những khối ngành kinh tế và kỹ thuật. Nó giúp công việc được đơn giản và nhẹ nhàng hơn, những công việc lặp đi lặp lại, những công việc nhàm chán cần được tự động hóa để tránh sai sót và giảm thiểu rủi ro. Excel là một công cụ tính toán hết sức mạnh mẽ của Microsoft, những hàm sẵn có trong Excel rất lớn, rất nhiều có lẽ đủ để giúp chúng ta xử lý các bài toán kinh tế, kỹ thuật tuy nhiên thay vì viết 10 dòng code thì có lẽ chỉ cần viết 1 dòng code thôi, đó chẳng phải tuyệt vời hơn sao? VBA trong Excel giúp chúng ta làm việc đó, với các hàm API sẵn có cộng với các hàm API mà các chương trình khác có thể tích hợp vào Excel như phần mềm Etabs, Robot Structural Analyis trong xây dựng…giúp Excel chuyên nghiệp và phổ biến. Cách sử dụng VBA trong Excel tương đối đơn giản, nếu bạn đã từng có tư duy về lập trình thì càng dễ dàng hơn. VBA có thể sử dụng mô hình hướng đối tượng hiện đại giúp dòng code sạch sẽ và dễ hiểu. Trong khuôn khổ ebook này, tác giả chỉ mang đến nhiều điều đơn giản nhất và VBA trong Excel nhằm giúp người đọc có cách nhìn dê dàng và bao quát nhất. Để hỏi – đáp về các vấn đề cụ thể trong VBA mời bạn sử dụng các hình thức sau: - Gửi email về địa chỉ: dev4contact@gmail.com - Đặt câu hỏi trên trang web: http://askme.dev4lifes.net Chân thành cảm ơn! VBA TRONG EXCEL | Lập trình cơ bản trong excel
2 MỤC LỤC 1 Tạo Macro ..................................................................................................................... 5 1.1 Thẻ Developer ........................................................................................................ 5 1.2 Command Button ................................................................................................... 6 1.3 Gán một Macro ...................................................................................................... 7 1.4 Visual Basic Editor ................................................................................................ 9 2 MsgBox ....................................................................................................................... 10 3 Workbooks và Worksheets ......................................................................................... 12 3.1 Cây đối tượng ....................................................................................................... 12 3.2 Collection ............................................................................................................. 12 3.3 Properties và Method ........................................................................................... 13 4 Đối tượng Range ......................................................................................................... 15 4.1 Ví dụ về Range ..................................................................................................... 15 4.2 Cell ....................................................................................................................... 16 4.3 Khai báo đối tượng Range ................................................................................... 17 4.4 Select .................................................................................................................... 17 4.5 Rows..................................................................................................................... 17 4.6 Columns ............................................................................................................... 18 4.7 Copy/Paste ........................................................................................................... 18 4.8 Clear ..................................................................................................................... 19 4.9 Count .................................................................................................................... 19 5 Biến ............................................................................................................................. 21 5.1 Integer .................................................................................................................. 21 5.2 String .................................................................................................................... 21 VBA TRONG EXCEL | Lập trình cơ bản trong excel
3 5.3 Double .................................................................................................................. 22 5.4 Boolean ................................................................................................................ 23 6 Câu lệnh If Then ......................................................................................................... 23 6.1 Else ....................................................................................................................... 24 7 Vòng lặp ...................................................................................................................... 25 7.1 Vòng lặp đơn ........................................................................................................ 25 7.2 Vòng lặp đôi ......................................................................................................... 25 7.3 Vòng lặp ba .......................................................................................................... 26 7.4 Vòng lặp Do While .............................................................................................. 27 8 Lỗi Macro ................................................................................................................... 28 9 Xử lý String................................................................................................................. 31 9.1 Liên kết String ...................................................................................................... 31 9.2 Left ....................................................................................................................... 32 9.3 Right ..................................................................................................................... 32 9.4 Mid ....................................................................................................................... 33 9.5 Len ....................................................................................................................... 33 9.6 Instr ...................................................................................................................... 34 10 Date và Time ........................................................................................................... 34 10.1 Year, Month, Day của Date .............................................................................. 34 10.2 DateAdd ............................................................................................................ 35 10.3 Date và Time hiện tại........................................................................................ 35 10.4 Giờ, phút, giây (Hour, Minute, Second) ........................................................... 36 10.5 TimeValue ........................................................................................................ 36 11 Event (Sự kiện) ........................................................................................................ 37 VBA TRONG EXCEL | Lập trình cơ bản trong excel
4 11.1 Sự kiện Workbook Open .................................................................................. 37 11.2 Sự kiện Worksheet Change .............................................................................. 38 12 Mảng ........................................................................................................................ 40 12.1 Mảng một chiều ................................................................................................ 40 12.2 Mảng hai chiều ................................................................................................. 41 13 Function và Sub ....................................................................................................... 42 13.1 Function ............................................................................................................ 42 13.2 Sub .................................................................................................................... 43 14 Đối tượng Application ............................................................................................. 43 14.1 WorksheetFunction ........................................................................................... 43 14.2 ScreenUpdating ................................................................................................ 44 14.3 DisplayAlerts .................................................................................................... 45 14.4 Calculation ........................................................................................................ 46 15 ActiveX Control ...................................................................................................... 47 16 Userform .................................................................................................................. 49 16.1 Thêm Control .................................................................................................... 50 16.2 Hiển thị Userform ............................................................................................. 52 16.3 Gán Macro ........................................................................................................ 54 16.4 Kiểm tra ............................................................................................................ 55 VBA TRONG EXCEL | Lập trình cơ bản trong excel
5 1 Tạo Macro Với Excel VBA bạn có thể tự động hóa các công việc trong Excel bằng cách viết một thứ gọi là macro. Trong bài này sẽ học cách để tạo một macro đơn giản để thực hiện một chức năng nào đó sau khi kích một một nút (button). Đầu tiên ta phải bật thẻ menu Developer. 1.1 Thẻ Developer Thẻ này dành riêng cho các bạn muốn lập trình trong Excel, còn nếu không thì cũng không cần quan tâm đến nó làm gì cả. 1. Để bật thẻ này, thực hiện các bước sauKích chuột phải vào bất kỳ đâu trên thanh ribbon, sau đó kích vào Customize the Ribbon 2. Tích chọn vào ô Developer VBA TRONG EXCEL | Lập trình cơ bản trong excel
6 3. Kích OK 4. Bạn sẽ thấy tab developer gần tab view 1.2 Command Button Để đặt một nút trên bảng Excel (WorkSheet) của bạn, thực hiện các bước sau: 1. Trên Tab Developer, kích vào nút Insert VBA TRONG EXCEL | Lập trình cơ bản trong excel
7 2. Trong khu vực ActiveX, kích vào Command Button 3. Kéo và thả nút đó vào worksheet của bạn 1.3 Gán một Macro Để gán một Macro cho nút trên, thực hiện các bước sau 1. Kích chuột phải vào CommandButton1 ( chắc chắn Design Mode đã chọn) 2. Kích vào View Code VBA TRONG EXCEL | Lập trình cơ bản trong excel
8 Trình duyệt Visual Basic Editor xuất hiện. 3. Đặt con trỏ chuột ở giữa dòng Private Sub CommandButton1_Click() và End Sub 4. Thêm dòng code sau vào đó VBA TRONG EXCEL | Lập trình cơ bản trong excel
9 Chú ý: Cửa sổ bên trái với tên là Sheet1, Sheet2 và Sheet3 được gọi là Project Explorer. Nếu cửa sổ này không hiện ra, các bạn chọn View > Project Explorer. Để thêm cửa sổ code sheet đầu tiên, kích vào Sheet1. 5. Đóng khung Visual Basic Editor 6. Kích vào command button trên sheet của bạn. Sẽ thấy kết quả như sau: Chúc mừng bạn đã tạo macro trong Excel. Bạn đừng quan tâm tới các dòng code vội, vì tôi sẽ nói trong các bài sau. 1.4 Visual Basic Editor Để mở Visual Basic Editor, trên thẻ Developer, kích vào Visual Basic VBA TRONG EXCEL | Lập trình cơ bản trong excel
10 Cửa sổ Visual Basic Editor xuất hiện: 2 MsgBox MsgBox là một bảng thông báo trong Excel VBA, bạn có thể sử dụng thể thông tin cho người dùng. Đặt một command button trong bảng tính và thêm những dòng code sau: 1. Tin nhắn đơn 1 MsgBox "This is fun" Kết quả khi bạn click vào nút này như sau: VBA TRONG EXCEL | Lập trình cơ bản trong excel
11 2. Tin nhắn nâng cao hơn một chút 1 MsgBox "Entered value is " & Range("A1").Value Nhập một giá trị nào đó vào trong ô A1 và ấn vào nút thì kết quả như sau: Chú ý: Chúng ta sử dụng toán tử & để nối chuỗi string. 3. Để bắt đầu một dòng mới, sử dụng vbNewLine 1 MsgBox "Line 1" & vbNewLine & "Line 2" Kết quả như sau: VBA TRONG EXCEL | Lập trình cơ bản trong excel
12 3 Workbooks và Worksheets 3.1 Cây đối tượng Trong Excel VBA, một đối tượng có thể bao gồm đối tượng khác, và đối tượng đó có thể bao gồm đối tượng khác, v.v.Vì vậy, lập trình Excel VBA sẽ liên quan đến việc làm việc với các đối tượng . Điều này nghe có vẻ rắc rối, nhưng chúng ta sẽ sớm hiểu rõ thôi. Đối tượng cha của tất cả đối tượng đó chính là bản thân Excel. Chúng ta có thể gọi nó là đối tượng Application. Đối tượng Application bao gồm những đối tượng khác . Ví dụ, đối tượng Workbook (file Excel), có thể là bất kỳ workbook nào bạn đã tao. Đối tượng Workbook bao gồm những đối tượng khác giống như đối tượng Worksheet. Cứ như vậy đối tượng Worksheet bao gồm đối tượng khác, như đối tượng Range. Bài tạo Marco đã minh họa cách để chạy code bằng cách kích vào một button. Chúng ta đã sử dụng đoạn code sau: 1 Range("A1").Value = "Hello" nhưng thực chất là như sau: 1 Application.Workbooks("create-a-macro").Worksheets(1).Range("A1").Value = "Hello" Chú ý: Đối tượng được gọi thông qua dấu chấm “.”. May mắn là chúng ta không phải sử dụng cả dòng code dài như bên trên, bởi vì chúng ta đặt button của chúng ta trong file create-a-marco.xls, ở trên worksheet đầu tiên. 3.2 Collection Bạn chú ý là cả Workbooks và Worksheet đều ở dạng số nhiều . Đó là bởi vì chúng là một tập hợp (collection). Tập hợp Workbooks bao gồm tất cả các đối tượng workbook mà đang mở . Tập hợp Worksheets bao gồm tất cả các đối tượng Worksheet trong một workbook. VBA TRONG EXCEL | Lập trình cơ bản trong excel
13 Bạn có thể trỏ đến một đối tượng trong một tập hợp, ví dụ một đối tượng worksheet theo 3 cách: 1. Sử dụng tên worksheet 1 Worksheets("Sales").Range("A1").Value = "Hello" 2. Sử dụng chỉ sổ (1 là worksheet đầu tiên bắt đầu từ bên trái) 1 Worksheets(1).Range("A1").Value = "Hello" 3. Sử dụng CodeName 1 Sheet1.Range("A1").Value = "Hello" để xem CodeName của worksheet, mở Visual Basic Editor. Trong Project Explorer, tên đầu tiên là CodeName. Tên thứ hai là tên worksheet (Sales) Chú ý: CodeName giống nhau nếu bạn thay đổi tên worksheet vì thế mà đây là cách an toàn nhất để trỏ đến worksheet. 3.3 Properties và Method Bây giờ chúng ta quan sát một số property và method của tập hợp workbook và worksheet. Property là những đặc trưng cho tập hợp, còn method được dùng để làm điều gì đó (ví dụ con chó có lông màu đen (đó là property), còn sủa hoặc cắn là method). Đặt một command button trên bảng tính và thêm những dòng code sau: VBA TRONG EXCEL | Lập trình cơ bản trong excel
14 1. Method “Add” của tập hợp workbook tạo một workbook mới: 1 Workbooks.Add Chú ý: Method Add của worksheet sẽ tạo worksheet mới 2. Property “Count” của tập hợp Worksheet đếm số lượng worksheets trong một workbook 1 MsgBox Worksheets.Count Kết quả khi bạn kích vào button như sau: Chú ý: Property “Count” của workbook sẽ đếm số workbook đang active. VBA TRONG EXCEL | Lập trình cơ bản trong excel
15 4 Đối tượng Range Đối tượng Range dùng để “minh họa” cho một ô hoặc nhiều ô (cell) trong worksheet của bạn, đây là đối tượng quan trọng nhất của Excel VBA. Bài này sẽ trình bày tổng quan về property (thuộc tính) và method(phương thức) của đối tượng này . 4.1 Ví dụ về Range Đặt một command button trong worksheet của bạn và thêm những dòng code sau: 1 Range("B3").Value = 2 Kết quả khi bạn kích vào button như sau: Code: 1 Range("A1:A4").Value = 5 Kết quả: Code: 1 Range("A1:A2,B3:C4").Value = 10 Kết quả: VBA TRONG EXCEL | Lập trình cơ bản trong excel
16 4.2 Cell Thay vì sử dụng Range, bạn có thể sử dụng Cells. Sử dụng Cells hữu ích khi bạn muốn duyệt qua một chuỗi các ô (range) Code: 1 Cells(3, 2).Value = 2 Kết quả: Giải thích: Excel VBA nhập giá trị 2 vào trong ô là giao của dòng 3 và cột 2 Code: 1 Range(Cells(1, 1), Cells(4, 1)).Value = 5 Kết quả: VBA TRONG EXCEL | Lập trình cơ bản trong excel
17 4.3 Khai báo đối tượng Range Bạn có thể khai báo một đối tượng Range sử dụng từ khóa Dim và Set Code: 1 Dim example As Range 2 Set example = Range("A1:C4") 3 4 example.Value = 8 Kết quả: 4.4 Select Một phương thức quan trọng của đối tượng Range là phương thức Select. Phương thức này làm nhiệm vụ chọn một chuỗi các ô. Code: 1 Dim example As Range 2 Set example = Range("A1:C4") 3 4 example.Select Kết quả: 4.5 Rows VBA TRONG EXCEL | Lập trình cơ bản trong excel
18 Thuộc tính Rows để bạn truy xuất vào một dòng cụ thể của chuỗi range Code: 1 Dim example As Range 2 Set example = Range("A1:C4") 3 4 example.Rows(3).Select Kết quả: Chú ý: Đường border chỉ có tính minh họa 4.6 Columns Thuộc tính column để bạn truy xuất đến một cột cụ thể . Code: 1 Dim example As Range 2 Set example = Range("A1:C4") 3 4 example.Columns(2).Select Kết quả: 4.7 Copy/Paste VBA TRONG EXCEL | Lập trình cơ bản trong excel
19 Phương thức copy/paste được sử dụng để sao chép một chuỗi và dán nó vào một nơi nào đó trên worksheet Code: 1 Range("A1:A2").Select 2 Selection.Copy 3 4 Range("C3").Select 5 ActiveSheet.Paste Kết quả: Mặc dù điều này được cho phép trong Excel VBA, nhưng tốt hơn là sử dụng code bên dưới với cùng mục đích: 1 Range("C3:C4").Value = Range("A1:A2").Value 4.8 Clear Để xóa bỏ nội dung của chuỗi các ô, bạn có thể sử dụng phương thức ClearContents 1 Range("A1").ClearContents hoặc đơn giản hơn: 1 Range("A1").Value = "" Chú ý: Sử dụng phương thức Clear sẽ xóa bỏ cả nội dung và định dạng của chuỗi . Sử dụng phương thức ClearFormats sẽ chỉ xỏa bỏ định dạng . 4.9 Count Với thuộc tính count, bạn có thể đếm số ô, số dòng, số cột của chuỗi VBA TRONG EXCEL | Lập trình cơ bản trong excel