Lập trình với Excel

LÀM VIỆC VỚI RANGE

Hầu hết chương trình VBA của bạn có lẽ bao gồm những vùng (Range) của bảng tính. Khi bạn làm việc với đối tượng vùng, hãy chú ý các vấn đề sau đây:

• VBA không cần thiết phải chọn trước một vùng khi làm việc với nó.
• Nếu code của bạn chọn một vùng, bảng tính phải được kích hoạt.
• Việc ghi bằng Macro luôn phát sinh những đoạn code kém hiệu quả. Thường, bạn có thể tạo Macro bằng cách sử dụng việc ghi lại và sau đó, chỉnh sửa lại đoạn code để làm cho nó hiệu quả hơn.
• Thật là một ý tưởng hay nếu sử dụng một vùng được đặt tên trong code VBA của bạn. Ví dụ, việc sử dụng Range(Total) sẽ tốt hơn Range(D45). Trong trường hợp sau này, nếu bạn thêm vào một hàng phía trên hàng 45, bạn cần hiệu chỉnh lại macro để code dùng địa chỉ đúng là (D46).
• Khi chạy một macro đang làm việc trên một vùng chọn hiện hành, người dùng có lẽ đã chọn toàn bộ những hàng hoặc những cột. Trong hầu hết các trường hợp, bạn không muốn lặp qua mọi cell trong vùng chọn (điều này cần nhiều thời gian). Macro của bạn nên tạo một tập hợp con của vùng chọn chỉ gồm những Cell không rỗng.
• Excel cho phép chọn nhiều vùng. Thí dụ, bạn có thể chọn một vùng, nhấn Ctrl, và chọn một vùng khác. (Dĩ nhiên, bạn phải thực hiện việc này với chuột). Bạn có thể thử nghiệm việc này với Macro của bạn và thực hiện những hành động thích hợp.

Nếu bạn thích nhập vào những ví dụ của chính bạn hơn, hãy nhấn Alt+F11 để kích hoạt VBE. Sau đó, bạn Insert một module VBA, và đánh code vào. Hãy chắc chắn rằng Workbook được thiết lập một cách thích hơp. Thí dụ, nếu trong ví dụ sử dụng 2 bảng tính được đặt tên là Sheet1 và Sheet2, hãy chắc chắn rằng Workbook của bạn có các bảng tính có những tên kia.

Sao chép một vùng

Ví dụ sau dùng Macro ghi lại việc sao chép A1:A5 sang B1:B5, bạn sẽ thu được Macro như sau:

1 2 3 4 5 6 7 8

Sub CopyRange()          Range(“A1:A5”).Select          Selection.Copy          Range(“B1”).Select          ActiveSheet.Paste          Application.CutCopyMode = False End Sub

Macro này làm việc rất tốt, nhưng bạn có thể sao chép một vùng hiệu quả hơn macro này. Bạn có thể thu được kết quả tương tự với một macro chỉ có một dòng lệnh như dưới đây mà không cần chọn bất kỳ một cell nào như đoạn code sau:

1 2 3

Sub CopyRange2()          Range(“A1:A5).Copy Range(“B1”) End Sub

Thí dụ này cũng chứng minh rằng việc ghi macro không luôn phát sinh code hiệu quả nhất.


Sao chép một vùng có kích thước thay đổi

Trong nhiều trường hợp, bạn cần sao chép một vùng nhưng không biết chính xác kích thước hàng và cột của vùng. Ví dụ, bạn có thể có một workbook theo dõi việc bán hàng hàng tuần. Số lượng hàng thay đổi khi bạn thêm vào dữ liệu mới.
Hình sau trình bày một vùng của bảng tính. Vùng này bao gồm một số hàng và số hàng này có thể thay đổi từ ngày này sang ngày khác. Bởi vì bạn không biết chính xác địa chỉ của vùng tại bất kỳ thời điểm thực hiện, nên việc viết macro để sao chép vùng có thể là một thử thách. Bạn có đối mặt với thử thách không?



Macro dưới đây giải thích cách sao chép vùng này từ Sheet1 sang Sheet2 (bắt đầu tại ô A1). Nó sử dụng thuộc tính CurrentRegion, thuộc tính này trả kết quả là một vùng tương ứng với một khối ô xung quanh ô xác định. Trong trường hợp này, đó là ô A1.

1 2 3 4 5 6 7 8 9

Sub CopyCurrentRegion()          Range(“A1”).CurrentRegion.Copy          Sheets(“Sheet2”).Select          Range(“A1”).Select          ActveSheet.Paste          Sheets(“Sheet1”).Select          Application.CutCopyMode = False End Sub

Sử dụng thuộc tính CurrentRegion tương đương với việc chọn lệnh Edit / GoTo, nhấn nút Special, và chọn tuỳ chọn Current Region. Để xem công việc này thế nào, hãy ghi lại macro các hành động của bạn trong khi bạn thao tác lệnh đó. Thông thường, CurrentRegion gồm một khối ô hình chữ nhật được bao quanh bởi một hay nhiều hàng hay cột trống.
Macro dưới đây cũng thực hiện được công việc trên bằng phương thức Copy.

