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

8/18/2016

VBA TRONG EXCEL Lập trình cơ bản trong excel

PHẠM TUẤN MINH HTTP://DEV4LIFES.NET

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Chân thành cảm ơn!

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

5.2 String .................................................................................................................... 21

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

11 Event (Sự kiện) ........................................................................................................ 37

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

16.4 Kiểm tra ............................................................................................................ 55

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

2. Tích chọn vào ô Developer

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:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

1. Trên Tab Developer, kích vào nút Insert

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)

VBA TRONG EXCEL | Lập trình cơ bản trong excel

2. Kích vào View Code

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

4. Thêm dòng code sau vào đó

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Để mở Visual Basic Editor, trên thẻ Developer, kích vào Visual Basic

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 MsgBox "This is fun"

1. Tin nhắn đơn

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Kết quả khi bạn click vào nút này như sau:

1 MsgBox "Entered value is " & Range("A1").Value

2. Tin nhắn nâng cao hơn một chút

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.

1 MsgBox "Line 1" & vbNewLine & "Line 2"

3. Để bắt đầu một dòng mới, sử dụng vbNewLine

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Kết quả như sau:

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.

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"

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:

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

VBA TRONG EXCEL | Lập trình cơ bản trong excel

mở . Tập hợp Worksheets bao gồm tất cả các đối tượng Worksheet trong một workbook.

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 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

1. Sử dụng tên worksheet

đầ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).

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button trên bảng tính và thêm những dòng code sau:

1 Workbooks.Add

1. Method “Add” của tập hợp workbook tạo một workbook mới:

Chú ý: Method Add của worksheet sẽ tạo worksheet mới

1 MsgBox Worksheets.Count Kết quả khi bạn kích vào button như sau:

2. Property “Count” của tập hợp Worksheet đếm số lượng worksheets trong một workbook

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Chú ý: Property “Count” của workbook sẽ đếm số workbook đang active.

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

1 Range("B3").Value = 2 Kết quả khi bạn kích vào button như sau:

Đặt một command button trong worksheet của bạn và thêm những dòng code sau:

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

Code:

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)

1 Cells(3, 2).Value = 2 Kết quả:

Code:

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

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

Code:

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

Dim example As Range Set example = Range("A1:C4") example.Value = 8

1 2 3 4 Kết quả:

Code:

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 ô.

Dim example As Range Set example = Range("A1:C4") example.Select

1 2 3 4 Kết quả:

Code:

4.5 Rows

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Thuộc tính Rows để bạn truy xuất vào một dòng cụ thể của chuỗi range

Dim example As Range Set example = Range("A1:C4") example.Rows(3).Select

1 2 3 4 Kết quả:

Code:

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ể .

1 2 3 4

Dim example As Range Set example = Range("A1:C4") example.Columns(2).Select

Kết quả:

Code:

4.7 Copy/Paste

VBA TRONG EXCEL | Lập trình cơ bản trong excel

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

Range("A1:A2").Select Selection.Copy Range("C3").Select ActiveSheet.Paste

1 2 3 4 5 Kết quả:

Code:

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

1 Range("C3:C4").Value = Range("A1:A2").Value

với cùng mục đích:

4.8 Clear

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

Để xóa bỏ nội dung của chuỗi các ô, bạn có thể sử dụng phương thức ClearContents

phương thức ClearFormats sẽ chỉ xỏa bỏ định dạng .

4.9 Count

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Với thuộc tính count, bạn có thể đếm số ô, số dòng, số cột của chuỗi

Chú ý: Đường border chỉ mang tính minh họa

Dim example As Range Set example = Range("A1:C4") MsgBox example.Count

1 2 3 4

Code:

Kết quả:

Dim example As Range Set example = Range("A1:C4") MsgBox example.Rows.Count

1 2 3 4 Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

Chú ý: Với cách tương tự bạn có thể đếm số cột .

5 Biến

Đặt một command button trong bảng tính của bạn và thêm những dòng code sau. Để thực

hiện dòng code thì kích vào button.

5.1 Integer

Dim x As Integer x = 6 Range("A1").Value = x

1 2 3 Kết quả:

Biến Integer được dùng để lưu trữ số

