VBA căn bản (3 người xem)

[handung107]

Tham chiếu địa chỉ Cell trong VBA

Visual Basic for Applications (VBA) là một bộ phận của ngôn ngữ lập trình Visual Basic dành riêng để soạn thảo các lệnh macro trong bộ Office, giúp tăng cường sức mạnh và tự động hóa một số công việc lặp đi lặp lại. Sẽ là một thuận lợi lớn nếu bạn hiểu rõ cách thức VBA tham chiếu đến địa chỉ cell trong Excel. Bài viết này giới thiệu các thuộc tính (property) và phương thức (method) trong VBA liên quan đến tham chiếu địa chỉ cell.

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 kiểu "Râu ông nọ cắm cằm bà kia", bạn hãy 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:

Worksheets("sheet1").Activate

Range("A1:H8").Formula = "=rand()"

Bạn cũng có thể đặt tên cho dãy cell và chỉ cho VBA tham chiếu đến tên này. Ví dụ, bạn ra lệnh xóa nội dung của một dãy có tên "Criteria" bằng cách:

Worksheets(1).Range("criteria").ClearContents

Nếu bạn sử dụng một đối số (argument) mà đối số đó là địa chỉ dãy cell, bạn phải định rõ địa chỉ đó theo kiểu A1 (dùng kiểu R1C1 máy sẽ báo lỗi).

 

[handung107]

Thuộc tính Cells

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, bạn 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ù bạn 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. Bạn 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

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

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

 

[handung107]

Thuộc tính Offset

Thuộc tính Offset​

VBA dùng thuộc tính Offset(r,c) để di chuyển đến một cell khác có vị trí cách r hàng và c cột so với cell hiện hành. Ví dụ dưới đây chọn một cell cách cell góc trên bên trái vùng hiện hành 3 hàng về phía dưới và 1 cột về bên phải. Bạn không thể chọn một cell không nằm trong sheet hoạt động, vì vậy trước đó bạn phải kích hoạt bảng tính bằng lệnh Activate.

Worksheets("sheet1").Activate

Selection.Offset(3, 1).Range("a1").Select

Phương thức Union​

VBA sử dụng Union(range1, range2,...) để trả về một vùng hỗn hợp - là các vùng gồm hai hoặc nhiều khối cell tách biệt nhau. Trong ví dụ dưới, một đối tượng được định nghĩa gồm 2 vùng A1:B2 và C34, sau đó chọn vùng đã được định nghĩa.

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range

Worksheets("sheet1").Activate

Set r1 = Range("A1:B2")

Set r2 = Range("C34")

Set myMultiAreaRange = Union(r1, r2)

myMultiAreaRange.Select

Nếu bạn làm việc với các vùng chọn chứa hai hay nhiều hơn một dãy cell, thuộc tính Areas rất hữu ích. Nó chia một vùng chọn hỗn hợp thành các đối tượng Range riêng biệt và sau đó trả về tập hợp các đối tượng đó. Bạn có thể sử dụng thuộc tính Count trên tập hợp đuợc trả về để kiểm tra một vùng chọn có chứa nhiều hơn một dãy cell hay không, như ví dụ sau đây:

Sub NoMultiAreaSelection()

numberOfSelectedAreas = Selection.Areas.Count

If numberOfSelectedAreas > 1 Then

MsgBox "You cannot carry out this command " & _

"on multi-area selections"

End If

End Sub.

 

[handung107]

Làm Việc Với đối Tượng Range

LÀM VIỆC VỚI ĐỐI TƯỢNG RANGE​

Tại sao lại làm việc với đối tượng Range? Nếu các bạn đọc nhiều sách viết về VBA, không ít thì nhiều các Tác giả đều chú trọng đến đối tượng Range. Cũng dễ hiểu thôi, vì khi làm việc với VBA trong Excel thì đây là đối tượng bạn phải thường xuyên làm việc. Nếu bạn hiểu rỏ đối tượng này thì bạn sẽ thao tác VBA trong Excel để giải quyết được nhiều vấn đề.
Bạn có thể xem định nghĩa của đối tượng này trong Help, ở đây tôi xin giới thiệu một cách đơn giản để bạn dễ hiểu đối tượng này thôi. Đối tượng Range có thể là một ô (cell) hay nhiều ô trong một worksheet.
Bạn có thể tham chiếu đến đối tượng này như sau:

Range("A1:C5")

Nếu bạn đã tạo ra tên của đối tượng Range, thì bạn có thể tham chiếu như sau:

Range("PriceList")

(Để tạo tên của một vùng, bạn dùng Insert→Name→Define)
Bạn có thể tham chiếu đến vùng không nằm trong ActiveWorksheet như sau:

Worksheets("Sheet1").Range("A1:C5")

Bạn cũng có thể tham chiếu đến đối tượng Range không ở ActiveWorkbook như sau

Workbooks("Budget.xls").Worksheets("Sheet1").Range ("A1:C5")

Đối tượng Range, có thể bao gồm một hay nhiều hàng

Range("3:3")

Trong trường hợ trên là tham chiếu đến hàng thứ 3.
Tương tự bạn có thể tham chiếu đến nhiều cột

Range("D:D")

Bạn cũng có thể làm việc với nhiều ô không liên tục.

Range("A1:B8,D9:G16")

Cuối cùng khi ta nói đến đối tượng Range, thì có nghĩa cũng giống như những đối tượng khác cũng có các thuộc tính (properties) và phương thức (method).
Một số cách tham chiếu đến đối tượng Range khác
Thuộc tính Cells
Ngòai cách dùng Range (keyword) bạn cũng có thể tham chiếu đến Range, thông qua thuộc tính Cells.
Thuộc tính Cells dùng 2 đối số (argument): row và column. Ví dụ sau tham chiếu đến ô C2 ở Sheet2:

Worksheets("Sheet2").Cells(2,3)

Bạn cũng có thể dùng thuộc tính Cells để tham chiếu đến nhiều ô như sau:
Range(Cells(1,1),Cells(10,10))
Ví dụ trên tham chiếu đến 100 ô, từ ô A1(hàng 1, cột 1) đến ô J10(hàng 10, cột 10)
Hai ví dụ sau cho cùng một kết quả:

Range("A1:J10").Value=99
Range(Cells(1,1),Cells(10,10)).Value=99

Nó gán giá trị 99 cho các ô trong vùng qua thuộc tính Value.
Thuận lợi của việc dùng thuộc tính Cells là bạn không cần phải đặt chính xác như "A1:J10", mà bạn có thể truyền tham số hàng và cột như ví dụ ở trên.
Thuộc tính Offset
Cũng tương tự như thuộc tính Cells ở trên thuộc tính Offset cũng dùng 2 đối số. Đối số thứ nhất: số hàng để di chuyển đi. Đối số thứ hai: số cột để di chuyển đi.
Ví dụ từ ô A1 bạn di chuyển xuống dưới 1 hàng và di chuyển qua bên phải 2 cột (tức là ô C2), được thể hiện như sau:

Range("A1").Offset(1,2)

Nếu bạn truyền cho đối số thứ nhất giá trị âm, có nghĩa là bạn di chuyển về phía bên trên. Nếu bạn truyền cho đối số thứ hai giá trị âm, có nghĩa là bạn di chuyển về phía bên trái. Ví dụ ngược lại với ở trên:

Range("C2").Offset(-1,-2)

Tức là tham chiếu đến ô A1. Ở đây bạn cũng chú ý là nếu bạn truyền tham số dẫn đến ô không thực (ví dụ nằm trên ô A1 chẳng hạn!) thì sẽ báo lỗi.
Và dĩ nhiên bạn cũng có thể truyền giá trị 0 cho hai đối số.
Dùng thuộc tính Offset là một cách hữu ích khi bạn truyền tham số chứ không phải một giá trị chính xác nào.
Tham chiếu đến các hàng và các cột
Bạn có thể tham chiếu đến một khỏang bao gồm một hay nhiều cột như sau:

Columns("A:C")

Hay tham chiếu đến nhiều hàng như sau:

Rows("1:5")

 

[handung107]

Một số thuộc tính hữu ích của đối tượng Range

Một số thuộc tính hữu ích của đối tượng Range​