1 2 3 4 5

Sub CopyCurrentRegion2()          Range(“A1”).CurrentRegion.Copy Sheets(“Sheet2”).Range(“A1”)          Application.CutCopyMode = False End Sub

Chọn đến cuối hàng hay cột

Có lẽ bạn có thói quen sử dụng tổ hợp phím <Ctrl + Shift + phím mũi tên phải> và <Ctrl + Shift + phím mũi tên xuống dưới> để chọn một dãy từ ô hiện hành đến cuối hàng hay cột. Không có gì ngạc nhiên, bạn có thể viết những macro để thực hiện những kiểu chọn lựa này.

Bạn có thể sử dụng thuộc tính CurrentRegion để chọn toàn bộ khối các ô. Nhưng cái gì sẽ xảy ra nếu bạn muốn chọn một cột trong khối các ô đó? Thật may mắn, VBA có kèm theo loại hoạt động này. Thủ tục VBA sau đây sẽ chọn một vùng bắt đầu từ ô hiện hành và kéo xuống dưới đến ngay ô phía trên ô trống đầu tiên của cột. Sau khi chọn xong vùng này, bạn có thể thực hiện bất kỳ việc gì bạn muốn – sao chép vùng, di chuyển vùng, định dạng vùng,…

1 2 3 4

Sub SelectDown()          Range(ActiveCell, ActiveCell.End(xlDown)).Select End Sub

Ví dụ sau sử dụng phương thức End của đối tượng ô hiện hành, mà kết quả thu được là đối tượng vùng. Phương thức End cần một trong các đối số sau: 

- xlUp
- xlDown
- xlToLeft
- xlToRight

Macro sau đây sẽ định dạng chữ đậm đối với một vùng có kích thườc thay đổi từ ô hiện hành đến ô trước ô trống trong cột hiện hành.

1 2 3

Sub MakeBold()         Range(ActiveCell. ActiveCell.End(xlDown)).Font.Bold = True End Sub

Chọn một hàng hay một cột

Thủ tục sau sẽ chon một cột có chứa ô hiện hành. Nó sử dụng thuôc tính EntireColumn, thuộc tính này trả về kết quả là đối tượng vùng gồm toàn bộ một cột.

1 2 3

Sub SelectColumn()          ActiveCell.EntireColumn.Select End Sub

VBA cũng cung cấp thuộc tính EntireRow, thuộc tính này trả về kết quả là một đối tượng vùng gồm toàn bộ một hàng.

1 2 3 4

Sub SelectRow()          ActiveCell.EntireRow.Select End Sub

Di chuyển một vùng

Bạn có thể di chuyển một vùng bằng cách cắt vùng dữ liệu vào bộ nhớ (clipboard) và sau đó dán nó vào một nơi khác. Nếu bạn ghi lại hành động của bạn trong khi bạn thực hiện thao tác di chuyển vùng này, việc ghi macro phát sinh đoạn code như dưới đây:

1 2 3 4 5 6

Sub MoveRange()          Range(“A1:C6”).Select          Selection.Cut          Range(“A10”).Select          ActiveSheet.Paste End Sub

Cũng như ví dụ về việc sao chép vùng, đoạn mã sinh ra khi ghi macro không phải là cách hiệu quả nhất để di chuyển một vùng các ô. Thật vậy, bạn có thể di chuyển một vùng với duy nhất 1 dòng lệnh VBA như sau:

1 2 3 4

Sub MoveRange2()          Range("A1:C6").Cut Range("A10") End Sub

Macro này sử dụng phương thức Cut và khai báo vùng đích đến. Cũng để ý rằng vùng nguồng cũng không được chọn. Con trỏ ô giữ nguyên vị trí ban đầu của nó.

Lặp qua một vùng một cách hiệu quả

Nhiều Macro thực hiện thao tác trên mỗi ô của vùng, hoặc chúng có thể thực hiện những tác động chọn lọc dựa trên nội dung của mỗi ô. Những Macro này luôn luôn bao gồm vòng lặp For-Next xử lý trên mỗi ô của vùng.

Ví dụ dưới đây giải thích việc lặp qua một vùng ô như thế nào. Trong trường hợp này, vùng là vùng chọn hiện hành. Một biến số được đặt tên là Cell tham chiếu đến ô đang được xử lý. Vòng lặp For-Next sẽ duyệt qua các ô trong vùng đang chọn và sẽ thay đổi màu nền của ô nếu ô chứa giá trị dương.

1 2 3 4 5 6

Sub ProcessCells()          Dim Cell As Range          For Each Cell In Selection                  If Cell.Value >0 Then Cell.Interior.ColorIndex = 6          Next Cell End Sub

