Phạm vi và ô trong VBA
Bảng tính Excel lưu trữ dữ liệu trong Ô. Các ô được sắp xếp thành Hàng và Cột. Mỗi ô có thể được xác định bằng giao điểm của hàng và cột của ô đó (Ví dụ: B3 hoặc R3C2).
Phạm vi Excel đề cập đến một hoặc nhiều ô (ví dụ: A3: B4)
Địa chỉ ô
Ký hiệu A1
Trong ký hiệu A1, một ô được tham chiếu bằng ký tự cột của nó (từ A đến XFD), theo sau là số hàng của nó (từ 1 đến 1,048,576).
Trong VBA, bạn có thể tham chiếu đến bất kỳ ô nào bằng cách sử dụng Đối tượng phạm vi.
| 123456789 | 'Tham khảo ô B4 trên trang tính hiện đang hoạt độngPhạm vi MsgBox ("B4")'Tham khảo ô B4 trên trang tính có tên' Dữ liệu 'MsgBox Worksheets ("Dữ liệu"). Phạm vi ("B4")'Tham khảo ô B4 trên trang tính có tên' Dữ liệu 'trong một sổ làm việc MỞ khác'có tên là' Dữ liệu của tôi 'MsgBox Workbooks ("My Data"). Worksheets ("Data"). Range ("B4") |
Ký hiệu R1C1
Trong ký hiệu R1C1, một ô được tham chiếu bởi R theo sau là Số hàng rồi đến chữ cái ‘C’ theo sau là Số cột. ví dụ B4 trong ký hiệu R1C1 sẽ được tham chiếu bởi R4C2. Trong VBA, bạn sử dụng Đối tượng ô để sử dụng ký hiệu R1C1:
| 12 | 'Tham khảo ô R [6] C [4] tức là D6Ô (6, 4) = "D6" |
Phạm vi ô
Ký hiệu A1
Để tham chiếu đến nhiều ô, hãy sử dụng dấu “:” giữa địa chỉ ô bắt đầu và địa chỉ ô cuối cùng. Phần sau sẽ tham chiếu đến tất cả các ô từ A1 đến D10:
| 1 | Phạm vi ("A1: D10") |
Ký hiệu R1C1
Để tham chiếu đến nhiều ô, hãy sử dụng dấu “,” giữa địa chỉ ô bắt đầu và địa chỉ ô cuối cùng. Phần sau sẽ tham chiếu đến tất cả các ô từ A1 đến D10:
| 1 | Phạm vi (Ô (1, 1), Ô (10, 4)) |
Ghi vào ô
Để ghi giá trị vào một ô hoặc nhóm ô liền kề, chỉ cần tham chiếu đến dải ô, đặt dấu = và sau đó ghi giá trị sẽ được lưu trữ:
| 12345678910 | 'Lưu F5 trong ô có Địa chỉ F6Phạm vi ("F6") = "F6"'Lưu trữ E6 trong ô có Địa chỉ R [6] C [5] tức là E6Ô (6, 5) = "E6"'Lưu trữ A1: D10 trong phạm vi A1: D10Phạm vi ("A1: D10") = "A1: D10"' hoặcPhạm vi (Ô (1, 1), Ô (10, 4)) = "A1: D10" |
Đọc từ các ô
Để đọc các giá trị từ các ô, chỉ cần tham chiếu đến biến để lưu trữ các giá trị, đặt dấu = và sau đó tham chiếu đến phạm vi sẽ được đọc:
| 1234567891011 | Dim val1Dim val2'Đọc từ ô F6val1 = Phạm vi ("F6")'Đọc từ ô E6val2 = Ô (6, 5)MsgBox val1Msgbox val2 |
Lưu ý: Để lưu trữ các giá trị từ một phạm vi ô, bạn cần sử dụng Mảng thay vì một biến đơn giản.
Các ô không liền kề
Để tham chiếu đến các ô không liền kề, hãy sử dụng dấu phẩy giữa các địa chỉ ô:
| 123456 | 'Lưu trữ 10 trong các ô A1, A3 và A5Phạm vi ("A1, A3, A5") = 10'Lưu trữ 10 trong các ô A1: A3 và D1: D3)Phạm vi ("A1: A3, D1: D3") = 10 |
Giao điểm của các ô
Để tham chiếu đến các ô không liền kề, hãy sử dụng khoảng cách giữa các địa chỉ ô:
| 123 | 'Cửa hàng' Col D 'trong D1: D10'thường gặp giữa A1: D10 và D1: F10Phạm vi ("A1: D10 D1: G10") = "Col D" |
Chênh lệch từ một ô hoặc dải ô
Sử dụng hàm Offset, bạn có thể di chuyển tham chiếu từ một Phạm vi nhất định (ô hoặc nhóm ô) theo số_mạch_mạch và số_mạch_số đã chỉ định.
Cú pháp bù đắp
Range.Offset (number_of_rows, number_of_columns)
Chênh lệch từ một ô
| 12345678910111213141516 | 'OFFSET từ ô A1'Tham chiếu đến chính ô'Di chuyển 0 hàng và 0 cộtPhạm vi ("A1"). Offset (0, 0) = "A1"'Di chuyển 1 hàng và 0 cộtPhạm vi ("A1"). Offset (1, 0) = "A2"'Di chuyển 0 hàng và 1 cộtPhạm vi ("A1"). Offset (0, 1) = "B1"'Di chuyển 1 hàng và 1 cộtPhạm vi ("A1"). Offset (1, 1) = "B2"'Di chuyển 10 hàng và 5 cộtPhạm vi ("A1"). Offset (10, 5) = "F11" |
Chênh lệch từ một Phạm vi
| 123 | 'Di chuyển Tham chiếu đến Phạm vi A1: D4 theo 4 hàng và 4 cột'Tham chiếu mới là E5: H8Phạm vi ("A1: D4"). Offset (4,4) = "E5: H8" |
Đặt tham chiếu cho một phạm vi
Để gán một phạm vi cho một biến phạm vi: khai báo một biến kiểu Phạm vi sau đó sử dụng lệnh Set để đặt nó thành một phạm vi. Xin lưu ý rằng bạn phải sử dụng lệnh SET vì RANGE là một đối tượng:
| 12345678 | 'Khai báo một biến Phạm viLàm mờ myRange dưới dạng Phạm vi'Đặt biến thành phạm vi A1: D4Đặt myRange = Range ("A1: D4")'In $ A $ 1: $ D $ 4MsgBox myRange.Address |
Thay đổi kích thước một phạm vi
Phương thức thay đổi kích thước của đối tượng Phạm vi thay đổi kích thước của phạm vi tham chiếu:
| 1234567 | Dim myRange As Range'Phạm vi thay đổi kích thướcĐặt myRange = Range ("A1: F4")'In $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address |
Ô trên cùng bên trái của phạm vi Đã thay đổi kích thước giống với ô trên cùng bên trái của phạm vi ban đầu
Thay đổi kích thước cú pháp
Range.Resize (number_of_rows, number_of_columns)
OFFSET so với Thay đổi kích thước
Offset không thay đổi kích thước của phạm vi nhưng di chuyển nó theo số hàng và cột được chỉ định. Thay đổi kích thước không thay đổi vị trí của phạm vi ban đầu nhưng thay đổi kích thước thành số hàng và cột được chỉ định.
Tất cả các ô trong trang tính
Đối tượng Cells tham chiếu đến tất cả các ô trong trang tính (1048576 hàng và 16384 cột).
| 12 | 'Xóa tất cả các ô trong trang tínhTế bào. |
Đã sử dụng
Thuộc tính usedRange cung cấp cho bạn phạm vi hình chữ nhật từ ô được sử dụng trên cùng bên trái đến ô được sử dụng dưới cùng bên phải của trang tính hiện hoạt.
| 1234567 | Dim ws As WorksheetĐặt ws = ActiveSheet'$ B $ 2: $ L $ 14 nếu L2 là ô đầu tiên có giá trị bất kỳ'và L14 là ô cuối cùng có bất kỳ giá trị nào trên' bảng hoạt độngDebug.Print ws.UsedRange.Address |
Hiện tại
Thuộc tính CurrentRegion cung cấp cho bạn phạm vi hình chữ nhật liền kề từ ô trên cùng bên trái đến ô được sử dụng dưới cùng bên phải chứa ô / phạm vi được tham chiếu.
| 1234567891011 | Dim myRange As RangeĐặt myRange = Range ("D4: F6")'Bản in $ B $ 2: $ L $ 14'Nếu có một đường dẫn được lấp đầy từ D4: F16 đến B2 VÀ L14Debug.Print myRange.CurrentRegion.Address'Bạn cũng có thể tham khảo một ô bắt đầu duy nhấtĐặt myRange = Range ("D4") 'In $ B $ 2: $ L $ 14 |
Thuộc tính phạm vi
Bạn có thể nhận được Địa chỉ, số hàng / cột của một ô và số hàng / cột trong một phạm vi như được cung cấp bên dưới:
| 123456789101112131415161718192021 | Dim myRange As RangeĐặt myRange = Range ("A1: F10")'In $ A $ 1: $ F $ 10Debug.Print myRange.AddressĐặt myRange = Range ("F10")'In 10 cho Hàng 10Debug.Print myRange.Row'In 6 cho Cột FDebug.Print myRange.ColumnĐặt myRange = Range ("E1: F5")'In 5 cho số Hàng trong phạm viDebug.Print myRange.Rows.Count'In 2 cho số cột trong phạm viDebug.Print myRange.Columns.Count |
Ô cuối cùng trong trang tính
Bạn có thể dùng Rows.Count và Cột. Số lượng tài sản với Tế bào đối tượng để lấy ô cuối cùng trên trang tính:
| 1234567891011 | 'In số hàng cuối cùng'Bản in 1048576Debug.Print "Hàng trong trang tính:" & Rows.Count'In số cột cuối cùng'Bản in 16384Debug.Print "Các cột trong trang tính:" & Columns.Count'In địa chỉ của ô cuối cùng'Bản in $ XFD $ 1048576Debug.Print "Address of Last Cell in the sheet:" & Cells (Rows.Count, Columns.Count) |
Số hàng được sử dụng cuối cùng trong một cột
Thuộc tính END đưa bạn đến ô cuối cùng trong phạm vi và End (xlUp) đưa bạn đến ô được sử dụng đầu tiên từ ô đó.
| 123 | Dim lastRow As LonglastRow = Cells (Rows.Count, "A"). End (xlUp) .Row |
Số cột được sử dụng cuối cùng trong một hàng
| 123 | Dim lastCol As LonglastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column |
Thuộc tính END đưa bạn đến ô cuối cùng trong phạm vi và End (xlToLeft) đưa bạn sang ô được sử dụng đầu tiên từ ô đó.
Bạn cũng có thể sử dụng các thuộc tính xlDown và xlToRight để điều hướng đến các ô được sử dụng dưới cùng hoặc bên phải đầu tiên của ô hiện tại.
Thuộc tính ô
Tài sản chung
Đây là mã để hiển thị Thuộc tính ô thường được sử dụng
| 12345678910111213141516171819202122 | Làm mờ ô dưới dạng phạm viĐặt ô = Phạm vi ("A1")cell.ActivateDebug.Print cell.Address'In $ A $ 1Debug.Print cell.Value'Bản in 456' Địa chỉ nhàDebug.Print cell.Formula'Bản in = SUM (C2: C3)' Bình luậnDebug.Print cell.Comment.Text' Phong cáchDebug.Print cell.Style'Định dạng ôDebug.Print cell.DisplayFormat.NumberFormat |
Phông chữ ô
Đối tượng Cell.Font chứa các thuộc tính của Phông chữ Ô:
| 1234567891011121314151617181920 | Làm mờ ô dưới dạng phạm viĐặt ô = Phạm vi ("A1")'Chữ thường, chữ nghiêng, chữ in đậm và chữ in đậmcell.Font.FontStyle = "Chữ in đậm"' Giống nhưcell.Font.Bold = Truecell.Font.Italic = True'Đặt phông chữ thành Chuyển phát nhanhcell.Font.FontStyle = "Chuyển phát nhanh"'Đặt màu phông chữcell.Font.Color = vbBlue' hoặccell.Font.Color = RGB (255, 0, 0)'Đặt kích thước phông chữcell.Font.Size = 20 |
Sao chép và dán
Dán tất cả
Dải / Ô có thể được sao chép và dán từ vị trí này sang vị trí khác. Đoạn mã sau sao chép tất cả các thuộc tính của dải ô nguồn sang dải ô đích (tương đương với CTRL-C và CTRL-V)
| 1234567 | 'Bản sao đơn giảnPhạm vi ("A1: D20"). Sao chépWorksheets ("Sheet2"). Phạm vi ("B10"). Dán'hoặc'Sao chép từ Trang tính hiện tại sang trang tính có tên' Trang tính2 'Phạm vi ("A1: D20"). Sao chép đích: = Trang tính ("Trang tính2"). Phạm vi ("B10") |
Dán đặc biệt
Các thuộc tính đã chọn của dải nguồn có thể được sao chép tới đích bằng cách sử dụng tùy chọn PASTESPECIAL:
| 123 | 'Dán phạm vi chỉ làm Giá trịPhạm vi ("A1: D20"). Sao chépWorksheets ("Sheet2"). Range ("B10"). PasteSpecial Paste: = xlPasteValues |
Dưới đây là các tùy chọn khả thi cho tùy chọn Dán:
| 12345678910111213 | 'Dán các loại đặc biệtxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Nội dung AutoFit
Kích thước của các hàng và cột có thể được thay đổi để phù hợp với nội dung bằng cách sử dụng mã dưới đây:
| 12345 | 'Thay đổi kích thước của các hàng từ 1 đến 5 để vừa với nội dungHàng ("1: 5"). AutoFit'Thay đổi kích thước của Cột A thành B để vừa với nội dungCác cột ("A: B"). AutoFit |
Các ví dụ về phạm vi khác
Bạn nên sử dụng Trình ghi Macro trong khi thực hiện hành động cần thiết thông qua GUI. Nó sẽ giúp bạn hiểu các tùy chọn khác nhau có sẵn và cách sử dụng chúng.
Cho mỗi
Việc lặp qua một phạm vi dễ dàng hơn bằng cách sử dụng Cho mỗi xây dựng như hiển thị bên dưới:
| 123 | Đối với mỗi ô trong phạm vi ("A1: B100")'Làm điều gì đó với tế bàoÔ tiếp theo |
Tại mỗi lần lặp của vòng lặp, một ô trong phạm vi được gán cho biến c và các câu lệnh trong vòng lặp For được thực thi cho ô đó. Vòng lặp thoát khi tất cả các ô được xử lý.
Loại
Sắp xếp là một phương thức của đối tượng Phạm vi. Bạn có thể sắp xếp một phạm vi bằng cách chỉ định các tùy chọn để sắp xếp thành Phạm vi. Đoạn mã dưới đây sẽ sắp xếp các cột A: C dựa trên khóa trong ô C2. Thứ tự sắp xếp có thể là xlAscending hoặc xlDescending. Header: = xl Có nên được sử dụng nếu hàng đầu tiên là hàng tiêu đề.
| 12 | Các cột ("A: C"). Phím sắp xếp1: = Phạm vi ("C2"), _order1: = xlAscending, Header: = xlYes |
Tìm thấy
Tìm cũng là một phương thức của Đối tượng Phạm vi. Nó tìm ô đầu tiên có nội dung phù hợp với tiêu chí tìm kiếm và trả về ô dưới dạng đối tượng Phạm vi. Nó trở lại Không nếu không có kết quả phù hợp.
Sử dụng FindNext (hoặc FindPrevious) để tìm lần xuất hiện tiếp theo (trước đó).
Mã sau sẽ thay đổi phông chữ thành "Arial Black" cho tất cả các ô trong phạm vi bắt đầu bằng "John":
| 12345 | Đối với mỗi c Trong phạm vi ("A1: A100")Nếu c Thích "John *" thìc.Font.Name = "Arial Black"Kết thúc nếuTiếp theo c |
Mã sau sẽ thay thế tất cả các lần xuất hiện từ "Để kiểm tra" thành "Đạt" trong phạm vi được chỉ định:
| 12345678910 | Với Phạm vi ("a1: a500")Đặt c = .Find ("Để kiểm tra", LookIn: = xlValues)Nếu không c Không có gì thìfirstaddress = c.AddressLàmc.Value = "Đạt"Đặt c = .FindNext (c)Loop While Not c Is Nothing And c.Kết thúc nếuKết thúc với |
Điều quan trọng cần lưu ý là bạn phải chỉ định một phạm vi để sử dụng FindNext. Ngoài ra, bạn phải cung cấp một điều kiện dừng nếu không vòng lặp sẽ thực thi mãi mãi. Thông thường, địa chỉ của ô đầu tiên được tìm thấy được lưu trữ trong một biến và vòng lặp sẽ dừng lại khi bạn đến ô đó một lần nữa. Bạn cũng phải kiểm tra trường hợp không tìm thấy gì để dừng vòng lặp.
Địa chỉ phạm vi
Sử dụng Range.Address để lấy địa chỉ trong Kiểu A1
| 123 | Dãy MsgBox ("A1: D10"). Địa chỉ' hoặcDebug.Print Range ("A1: D10"). Địa chỉ |
Sử dụng xlReferenceStyle (mặc định là xlA1) để nhận các tiện ích bổ sung theo kiểu R1C1
| 123 | Phạm vi MsgBox ("A1: D10"). Địa chỉ (Kiểu tham chiếu: = xlR1C1)' hoặcDebug.Print Range ("A1: D10"). Địa chỉ (ReferenceStyle: = xlR1C1) |
Điều này hữu ích khi bạn xử lý các phạm vi được lưu trữ trong các biến và chỉ muốn xử lý cho một số địa chỉ nhất định.
Phạm vi thành Mảng
Việc chuyển một dải ô sang một mảng và sau đó xử lý các giá trị sẽ nhanh hơn và dễ dàng hơn. Bạn nên khai báo mảng là Biến thể để tránh tính toán kích thước cần thiết để điền phạm vi trong mảng. Thứ nguyên của mảng được đặt để khớp với số lượng giá trị trong phạm vi.
| 123456789 | Dim DirArray làm biến thể'Lưu trữ các giá trị trong phạm vi vào MảngDirArray = Range ("a1: a5"). Giá trị'Vòng lặp để xử lý các giá trịĐối với mỗi c Trong DirArrayDebug.Print cKế tiếp |
Mảng đến Phạm vi
Sau khi xử lý, bạn có thể ghi Mảng trở lại một Dải. Để viết Mảng trong ví dụ trên thành một Dải, bạn phải chỉ định một Dải có kích thước phù hợp với số phần tử trong Mảng.
Sử dụng mã bên dưới để ghi Mảng vào phạm vi D1: D5:
| 123 | Phạm vi ("D1: D5"). Giá trị = DirArrayPhạm vi ("D1: H1"). Giá trị = Application.Transpose (DirArray) |
Xin lưu ý rằng bạn phải Chuyển đổi Mảng nếu bạn viết nó thành một hàng.
Tổng phạm vi
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Phạm vi ("A1: A10"))Debug.Print SumOfRange |
Bạn có thể sử dụng nhiều hàm có sẵn trong Excel trong mã VBA của mình bằng cách chỉ định Application.WorkSheetFunction. trước Tên hàm như trong ví dụ trên.
Dải đếm
| 1234567 | 'Đếm số ô có số trong phạm viCountOfCells = Application.WorksheetFunction.Count (Phạm vi ("A1: A10"))Debug.Print CountOfCells'Đếm số ô không trống trong phạm viCountOfNonBlankCells = Application.WorksheetFunction.CountA (Phạm vi ("A1: A10"))Debug.Print CountOfNonBlankCells |
Viết bởi: Vinamra Chandra