Giải thích: Dòng code đầu tiên khai báo một biến với tên x kiểu Integer. Kế tiếp, chúng ta

khởi tạo x với giá trị x. Cuối cùng gán giá trị vào ô A1.

5.2 String

Biến kiểu String được sử dụng để lưu trữ text

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

Dim book As String book = "bible" Range("A1").Value = book

1 2 3 Kết quả:

Giải thích: Dòng code đầu tiên khai báo một kiến tên là book kiểu String. Tiếp theo là

khởi tạo biến book bằng việc gán giá trị cho nó là “bible”. Luôn luôn sử dụng dấu nhắc để

tạo biến kiểu string. Cuối cùng là gán nó vào ô A1

5.3 Double

Một biến kiểu Double thì được dùng cho những số chính xác hơn kiểu Integer và có thể

lưu trữ những số thập phân

Dim x As Integer x = 5.5 MsgBox "value is " & x

1 2 3 Kết quả:

Code:

Nhưng đó không phải là giá trị đúng! Đang lẽ nó phải là 5.5 chứ . Cái chúng ta cần là biến

VBA TRONG EXCEL | Lập trình cơ bản trong excel

kiểu Double

Dim x As Double x = 5.5 MsgBox "value is " & x

1 2 3 Kết quả:

5.4 Boolean

Sử dụng biến kiểu Boolean để giữ giá trị True hoặc False

Dim continue As Boolean continue = True If continue = True Then MsgBox "Boolean variables are cool"

1 2 3 4 Kết quả:

Code:

6 Câu lệnh If Then

1 2 3

Dim score As Integer, result As String score = Range("A1").Value

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một Command button trong bảng tính của bạn và add những dòng code sau:

If score >= 60 Then result = "pass" Range("B1").Value = result

4 5 6 Giải thích: Nếu score lớn hơn hoặc bằng 60, Excel VBA sẽ trả kết quả là “pass”

Kết quả như sau:

Chú ý: Nếu score ít hơn 60, Excel VBA sẽ đặt một giá trị trống trong ô B1

6.1 Else

Dim score As Integer, result As String score = Range("A1").Value If score >= 60 Then result = "pass" Else result = "fail" End If Range("B1").Value = result

1 2 3 4 5 6 7 8 9 10 Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button trong bảng tính và thêm những dòng code sau:

7 Vòng lặp

Vòng lặp là một trong những kỹ thuật lập trình mạnh mẽ nhất . Một vòng lặp trong Excel

VBA cho phép bạn lặp qua lượt lượt các ô trong chuỗi chỉ với một vài dòng code

7.1 Vòng lặp đơn

Bạn có thể sử dụng vòng lặp đơn để lặp qua một chuỗi một chiều các ô trong Excel

Dim i As Integer 1 2 For i = 1 To 6 3 Cells(i, 1).Value = 100 4 Next i 5 Kết quả:

Đặt một command button trong bảng tính và thêm những dòng code sau:

Giải thích: Những dòng code giữa For và Next sẽ được thực hiện 6 lần . For i = 1, Excel

VBA sẽ nhập giá trị 100 vào ô giao giữa dòng 1 và cột 1. Khi Excel VBA chạy tới dòng

Next i, nó tăng i thêm 1 đơn vị và trở lại dòng code For. For i=2, Excel VBA sẽ nhập giá

trị 100 vào giao giữa dòng 2 và cột 1, v..v

7.2 Vòng lặp đôi

Bạn sử dụng một vòng lặp đôi để lặp qua mảng 2 chiều . (Range gồm cả dòng và cột)

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button vào bảng tính và thêm các dòng code sau:

Dim i As Integer, j As Integer 1 2 For i = 1 To 6 3 For j = 1 To 2 4 Cells(i, j).Value = 100 5 Next j 6 7 Next i Kết quả:

Giải thích: For i = 1 và j = 1, Excel VBA nhập giá trị 100 vào ô giao giữa dòng 1 và cột

1. Khi Excel VBA chạy tới dòng Next j, nó tăng j thêm 1 và nhẩy trở lại dòng For j. For i

= 1 và j = 2, Excel VBA sẽ nhập giá trị 100 vào giao giữa dòng 1 và cột 2. Cứ tiếp tục như

