Bài viết này sẽ trình bày cách tạo Dải động trong Excel VBA.
Việc khai báo một phạm vi ô cụ thể dưới dạng một biến trong Excel VBA giới hạn chúng ta chỉ làm việc với những ô cụ thể đó. Bằng cách khai báo Dải động trong Excel, chúng tôi có được sự linh hoạt hơn nhiều đối với mã của chúng tôi và chức năng mà nó có thể thực hiện.
Tham chiếu các dãy và ô
Khi chúng tôi tham chiếu đối tượng Phạm vi hoặc Ô trong Excel, chúng tôi thường tham chiếu đến chúng bằng cách mã hóa cứng trong hàng và cột mà chúng tôi yêu cầu.
Thuộc tính phạm vi
Sử dụng Thuộc tính Phạm vi, trong các dòng mã ví dụ bên dưới, chúng ta có thể thực hiện các hành động trên phạm vi này, chẳng hạn như thay đổi màu của ô hoặc làm cho ô đậm.
| 12 | Phạm vi ("A1: A5"). Font.Color = vbRedPhạm vi ("A1: A5"). Font.Bold = True |
Thuộc tính ô
Tương tự, chúng ta có thể sử dụng Thuộc tính Ô để tham chiếu đến một phạm vi ô bằng cách tham chiếu trực tiếp đến hàng và cột trong thuộc tính ô. Hàng phải luôn là một số nhưng cột có thể là một số hoặc có thể là một chữ cái được đặt trong dấu ngoặc kép.
Ví dụ: địa chỉ ô A1 có thể được tham chiếu là:
| 1 | Ô (1,1) |
Hoặc
| 1 | Ô (1, "A") |
Để sử dụng Thuộc tính ô để tham chiếu một dải ô, chúng ta cần chỉ ra điểm bắt đầu của dải ô và phần cuối của dải ô.
Ví dụ đối với phạm vi tham chiếu A1: A6, chúng tôi có thể sử dụng cú pháp này dưới đây:
| 1 | Phạm vi (Ô (1,1), Ô (1,6) |
Sau đó, chúng tôi có thể sử dụng thuộc tính Cells để thực hiện các hành động trên phạm vi theo các dòng mã ví dụ bên dưới:
| 12 | Phạm vi (Ô (2, 2), Ô (6, 2)). Font.Color = vbRedPhạm vi (Ô (2, 2), Ô (6, 2)). Font.Bold = True |
Phạm vi động với các biến
Khi kích thước dữ liệu của chúng tôi thay đổi trong Excel (tức là chúng tôi sử dụng nhiều hàng và cột hơn mà phạm vi mà chúng tôi đã mã hóa), sẽ rất hữu ích nếu các phạm vi mà chúng tôi tham chiếu đến trong mã của chúng tôi cũng thay đổi. Sử dụng đối tượng Phạm vi ở trên, chúng tôi có thể tạo các biến để lưu trữ số hàng và số cột tối đa của vùng của trang tính Excel mà chúng tôi đang sử dụng và sử dụng các biến này để điều chỉnh động đối tượng Phạm vi trong khi mã đang chạy.
Ví dụ
| 1234 | Dim lRow dưới dạng số nguyênDim lCol dưới dạng số nguyênlRow = Range ("A1048576"). End (xlUp) .RowlCol = Range ("XFD1"). End (xlToLeft) .Column |
Hàng cuối cùng trong cột
Vì có 1048576 hàng trong trang tính, biến lRow sẽ đi đến cuối trang tính và sau đó sử dụng kết hợp đặc biệt của phím Kết thúc cộng với phím Mũi tên lên để chuyển đến hàng cuối cùng được sử dụng trong trang tính - điều này sẽ cung cấp cho chúng tôi số lượng hàng mà chúng tôi cần trong phạm vi của chúng tôi.
Cột cuối cùng trong hàng
Tương tự, lCol sẽ di chuyển đến Cột XFD là cột cuối cùng trong trang tính và sau đó sử dụng tổ hợp phím đặc biệt của phím Kết thúc cộng với phím Mũi tên trái để chuyển đến cột cuối cùng được sử dụng trong trang tính - điều này sẽ cung cấp cho chúng tôi số cột mà chúng tôi cần trong phạm vi của chúng tôi.
Do đó, để lấy toàn bộ phạm vi được sử dụng trong trang tính, chúng ta có thể chạy mã sau:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol As IntegerDim rng As RangelRow = Range ("A1048576"). End (xlUp) .Row'sử dụng lRow để giúp tìm cột cuối cùng trong phạm vilCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnĐặt rng = Phạm vi (Ô (1, 1), Ô (lRow, lCol))'msgbox để hiển thị cho chúng tôi phạm viMsgBox "Phạm vi là" & rng.AddressKết thúc Sub |
SpecialCells - LastCell
Chúng ta cũng có thể sử dụng phương thức SpecialCells của Đối tượng Phạm vi để lấy hàng và cột cuối cùng được sử dụng trong Trang tính.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol As IntegerDim rng As RangeDim rngBegin As RangeĐặt rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnĐặt rng = Phạm vi (Ô (1, 1), Ô (lRow, lCol))'msgbox để hiển thị cho chúng tôi phạm viMsgBox "Phạm vi là" & rng.AddressKết thúc Sub |
Đã sử dụng
Phương pháp Phạm vi Đã sử dụng bao gồm tất cả các ô có giá trị trong trang tính hiện tại.
| 123456 | Sub usedRangeExample ()Dim rng As RangeĐặt rng = ActiveSheet.UsedRange'msgbox để hiển thị cho chúng tôi phạm viMsgBox "Phạm vi là" & rng.AddressKết thúc Sub |
Hiện tại
Vùng hiện tại khác với usedRange ở chỗ nó xem xét các ô xung quanh ô mà chúng ta đã khai báo là phạm vi bắt đầu (tức là biến rngBegin trong ví dụ bên dưới), sau đó xem xét tất cả các ô được 'đính kèm' hoặc liên kết vào ô đã khai báo đó. Nếu một ô trống trong một hàng hoặc cột xảy ra, thì CurrentRegion sẽ ngừng tìm kiếm bất kỳ ô nào khác.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeDim rngBegin As RangeĐặt rngBegin = Range ("A1")Đặt rng = rngBegin.CurrentRegion'msgbox để hiển thị cho chúng tôi phạm viMsgBox "Phạm vi là" & rng.AddressKết thúc Sub |
Nếu chúng tôi sử dụng phương pháp này, chúng tôi cần đảm bảo rằng tất cả các ô trong phạm vi mà bạn yêu cầu được kết nối với không có hàng hoặc cột trống nào trong số chúng.
Dải ô được đặt tên
Chúng tôi cũng có thể tham chiếu các Dãy được Đặt tên trong mã của chúng tôi. Phạm vi được đặt tên có thể động cho đến nay khi dữ liệu được cập nhật hoặc chèn vào, Tên phạm vi có thể thay đổi để bao gồm dữ liệu mới.
Ví dụ này sẽ thay đổi phông chữ thành in đậm cho tên phạm vi “Tháng Giêng”
| 12345 | Sub RangeNameExample ()Dim rng as RangeĐặt rng = Range ("Tháng 1")rng.Font.Bold = = ĐúngKết thúc Sub |
Như bạn sẽ thấy trong hình bên dưới, nếu một hàng được thêm vào tên phạm vi, thì tên phạm vi sẽ tự động cập nhật để bao gồm hàng đó.
Sau đó, nếu chúng ta chạy lại mã ví dụ, phạm vi bị ảnh hưởng bởi mã sẽ là C5: C9 trong khi trong trường hợp đầu tiên, nó sẽ là C5: C8.
Những cái bàn
Chúng ta có thể tham chiếu các bảng (bấm để biết thêm thông tin về cách tạo và thao tác với các bảng trong VBA) trong mã của chúng ta. Khi dữ liệu bảng trong Excel được cập nhật hoặc thay đổi, mã tham chiếu đến bảng sau đó sẽ tham chiếu đến dữ liệu bảng được cập nhật. Điều này đặc biệt hữu ích khi đề cập đến các bảng Pivot được kết nối với nguồn dữ liệu bên ngoài.
Sử dụng bảng này trong mã của chúng tôi, chúng tôi có thể tham chiếu đến các cột của bảng theo tiêu đề trong mỗi cột và thực hiện các hành động trên cột theo tên của chúng. Khi các hàng trong bảng tăng hoặc giảm theo dữ liệu, phạm vi bảng sẽ điều chỉnh cho phù hợp và mã của chúng tôi sẽ vẫn hoạt động cho toàn bộ cột trong bảng.
Ví dụ:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Nhà cung cấp"). XóaKết thúc Sub |