Ví dụ trên làm việc đúng như mong đợi, nhưng cái gì sẽ xảy ra nếu vùng chọn là toàn bộ cột hay hàng? Trong trường hợp như thế, macro dường như thực hiện mãi mãi bởi vì nó lặp qua mỗi ô của vùng chọn – ngay cả những ô trống. Để macro hiệu quả hơn, bạn cần thêm điều kiện cho quá trình lặp và chỉ thực hiện việc thay màu ô cho những ô không trống.

Thủ tục dưới đây sử dụng phương thức SpecialCells. Thủ tục này sử dụng từ khoá Set để tạo 2 đối tượng mới: một tập con của vùng đang chọn bao gồm các ô chứa hằng số, và một tập con của vùng chọn bao gồm các ô chứa công thức. Thủ tục xử lý trong mỗi tập con này sẽ hiệu quả hơn vì bỏ qua những ô trống. Một sự khéo léo thú vị, phải không bạn?

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

Sub SkipBlanks()     Dim ConstantCells As Range     Dim FormulaCells As Range     Dim cell As Range           'Ignore errors     On Error Resume Next           Cells.ClearFormats           'Process the constants     Set ConstantCells = Selection.SpecialCells(xlConstants)     For Each cell In ConstantCells         If cell.Value > 10 Then             cell.Interior.ColorIndex = 15             cell.Font.Color = vbRed         End If     Next cell         'Process the formulas     Set FormulaCells = Selection.SpecialCells(xlFormulas)     For Each cell In FormulaCells         If cell.Value > 20 Then             cell.Interior.ColorIndex = 16             cell.Font.Color = vbBlue         End If     Next cell End Sub

Thủ tục SkipBlanks sẽ chạy nhanh hơn thủ tục trước bất chấp bạn chọn cái gì. Ví dụ, bạn có thể chọn một vùng hoặc tất cả các cột trong một vùng, hoặc tất cả các hàng trong một vùng, hoặc ngay cả toàn bộ bảng tính. Đây là một sự cải thiện rất lớn so với thủ tục ProcessCells được trình bày ở phần trước. Hãy chú ý rằng tôi sử dụng dòng lệnh dưới đây trong code này:

1	On Error Resume Next

Dòng lệnh này để Excel bỏ qua bất kỳ các lỗi xảy ra ở dòng lệnh nào và nhảy sang dòng lệnh kế tiếp. Dòng lệnh này cần thiết vì phương thức SpecialCells tạo ra lỗi nếu không có những ô đủ điều kiện.

Sử dụng phương thức SpecialCells tương đương với việc chọn lệnh Home | Find & Select | GoTo(Ctrl+G), nhấn nút Special và chọn tuỳ chọn Constants hay Formulas. Để hiểu hơn, bạn hãy ghi macro lại các hành động này với những sự lựa chọn khác nhau.

Xác định kiểu của vùng đang chọn

Nếu bạn thiết kế Macro của bạn để làm việc với vùng chọn, macro phải có thể xác định được một vùng thực sự được chọn hay không. Nếu đang chọn là một đối tượng khác vùng (như một đồ thị hay một hình vẽ), macro sẽ bị lỗi. Thủ tục dưới đây sử dụng hàm VBA TypeName để nhận ra kiểu đối tượng đang được chọn.

1 2 3 4

Sub SelectionType()          MsgBox TypeName(Selection) End Sub

Nếu bạn muốn macro của bạn chỉ làm việc với vùng, bạn có thể thêm câu lệnh If để kiểm tra đối tượng đang chọn có phải là vùng hay không. Ví dụ sau minh họa cách thức kiểm tra đối tượng chọn, nếu không là vùng sẽ thoát thủ tục nếu đúng là vùng thì sẽ thực hiện các lệnh tiếp theo.

1 2 3 4 5 6 7 8 9 10

Sub CheckSelection()          If TypeName(Selection) <> “Range” Then                   MsgBox “Select a Range”                   Exit Sub          End If          ...          Những dòng lệnh khác ở đây          ... End Sub

Nhận biết nhiều vùng đang chọn

Như bạn biết, Excel cho phép chọn nhiều vùng bằng cách nhấn phím Ctrl và chọn các đối tượng hoặc các vùng. Việc này gây ra nhiều vấn đề với một vài Macro. Ví dụ, bạn không thể sao chép nhiều vùng gồm mhững vùng không kề cận nhau.

Macro dưới đây minh họa cách xác định người dùng có chọn nhiều vùng hay không để từ đó có phương án xử lý thích hợp.

1 2 3 4 5 6 7 8 9 10

Sub MultipleSelection()          If Selection.Areas.Count > 1 Then                  MsgBox “Ban dang chon nhieu vung!”                  Exit Sub          End If          ...          Những dòng lệnh khác ở đây          ... End Sub

Ví dụ này sử dụng phương thức Areas, phương thức này sẽ cho kết quả là một tập hợp các đối tượng trong vùng chọn. Thuộc tính Count cho kết quả là số lượng các đối tượng trong tập hợp.

Xem ví dụ tại đây