Đối tượng Range có rất nhiều thuộc tính. Ở đây tôi chỉ xin giới thiệu một số thuộc tính hữu ích và hay dùng.
Chú ý: một số thuộc tính là read-only, điều này có nghĩa là bạn không thể thay đổi nó. Ví dụ, mỗi đối tượng Range có thuộc tính Address bạn có thể tham chiếu đến nó nhưng không thể thay đổi.
Thuộc tính Value
Thuộc tính Value đại diện cho giá trí được chứa trong một ô. Đây là thuộc tính read-write.
Ví dụ sau thể hiện giá trị chứa trong ô A1 tại Sheet1:
Msgbox Worksheets("Sheet1").Range("A1").Value
Bạn chỉ có thể đọc giá trị tại một ô, do đó nếu bạn viết như đọan mã sau sẽ gây ra lỗi:
Msgbox Worksheets("Sheet1").Range("A1:C3").Value
Tuy nhiên bạn có thể gán giá trị cho nhiều ô cùng một lúc như sau:
Worksheets("Sheet1").Range("A1:C3").Value=123
Đây là thuộc tính mặc định của đối tượng Range, do đó hai đọan mã sau sẽ cho giá trị như nhau:
Worksheets("Sheet1").Range("A1").Value=75
Worksheets("Sheet1").Range("A1")=75
Thuộc tính Text
Thuộc tính Text trả về một chuổi đại diện cho chữ (text) được thể hiện trong một ô, chú ý giá trị text này đã được định dạng. Đây là thuộc tính read-only. Giả sử ô A1 chứa giá trị 12.3, nhưng đã được định dạng để thể hiện $12.3. Ví dụ sau sẽ thông báo giá trị $12.3 của ô A1.
Msgbox Worksheets("Sheet1").Range("A1").Text
Nhưng ví dụ sau đây chỉ thể hiện giá trị 12.3
Msgbox Worksheets("Sheet1").Range("A1").Value
Thuộc tính Count
Thuộc tính này trả về giá trị là số lượng ô trong một khỏang (tất cả các ô chứ không phải chỉ các ô rỗng). Đây là thuộc tính read-only.
Thuộc tính Column và Row
Thuộc tính Column trả về giá trị được đại diện cho cột của một ô. Tương tự thuộc tính Row trả về giá trị được đại diện cho hàng của một ô. Hai thuộc tính này thuộc dạng read-only.
Ví dụ sau sẽ thông báo giá trị là 6:
Msgbox Sheest("Sheet1").Range("F3").Column
Ví dụ sau sẽ thông báo giá trị là 3:
Msgbox Sheest("Sheet1").Range("F3").Column
Chú ý: Nếu có nhiều ô trong một khỏang thì thuộc tính Column sẽ trả về giá trị thể hiện cột đầu tiên của khỏang đó. Tương tự với thuộc tính Row trong trường hợp này. Bạn đừng có nhầm lẫn giữa thuộc tính Column và Columns hay Row và Rows.
Thuộc tính Address
Thuộc tính Address là thuộc tính dạng read-only, thể hiện địa chỉ của ô đối với đối tượng Range theo dạng địa chỉ tuyệt đối (absolute notation). Ví dụ sau đây sẽ cho kết quả là $A$1:$E$5
Msgbox Range(Cells(1,1),Cells(5,5)).Address
Thuộc tính HasFormula
Thuộc tính này thuộc dạng read-only, thuộc tính này trả về giá trị True nếu một ô (single-cell) có chứa công thức, ngược lại trả về False. Nếu một khỏang có nhiều hơn một ô, thì VBA sẽ chỉ trả về True nếu tất cả các ô trong khỏang này đều có chứa công thức. Nếu có những ô trong một khỏang có công thức và các ô khác không có công thức thì thuộc tính này trả về Null.
Chú ý bạn khai báo biến dùng cho thuộc tính này, nếu không sẽ tạo ra lỗi sai kiểu dữ liệu.
Thuộc tính Font
Như bạn cũng đã biết, thuộc tính có thể trả về một đối tượng. Đối tượng Font có nhiều thuộc tính hữu ít cho việc định dạng.
Ví dụ sau trả về đối tượng Font của một khỏang:
Range("A1").Font
Ví dụ sau thiết lập thuộc tính Bold (kiểu chữ in đậm) của đối tượng Font của một khỏang:
Range("A1").Font.Bold=True
Thuộc tính Interior
Thuộc tính này cũng tương tự như thuộc tính Font ở trên sẽ trả về đối tượng Interior.
Ví dụ sau, sẽ thay đổi màu background của ô A1 sang màu đỏ (màu đỏ có ColorIndex=3)
Range("A1").Interior.ColorIndex=3
ColorIndex sẽ có giá trị từ 1 đến 56 tương tự với các màu trong bảng màu của Excel. Để dễ nhớ, bạn cũng có thể dùng các giá trị hằng số như: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan hay vbWhite.
Thuộc tính Formula
Thuộc tính Formula đại diện cho công thức trong một ô. Đây là thuộc tính dạng read-write. Ví dụ sau đưa công thức tính tổng vào ô A13:
Range("A13").Formula= "=Sum(A1:A12)"
Chú ý rằng công thức là chuỗi ký tự và được nằm trong dấu ngoặc kép "
Thuộc tính NumberFormat
Thuộc tính NumberFormat đại diện cho định dạng số của đối tượng Range (chú ý là chuỗi ký tự) như ví dụ sau:
Columns("A:A").NumberFormat="0.00%"
Đây là thuộc tính dạng read-write.

 