vậy cho tới khi j > 2, thì vòng lặp sẽ nhẩy tới dòng Next i và tăng i thêm một và tiếp tục

vòng lặp của biến j.

7.3 Vòng lặp ba

Bạn có thể sử dụng vòng lặp ba để lặp qua mảng 3 chiều ở nhiều bảng tính

1 2 3 4 5 6 7 8 9

Dim c As Integer, i As Integer, j As Integer For c = 1 To 3 For i = 1 To 6 For j = 1 To 2 Worksheets(c).Cells(i, j).Value = 100 Next j Next i Next c

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

Bạn tự suy luận cách mà nó làm việc nhé

7.4 Vòng lặp Do While

Bên cạnh vòng lặp For Next, có những vòng lặp khác trong Excel VBA. Ví dụ, vòng lặp

Do While. Code đặt giữa Do While và Loop sẽ lặp lại miễn là phần điều kiện sau Do While

đúng

Dim i As Integer 1 i = 1 2 3 Do While i < 6 4 Cells(i, 1).Value = 20 5 i = i + 1 6 7 Loop Kết quả:

1. Đặt command button trong worksheet và thêm những dòng code sau:

Giải thích: miễn là i nhỏ hơn 6, Excel VBA nhập giá trị 20 vào ô giao giữa dòng i và cột

1 và tăng i thêm 1. Trong Excel VBA, dấu “=” không có nghĩa là băng . Vì thế i = i + 1

nghĩa là i sẽ trở thành i + 1. Vì thế, lấy giá trị hiện tại của i và cộng thêm 1. Ví dụ, nếu i =

1, i sẽ thành 1 + 1 = 2. Kết quả, giá trị 20 sẽ đặt trong cột A 5 lân.

VBA TRONG EXCEL | Lập trình cơ bản trong excel

2. Nhập một vài số trong cột A

Dim i As Integer 1 i = 1 2 3 Do While Cells(i, 1).Value <> "" 4 Cells(i, 2).Value = Cells(i, 1).Value + 10 5 i = i + 1 6 7 Loop Kết quả:

3. Đặt một command button trong bảng tính và thêm dòng code sau:

8 Lỗi Macro

Bài này dạy bạn cách để xử lý lỗi macro trong Excel. Đầu tiên, chúng ta hãy tạo một vài

lỗi

x = 2 Range("A1").Valu = x

1 2 1. Kích vào button trên bảng tính

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button trong bảng tính và thêm vài dòng code sau:

Kết quả:

2. Kích OK

Biến x chưa được định nghĩa . Bởi vì chúng ta sử dụng câu lệnh Option Explicit ở đầu đoạn

code, chúng ta phải khai báo tất cả các biến . Excel VBA đã bôi màu x thành màu xanh để

chỉ dẫn lỗi

3. Trong Visual Basic Editor, kích Reset để dừng debugger

1 Dim x As Integer

VBA TRONG EXCEL | Lập trình cơ bản trong excel

4. Sửa lỗi bằng cách thêm dòng code sau

Có thể nếu bạn đã quen với lập trình thì đã nghe thấy từ debug, kỹ thuật này giúp bạn chạy

qua từng dòng code để xem kết quả

5. Trong Visual Basic Editor, đặt con trỏ trước Private và ấn F8

Dòng đầu tiên sẽ chuyển sang màu vàng

VBA TRONG EXCEL | Lập trình cơ bản trong excel

6. Ấn F8 3 lần

Lỗi sau sẽ xuất hiện

Đối tượng Range có một property gọi là Value. Value không được viết đúng ở đây . Debug

là một cách tuyệt vời để tìm lỗi, ngoài ra giúp bạn hiểu code hơn.

9 Xử lý String

Trong bài này, bạn sẽ tìm những chức năng quan trọng nhất để xử lý string trong Excel

VBA

Đặt một command button trong bảng tính và thêm dòng code ở bên dưới .

9.1 Liên kết String

Chúng ta sử dụng toán tử & để kết nối string

Dim text1 As String, text2 As String text1 = "Hi" text2 = "Tim" MsgBox text1 & " " & text2

1 2 3 4 5 Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

