Lê Thế Vinh, Phòng Đào tạo, Trường ĐHSP Kỹ thuật Vinh
Nghệ An, 5/2013 1
Hướng dẫn sử dụng VBA trong Excel
1. Giới thiệu
Các phần mềm trong MS Office nói chung và Excel nói riêng là công cụ giúp
chúng ta lưu trữ số liệu, xử lý số liệu trong quá trình làm việc rất hiệu quả, tiết kiệm
thời gian, công sức. Ngoài các hàm đơn giản được trang bị sẵn trong thưviện của
Excel nhưhàm Sum(), Max(), Min(), Text(), Value(), Left(), Vlookup () Average()
v.v. Người sử dụng có thể lập trình, tạo ra các hàm, thủ tục phức tạp hơn theo nhu cầu
sử dụng bằng cách dùng công cụ VBA (Visual Basic for Application), dùng ngôn ngữ
lập trình VB, nhúng vào các ứng dụng của Microsoft nhưMS Word, PowerPoint, Auto
CAD v.v. Ở đây chúng ta viết các hàm, thủ tục cho Microsoft Excel.
2. Vào ra chương trình, cài đặt để sử dụng hàm trong Excel
a. Vào ra chương trình
Bước 1. Khởi động Microsoft Excel, tạo một workbook mới (hoặc dùng Book1
nhưmặc định). Chọn Tools\Macro\Visual Basic Editor để mở cửa sổ soạn thảo VBA.
Trong cửa sổ Project Explorer (nếu không thấy thì chọn View Project Explorer để mở
nó), chọn VBAProject (Book1). Chọn Insert Module để thêm một module.
Bước 2. Gõ nội dung hàm phepcong() vào module mới tạo.
Ví dụ:
Public Function Phepcong(x as double, y as double) as double
Phepcong = x + y
End Function
Bước 3. Chuyển sang cửa sổ Excel để kiểm tra hàm bằng cách thử gõ vào ô bất
kỳ, =Phepcong(x,y), kết quả trả về ô tương ứng là x+y. Ví dụ: = Phepcong(2,3) kết quả
trả về là 5.
b. Cài đặt và sử dụng hàm trong Excel
Bước 1. Mô tả hàm để thuận tiện cho việc sử dụng. Trong cửa sổ Excel, chọn
menu Tools\Macro\Macros để hiển thị hộp thoại Macro. Gõ tên hàm Phepcong vào ô
Macro name, rồi bấm Options để mở tiếp hộp thoại Macro Options, gõ nội dung mô tả
Lê Thế Vinh, Phòng Đào tạo, Trường ĐHSP Kỹ thuật Vinh
Nghệ An, 5/2013 2
hàm vào ô Description, bấm OK để đóng hộp thoại Macro Options, cuối cùng bấm
Cancel để đóng hộp thoại Macro.
Bước 2. LưuBook1 vào máy với tên file, ví dụ MyFunctions.xla; Bạn lưu file này
đúng vị trí để mỗi lần MS Excel khởi động sẽ mặc định đưa ham vào thưviện chung,
để sẵn sàng sử dụng.
Ví dụ: Thưmục C:\Documents and Settings\Administrator\Application Data\
Microsoft\AddIns đối với MS 2003.
Đóng MS Excel mà không cần ghi lại các thay đổi trên Book1.
Bước 3. Dùng Windows Explorer để thêm mô tả cho Add-in bằng cách bấm chuột
phải trên tên tập tin MyFunctions.xla, chọn mục Properties để mở hộp thoại
Properties, chọn mục Summary để gõ mô tả vào ô Comments, gõ tên của Add-in vào ô
Title. Chọn nút OK để chấp nhận các thông tin.
Bước 4. Khởi động lại MS Excel, cài đặt add-in vừa tạo cho Excel bằng cách chọn
menu Tools\Add-Ins để mở hộp thoại Add-Ins, chọn tên Add-in của ta trong danh sách
Add-Ins available, rồi bấm chuột vào nút OK.
Từ đây trở về sau, mỗi lần mở MS Excel, mặc định bạn có thể dùng được hàm
Phepcong trong Add-in MyFunctions.xla.
3. Lưu ý một số khái niệm
a) Thuộc tính RANGE
Thuộc tính Range trả về một đối tượng, có thể là một cell đơn lẻ hoặc một dãy
gồm nhiều cell. Trong lệnh gán giá trị của cell A1 vào cell A5 đưới đây, Range trả về
giá trị chỉ gồm 1 cell:
Worksheets("Sheet1").Range("A5").Value =
Worksheets("Sheet1").Range("A1").Value
Khi dùng với kiểu không xác định, Range trả về một dãy cell trên Sheet đang hoạt
động. Để tránh nhầm lẫn kiểu "Râu ông nọ cắm cằm bà kia", ta kích hoạt một bảng
tính bằng phương pháp Activate trước khi dùng thuộc tính Range mà không cần chỉ ra
giới hạn cụ thể. Sau đây là cách điền vào vùng A1:H8 các số ngẫu nhiên bằng cách đặt
công thức cho mỗi ô trong dãy mà trướcđó Sheet1 đãđược kích hoạt:
Lê Thế Vinh, Phòng Đào tạo, Trường ĐHSP Kỹ thuật Vinh
Nghệ An, 5/2013 3
Worksheets("sheet1").Activate
Range("A1:H8").Formula = "=rand()"
Chúng ta có thể đặt tên cho dãy cell và chỉ cho VBA tham chiếu đến tên này. Ví
dụ, để ra lệnh xóa nội dung của một dãy có tên "Criteria" chúng ta viết:
Worksheets(1).Range("criteria").ClearContents
Nếu ta sử dụng một đối số (argument) màđối số đó làđịa chỉ dãy cell, thì phải
định rõđịa chỉ đó theo kiểu A1 (nếu dùng kiểu R1C1 máy sẽ báo lỗi).
b) Thuộc tính Cells
Thuộc tính Cells sử dụng cú pháp Cells(row,column), với row là chỉ số hàng và
column là chỉ số cột, để trả về một cell đơn lẻ. Chẳng hạn, chúng ta có thể gán giá trị
24 cho cell A1 bằng lệnh:
Worksheets(1).Cells(1, 1).Value = 24
Và gán một công thức cho cell A2:
ActiveSheet.Cells(2, 1).Formula = "=sum(b1:b5)"
Cho dù chúng ta có thể dùng công thức Range("A1") để trả về cell A1 (không hề
sai!), nhưng dùng thuộc tính Cells thì tiện lợi hơn bởi bạn có thể sử dụng một biến số
đại diện cho hàng hoặc cột phục vụ yêu cầu lập trình. Hãy tham khảo ví dụ tạo tiêu đề
cột và hàng trên Sheet1 sau đây, lưu ý sau khi bảng tính đãđược kích hoạt, thuộc tính
Cells có thể được sử dụng thoải mái mà không cần khai báo một sheet cụ thể nào vì nó
trả về một cell trên sheet hoạt động.
Sub SetUpTable()
Worksheets("sheet1").Activate
For theYear = 1 To 5
Cells(1, theYear + 1).Value = 1990 + theYear
Next theYear
For theQuarter = 1 To 4
Cells(theQuarter + 1, 1).Value = "Q" & theQuarter
Next theQuarter
End Sub
Lê Thế Vinh, Phòng Đào tạo, Trường ĐHSP Kỹ thuật Vinh
Nghệ An, 5/2013 4
Mặc dù bạn có thể sử dụng những hàm xử lý chuỗi của Visual Basic để thay đổi
tham chiếu kiểu A1, nhưng để dễ hiểu hơn và rèn luyện kỹ năng lập trình của mình,
bạn nên sử dụng kiểu Cells(1, 1).
Nếu bạn sử dụng cú pháp expression.Cells(row,column), với expression là một
biểu thức trả về một đối tượng Range, và row và column là vị trí tương đối so với góc
trên trái của dãy cell thì kết quả trả về là một bộ phận của dãy. Trong ví dụ dưới đây,
VBA chỉ đến Cell(1,1) tức là cell C5 trên bảng tính:
Worksheets(1).Range("c5:c10").Cells(1, 1).Formula = "=rand()
c) Kết hợp Range and Cells
Phối hợp Range và Cells để tham chiếu đến một dãy cell, bạn hãy sử dụng cú
pháp Range(cell 1, cell 2), với cell 1 và cell 2 là cell bắt đầu và cell kết thúc của dãy.
Để đặt kiểu đường viền cho các cell bạn dùng lệnh sau:
With Worksheets(1)
.Range(.Cells(1, 1), .Cells(10, 10)).Borders.LineStyle = xlThick
End With
Ở đây cần lưu ý đến dấu chấm đằng trước thuộc tính Cells. Dấu chấm này quy
định Worksheets(1) mà With đã nêu trước đó được áp dụng cho thuộc tính Cells để tiết
kiệm chỗ và làm cho câu lệnh gọn hơn (nếu không có dấu chấm, thuộc tính Cells trả về
các cell trên sheet hoạt động).
4. Một số ví dụ
Sau đây là một số ví dụ xử lý chuỗi và số trong trường học.
Option Explicit
Dim k As Long
Public luachon As Byte
Public newValue As Double
Public actCell As Range
Public formu As String
Public ass As String
Public first As Boolean
Function SoTCchuadat(Dayheso As Range, Daydiemthi As Range) As
Integer
' Xac dinh Co tin chi Chua dat (co Diem <=0.5)
Dim i As Integer
Lê Thế Vinh, Phòng Đào tạo, Trường ĐHSP Kỹ thuật Vinh
Nghệ An, 5/2013 5
SoTCchuadat = 0.5
For i = 1 To Daydiemthi.Columns.Count
If (Daydiemthi.Columns.Cells(1, i) <= 0.5) Then
SoTCchuadat = SoTCchuadat + Dayheso.Columns.Cells(i)
End If
Next i
End Function
Function SoTCTL(Dayheso As Range, Daydiemthi As Range,
TrueORflase As Boolean) As Integer
Dim i As Integer
' Neu TrueORflase=true thi tra ve so Tin chi tich luy
' Neu TrueORflase=flase thi tra ve so Tin chi diem 0
SoTCTL = 0.5
If TrueORflase Then
For i = 1 To Daydiemthi.Columns.Count
If (Daydiemthi.Columns.Cells(1, i) > 0.5) Then
SoTCTL = SoTCTL + Dayheso.Columns.Cells(i)
End If
Next i
Else
For i = 1 To Daydiemthi.Columns.Count
If (Daydiemthi.Columns.Cells(1, i) = 0.5) Then
SoTCTL = SoTCTL + Dayheso.Columns.Cells(i)
End If
Next i
End If
End Function
Public Function Loc(diem As String) As Byte
Dim lan1 As Byte
Dim lan2 As Byte
Dim s As String
If Not KiemtraDiem(diem) Then