[handung107]

Một số phương thức (Method) của đối tượng Range

Một số phương thức (Method) của đối tượng Range​

Như bạn đã biết, phương thức sẽ thực hiện một hành động (action). Đối tượng Range có rất nhiều phương thức, nhưng bạn có thể chỉ cần biết một số phương thức chính thường hay dùng mà thôi.
Phương thức Select
Dùng phương thức Select để chọn một khỏang nhiều ô. Ví dụ sau đây chọn khỏang A1:A12 của sheet đang họat động (active)
Range("A1:A12").Select
Chú ý rằng trước khi bạn chọn một khỏang thuộc worksheet nào thì phải làm cho worksheet đó active trước nếu không sẽ có lỗi đáng tiếc xãy ra.
Sheets("Sheet1").Active
Range("A1:A12").Select
Hay bạn cũng có thể thay thế chỉ bằng một phát biểu như sau:
Sheets("Sheet1").Range("A1:A12").Select
Phương thức Copy và Paste
Bạn có thể sao chép hay dán trong VBA bằng cách dùng phương thức Copy hay Paste. Phương thức Copy được áp dụng cho đối tượng Range nhưng phương thức Paste thì dùng cho đối tượng Worksheet. Ví dụ sau sao chép khỏang A1:A12 và dán tại vị trí ô C1.
Sub CopyRange()
Range("A1:A12").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
End Sub
Ví dụ trên được ghi lại khi bạn thao tác. Bạn có thể viết ngắn lại như sau:
Sub CopyRange()
Range("A1:A12").Copy Range("C1")
End Sub
Bạn có thể tìm hiểu sâu hơn về hai phương thức này trong phần help.
Phương thức Clear
Phương thức Clear xóa nội dung của một khỏang và tất cả các định dạng của nó. Ví dụ bạn muốn xóa nội dung và định dạng của cột D ở worksheet hiện hành thì
Columns("D").Clear
Nếu bạn chỉ muốn xóa nội dung thì bạn có thể dùng phương thức ClearContents. Còn nếu bạn muốn xóa định dạng thôi thì bạn dùng phương thức ClearFormats.
Phương thức Delete
Phương thức Delete khác phương thức Clear ở chổ, nếu bạn xóa thì Excel sẽ tự động đưa các ô nằm bên dưới khỏang này lên điền đầy vào khỏang vừa bị xóa.
Rows("6:6").Delete
Khi bạn xóa một khỏang chứ không phải là xóa hẳn một hàng hay một cột thì Excel muốn biết bạn muốn di chuyển các ô còn lại vào điền đầy các ô vừa bị xóa theo hình thức nào.Ví dụ sau, sau khi xóa khỏang C6:C10, Excel sẽ di chuyển các ô bên phải qua bên trái.
Range("C6:C10").Delete xlToLeft
Trong ví dụ trên, xlToLeft là hằng số cho phương thức này. Bạn có thể tìm ra các hằng số khác bằng cách ghi lại theo tác bạn thực hiện.

 

[fozet]

bác có cuốn sách nào nói về VB for Applications không gởi cho em với. Em đang rất cần
mail to : dthongbk@gmail.com

 

[PhanTuHuong]

Bạn có thể vào đây xem:

http://www.giaiphapexcel.com/forum/showthread.php?t=427&page=1

Cần gì thì liên hệ với chị handung107

 