9.2 Left

Để lấy số ký tự bên trái string sử dụng phương thức Left

Dim text As String text = "example text" MsgBox Left(text, 4)

1 2 3 4 Kết quả:

Code:

9.3 Right

Tương tự như Left, phương thức Right sẽ lấy ký tự ở bên trái string

1 MsgBox Right("example text", 2) Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

9.4 Mid

Để lấy chữ từ một vị trí trong string sử dụng phương thức Mid

1 MsgBox Mid("example text", 9, 2) Kết quả:

Code:

9.5 Len

Để lấy về độ dài của string, sử dụng Len

1 MsgBox Len("example text") Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

9.6 Instr

Để tìm vị trí của một chuỗi ký tự trong một string, sử dụng Instr

1 MsgBox Instr("example text", "am") Kết quả:

Code:

10 Date và Time

Bài này sẽ học cách làm việc với Date và Time trong Excel VBA

Đặt một command button trong bang tính và add những dòng code sau .

10.1 Year, Month, Day của Date

Đoạn code sau sẽ lấy về năm (year) của Date. Để khai báo date, sử dụng câu lệnh Dim. Để

khởi tạo date, sử dụng chức năng DateValue

1 Dim exampleDate As Date

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

exampleDate = DateValue("Jun 19, 2010") MsgBox Year(exampleDate)

2 3 4 5 Kết quả:

Tương tự bạn sử dụng Month và Day để lấy và tháng và ngày của Date

10.2 DateAdd

Để cộng một số ngày vào date, sử dụng phương thức DateAdd. Phương thức này có 3 đối

số . Để “d” ở đối số đầu tiên để cộng ngày . Để 3 ở đối số thứ 2 để cộng 3 ngày . Đối số

thứ 3 chính là biến date để cộng ngày vào .

Dim firstDate As Date, secondDate As Date firstDate = DateValue("Jun 19, 2010") secondDate = DateAdd("d", 3, firstDate) MsgBox secondDate

1 2 3 4 5 6

Code:

Chú ý: Thay đổi “d” thành “m” để công tháng vào date. Bạn có thể sử dụng phím F1 để

tìm hiểu thêm. Date ở trong định dạng US.

10.3 Date và Time hiện tại

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Để lấy Date và time hiện tại sử dụng hàm Now

1 MsgBox Now Kết quả:

Code:

10.4 Giờ, phút, giây (Hour, Minute, Second)

Để lấy giờ sử dụng hàm Hour

1 MsgBox Hour(Now) Kết quả:

Code:

Tương tự với phút và giây

10.5 TimeValue

Hàm TimeValue sẽ chuyển đổi một string thành thời gian. Thời gian là một số giữa 0 và 1.

Ví dụ, noon sẽ hiển thị là 0.5

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Code:

1 MsgBox TimeValue("9:20:01 am") Kết quả:

Dim y As Double y = TimeValue("09:20:01") MsgBox y

1 2 3 Kết quả:

Bây giờ thử đoạn code sau:

11 Event (Sự kiện)

Events là những hành động được thực hiện bởi người dùng mà sẽ thực hiện đoạn code trong

Excel VBA

11.1 Sự kiện Workbook Open

Code đã thêm vào sử dụng Workbook open sẽ thực hiện khi bạn mở workbook

VBA TRONG EXCEL | Lập trình cơ bản trong excel

1. Mở Visual Basic Editor 2. Kích đúp vào ThisWorkbook trong Project Explorer

3. Chọn Workbook từ danh sách xổ xuống bên trái . Chọn Open từ danh sách xổ

xuống bên phải

1 MsgBox "Good Morning" 5. Lưu lại, đóng và mở lại file Excel

4. Thêm dòng code sau vào sự kiện Workbook Open

Kết quả:

11.2 Sự kiện Worksheet Change

Code sẽ thực hiện khi bạn thay đổi một cell nào đó trong bảng tính

VBA TRONG EXCEL | Lập trình cơ bản trong excel

1. Mở Visual Basic Editor 2. Kích đúp vào một sheet (ví dụ Sheet1) trong Project Explorer

3. Chọn Worksheet và Change như hình dưới:

4. Sử kiện Worksheet Change sẽ lắng nghe tất cả những thay đổi trong Sheet1. Chúng ta

chỉ muốn Excel VBA làm một điều gì đó nếu thực hiện thay đổi ở ô B2 . Thêm những dòng

If Target.Address = "$B$2" Then End If

1 2 3 5. Chúng ta muốn Excel VBA hiển thị một MsgBox nếu người dùng nhập giá trị lớn hơn 80. Thêm dòng code sau:

1 If Target.Value > 80 Then MsgBox "Goal Completed" 6. Trong Sheet1, nhập một số lớn hơn 80 trong ô B2

code sau:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Kết quả:

12 Mảng

Một Array là một tập hợp các biến . Trong Excel VBA, bạn có thể gọi đến một phần tử của

mảng sử dụng tên array kèm theo chỉ số

12.1 Mảng một chiều

Để tạo mảng một chiều, thực hiện các bước sau:

Dim Films(1 To 5) As String Films(1) = "Lord of the Rings" Films(2) = "Speed" Films(3) = "Star Wars" Films(4) = "The Godfather" Films(5) = "Pulp Fiction" MsgBox Films(4)

1 2 3 4 5 6 7 8 9 Kết quả khi bạn kích vào command button:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button trong bảng tính và thêm dòng code sau:

Giải thích: Dòng code đầu tiên khai báo một mảng string với tên là Films. Mảng bao gồm

5 phần tử . Kế đến chúng ta khởi tạo mỗi phần tử của mảng . Cuối cùng, chúng ta hiển thị

phần tử thứ tư sử dụng MsgBox

12.2 Mảng hai chiều

Để tạo mảng hai chiều, thực hiện các bước sau. Lần này, chúng ta sẽ đọc tên của bảng tính

Dim Films(1 To 5, 1 To 2) As String Dim i As Integer, j As Integer For i = 1 To 5 For j = 1 To 2 Films(i, j) = Cells(i, j).Value Next j Next i MsgBox Films(4, 2)

1 2 3 4 5 6 7 8 9 10 Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button trong bảng tính và thêm những dòng code sau:

Giải thích: Dòng code đầu tiên khai báo một mảng String với tên Films. Mảng có hai chiều

. Nó bao gồm 5 dòng và 2 cột (dòng trước, cột sau nhé) . Hai biến loại Integer được dùng

cho vòng lặp để khởi tạo giá trị trong mảng . Cuối cùng chúng ta hiển thị phần tử giao giữa

hàng 4 và cột 2

13 Function và Sub

Sự khác nhau giữa một function và sub trong Excel VBA đó là function trả về một giá trị

còn sub thì không .

13.1 Function

Function Area(x As Double, y As Double) As Double Area = x * y End Function

Nếu bạn muốn Excel VBA để thực hiện một công việc mà trả về một kết quả, bạn có thể sử dụng một function. Đặt một function trong một module (trong Visual Basic Editor, kích vào Insert > Module). Ví dụ, function với tên là Area

1 2 3 4 5 Giải thích: Chức năng này có hai đối số (loại Double) và trả về kiểu Double. Bạn có thể

sử dụng tên của function (Area) trong code của bạn để chỉ dẫn loại kết quả mà bạn muốn

trả về

Bây giờ bạn có thể sử dụng function này từ bất kỳ đâu trong code của bạn bằng cách sử

dụng tên của function và nhập đối số cho nó

Dim z As Double z = Area(3, 5) + 2 MsgBox z

1 2 3 4 5 Kết quả:

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Đặt một command button trong bảng tính và thêm dòng code sau

13.2 Sub

Sub Area(x As Double, y As Double) MsgBox x * y End Sub

1 2 3 4 5 Đặt một command button trong bảng tính và thêm dòng code sau:

1 Area 3, 5 Kết quả:

Nếu bạn muốn Excel VBA để thực hiện một số hành động, bạn có thể sử dụng sub. Đặt một sub trong một module:

Bạn có thể thấy sự khác nhau giữa function và sub chứ ????

14 Đối tượng Application

Đối tượng Application là cha của tất cả các đối tượng trong Excel.

14.1 WorksheetFunction

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Bạn có thể sử dụng WorksheetFunction trong Excel VBA để trỏ đến các chức năng trong

Excel

1 Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2")) Khi bạn kích vào command button trong bảng tinh. Excel VBA tính toán giá trị trung bình

1. Ví dụ, đặt một command button trong bảng tính và thêm dòng code sau

của ô A1 và A2, sau đó đặt kết quả vào ô A3.

Chú ý: Thay vì sử dụng Application.WorksheetFunction.Average, chỉ cần sử dụng

WorksheetFunction.Average. Nếu bạn nhìn vào thanh công thức, bạn có thể thấy rằng bản

thân công thức không hiển thị trong ô A3. Để thêm công thức vào ô này sử dụng dòng code

1 Range("A3").Value = "=AVERAGE(A1:A2)"

14.2 ScreenUpdating

Đôi khi bạn có thể vô hiệu hóa quá trình cập nhật tính toán trên màn hình trong khi thực

hiện code, điều này sẽ giúp code chạy nhanh hơn

Dim i As Integer For i = 1 To 10000 Range("A1").Value = i Next i

1 2 3 4 5 Khi bạn kích vào command button trong bảng tính, Excel VBA sẽ tính toán và mất vài giây

VBA TRONG EXCEL | Lập trình cơ bản trong excel

1. Ví dụ, đặt một command button trong bảng tính và thêm những dòng code sau

Dim i As Integer Application.ScreenUpdating = False For i = 1 To 10000 Range("A1").Value = i Next i Application.ScreenUpdating = True

1 2 3 4 5 6 7 8 9 Kết quả là code sẽ chay nhanh hơn và bạn sẽ chỉ nhìn thấy kết quả là 10000

2. Để tăng tốc, sửa code như sau:

14.3 DisplayAlerts

Bạn có thể chỉ dẫn Excel VBA không hiển thị cảnh báo khi chạy code

1 ActiveWorkbook.Close Khi bạn kích vào command button trong bảng tính, Excel VBA sẽ đóng file Excel và hỏi

1. Ví dụ đặt một command button trong bảng tính và thêm những dòng code sau

bạn có lưu hay không

1 2

Application.DisplayAlerts = False

VBA TRONG EXCEL | Lập trình cơ bản trong excel

2. Để chỉ dẫn không hiển thị bảng thông báo khi chạy code, sửa code như sau:

3 4 5

ActiveWorkbook.Close Application.DisplayAlerts = True

14.4 Calculation

Mặc định, sự tính toán được đặt tự động . Nếu workbook của bạn bao gồm nhiều công thức

phức tạp, bạn có thể tăng tốc macro của bạn bằng cách sử dụng tính toán bằng tay

1. Đặt đoạn code sau vào command button 1 Application.Calculation = xlCalculationManual Khi bạn kích vào nút này, Excel VBA sẽ cài đặt sự tính toán trên bảng excel bằng tay

VBA TRONG EXCEL | Lập trình cơ bản trong excel

2. Bạn có thể xác nhận điều này bằng kích chọn File > Option > Formulas

3. Bây giờ khi bạn thay đổi giá trị ô A1, giá trị ô B1 không được tính lại

bạn có thể tính lại bằng cách ấn F9

4. Trong hầu hết trường hợp, bạn sẽ cài đặt sự tính toán để tự động tính lại ở cuối đoạn

1 Application.Calculation = xlCalculationAutomatic

code. Đơn giản thêm dòng code sau:

15 ActiveX Control

Bài này sẽ học cách tạo ActiveX control như command button, text box, list box, v..v. Để

tạo một ActiveX control trong VBA, làm những bước sau

1. Trên menu Developer, kích Insert

VBA TRONG EXCEL | Lập trình cơ bản trong excel

2. Ví dụ, trong khu vực ActiveX control, kích vào Command Button để thêm một nút

3. Kéo nút này vào trong bảng tính

4. Kích chuột phải vào command button

5. Kích View Code

Chú ý: Bạn có thể thay đổi tiêu đề và tên của control bằng kích chuột phải vào control và

chọn properties.

VBA TRONG EXCEL | Lập trình cơ bản trong excel

6. Thêm dòng code bên dưới vào giữa Private Sub và End Sub