[daica]

Xin tai lieu VBA

Chào các bạn, mình mới biết đến diễn đàn này gần đây. Mình cảm thấy nó rất hữu ít cho mình.
Nhân tiện cho mình hỏi thăm làm sao liên lạc với chị handung được. Mình muốn xin các tài liệu về để học hỏi. Mình hiện đang ở HCM
Cám ơn rất nhiều

Trương An Nhiên
tanhiencic9@yahoo.com

 

[nvlhy071206]

Chào các bạn, mình mới biết đến diễn đàn này gần đây. Mình cảm thấy nó rất hữu ít cho mình.
Nhân tiện cho mình hỏi thăm làm sao liên lạc với chị handung được. Mình muốn xin các tài liệu về để học hỏi. Mình hiện đang ở HCM
Cám ơn rất nhiều

Trương An Nhiên
tanhiencic9@yahoo.com

Mình cũng như bạn Trương An Nhiên, cũng mới biết tới diễn đàn và rất thích Excel. Mình cũng đang tập tành với VBA nhưng không biết đọc sách nào cho hữu ích vì bây giờ vô nhà sách ngập đầu với sách tin học. Bác nào có tài liệu hay xin vui lòng chỉ giúp.
Địa chỉ mail của mình: nvlhy071206@gmail.com
Xin chân thành cảm ơn

 

[Pansy_flower]

Mình cũng như bạn Trương An Nhiên, cũng mới biết tới diễn đàn và rất thích Excel. Mình cũng đang tập tành với VBA nhưng không biết đọc sách nào cho hữu ích vì bây giờ vô nhà sách ngập đầu với sách tin học. Bác nào có tài liệu hay xin vui lòng chỉ giúp.
Địa chỉ mail của mình: nvlhy071206@gmail.com
Xin chân thành cảm ơn

Mời bạn qua đây đọc bài nha!.

 

[lenhu038]

minh đang theo giỏi mục này đây. Hay lăm. Mình hy vọng mình có thể hiểu về VBA.
Thanks

 

[phucdmctn]

Xin tai lieu VBA
Chào các bạn, mình mới biết đến diễn đàn này gần đây. Mình cảm thấy nó rất hữu ít cho mình.
Nhân tiện cho mình hỏi thăm làm sao liên lạc với chị handung được. Mình muốn xin các tài liệu về để học hỏi. Mình hiện đang ở HCM
Cám ơn rất nhiều

 

[dinhtuanbs]

Mình rất muốn học Lập Trình VBA với excel nhưng không có tài liệu. Xin Giúp mình viết câu lệnh cho các nút trong form. Câu lệnh cho Form tự hiển thị khi khới động Tập tin excel. Câu lệnh cho form ẩn đi và hiện lại.

 

[nghiaphuc]

Mình rất muốn học Lập Trình VBA với excel nhưng không có tài liệu. Xin Giúp mình viết câu lệnh cho các nút trong form. Câu lệnh cho Form tự hiển thị khi khới động Tập tin excel. Câu lệnh cho form ẩn đi và hiện lại.

Muốn tìm tài liệu về VBA thì bạn nhìn dòng chữ chạy ở đầu diễn đàn đó.
Nhân tiện, trả lời một số yêu cầu trước mắt của bạn:
1. Câu lệnh cho Form tự hiển thị khi khới động Tập tin excel: Bạn Sử dụng 1 trong 2 Sub như vầy:
- Sub đặt trong đối tượng ThisWorkbook:

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

hoặc:
- Sub đặt trong 1 Module nào đó:

Sub Auto_Open()
    UserForm1.Show
End Sub

2. Câu lệnh cho form ẩn đi và hiện lại:
- Ẩn form: UserForm1.Hide hoặc Me.Hide (nếu câu lệnh này dùng để ẩn Form chứa code này)
- Hiện form: UserForm1.Show hoặc Me.Show
- Đóng form (khác với ẩn form đấy nhé): Unload Me

 

[12C7_CG]

các bác cho em hỏi chút (em tìm trong diễn đàn mãi mà ko có kết quả)
chả là em có số a, b(biến số)
a=cells(1,1)
b=cells(1,2)
em muốn xóa row từ 11 đến a+10 và insert "b" row dười row 10 thì cú pháp như thế nào vậy ạ.