7. Chọn các ô B2:B4 và kích vào command button

Kết quả:

16 Userform

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Bài này dạy bạn cách tạo một giao diện người dùng trong Excel VBA. Nó trông như sau:

16.1 Thêm Control

Để thêm control làm những bước sau:

1. Mở Visual Basic Editor. Nếu Project Exporer không xuất hiện, kích vào View > Project

Explorer

2. Kích Insert > Userform. Nếu Toolbox không xuất hiện tự động, kích View > Toolbox.

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Màn hình của bạn trông như bên dưới:

3. Thêm những control như bảng bên dưới đã liệt kê . Khi hoàn thành, kết quả giống như

hình đầu tiên của bài . Ví dụ, tạo một text box bằng cách kích vào TextBox từ trong

Toolbox, kế đến bạn kéo text box vào trong Userform …

4. Thay đổi tên và tiêu đề của các control như bảng bên dưới . Tên (Name) được sử dụng

VBA TRONG EXCEL | Lập trình cơ bản trong excel

trong code Excel VBA. Caption xuất hiện trên màn hình .

16.2 Hiển thị Userform

Để hiển thị Userform, đặt một command button trong bảng tính và thêm những dòng code

1 2 3 4 5

Private Sub CommandButton1_Click() DinnerPlannerUserForm.Show End Sub

VBA TRONG EXCEL | Lập trình cơ bản trong excel

Chúng ta sẽ tạo một Sub UserForm_Initialize. Khi bạn sử dụng phương thức Show của

Userform, sub này sẽ tự động được gọi

1. Mở Visual basic editor

2. Trong Project Explorer, kích chuột phải vào DinnerPlannerUsserForm và kích vào View

Code

3. Chọn Userform từ danh sách bên trái và chọn Initialize ở danh sách bên phải

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Private Sub UserForm_Initialize() 'Empty NameTextBox NameTextBox.Value = "" 'Empty PhoneTextBox PhoneTextBox.Value = "" 'Empty CityListBox CityListBox.Clear 'Fill CityListBox With CityListBox .AddItem "San Francisco" .AddItem "Oakland" .AddItem "Richmond" End With 'Empty DinnerComboBox DinnerComboBox.Clear 'Fill DinnerComboBox With DinnerComboBox .AddItem "Italian" .AddItem "Chinese" .AddItem "Frites and Meat" End With 'Uncheck DataCheckBoxes DateCheckBox1.Value = False DateCheckBox2.Value = False DateCheckBox3.Value = False

VBA TRONG EXCEL | Lập trình cơ bản trong excel

4. Thêm dòng code sau:

33 34 35 36 37 38 39 40 41 42 43

'Set no car as default CarOptionButton2.Value = True 'Empty MoneyTextBox MoneyTextBox.Value = "" 'Set Focus on NameTextBox NameTextBox.SetFocus End Sub

16.3 Gán Macro

Chúng ta đã tạo phần đầu tiên của Userform. Tiếp theo làm như sau

1. Mở Visual Basic Editor

2. Trong Project Explorer, kích đúp vào DinnerPlannerUserForm

3. Kích đúp vào nút Money spin

Private Sub MoneySpinButton_Change() MoneyTextBox.Text = MoneySpinButton.Value End Sub

1 2 3 4 5 5. Kích đúp vào nút OK

4. Thêm những dòng code sau:

1 2 3 4 5 6 7 8 9 10

Private Sub OKButton_Click() Dim emptyRow As Long 'Make Sheet1 active Sheet1.Activate 'Determine emptyRow emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 'Transfer information Cells(emptyRow, 1).Value = NameTextBox.Value Cells(emptyRow, 2).Value = PhoneTextBox.Value

VBA TRONG EXCEL | Lập trình cơ bản trong excel

6. Add những dòng code sau:

Cells(emptyRow, 3).Value = CityListBox.Value Cells(emptyRow, 4).Value = DinnerComboBox.Value If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption If CarOptionButton1.Value = True Then Cells(emptyRow, 6).Value = "Yes" Else Cells(emptyRow, 6).Value = "No" End If Cells(emptyRow, 7).Value = MoneyTextBox.Value End Sub