Tham chiếu cột biến excel
|
Bộ sưu tập là một phần rất quan trọng của VBA. Nếu bạn đã sử dụng ngôn ngữ trong một khoảng thời gian bất kỳ thì bạn sẽ sử dụng Bộ sưu tập. Những cái phổ biến nhất là các bộ sưu tập Workbooks, Worksheets, Range và Cells Show
Đoạn mã sau đây cho thấy một số ví dụ về việc sử dụng bộ sưu tập VBA Workbooks ' Workbooks is a collection of all open workbooks ' Count is the number of workbooks in the collection Debug.Print Workbooks.Count ' Print the full name of the workbook called Example.xlsm Debug.Print Workbooks("Example.xlsm").FullName ' Print the full name of the workbook that was opened second Debug.Print Workbooks(2).FullName Bộ sưu tập tương tự như mảng, vì vậy, điều quan trọng là phải hiểu chúng là gì và khác với mảng như thế nào
Tải xuống mã
Hội thảo trên web về Bộ sưu tậpNếu bạn là thành viên của trang web, hãy nhấp vào hình ảnh bên dưới để xem hội thảo trên web (Ghi chú. Các thành viên của trang web có quyền truy cập vào kho lưu trữ hội thảo trên web đầy đủ. )
Bộ sưu tập là gì?Bộ sưu tập và mảng đều được sử dụng để nhóm các biến. Cả hai đều lưu trữ một tập hợp các mặt hàng tương tự e. g. một danh sách các điểm sinh viên hoặc tên quốc gia. Sử dụng tập hợp hoặc mảng cho phép bạn thao tác nhanh chóng và dễ dàng với một số lượng lớn các mục Trong bài đăng của tôi trên tôi đã giải thích một cách đơn giản mảng là gì và tại sao chúng lại hữu ích như vậy. Tôi sẽ tóm tắt ngắn gọn thông tin này ở đây
Nếu bạn đang lưu trữ điểm của một học sinh thì bạn có thể dễ dàng thực hiện việc này bằng cách sử dụng một biến duy nhất Dim mark As Long mark = sheetMarks.Range("A1") Tuy nhiên, hầu hết thời gian bạn sẽ có nhiều hơn một học sinh để giải quyết. Hãy tưởng tượng bạn muốn lưu trữ điểm của 100 sinh viên. Nếu không sử dụng tập hợp hoặc mảng, bạn sẽ cần tạo hàng trăm biến – một biến để lưu điểm cho mỗi học sinh
Một vấn đề khác là bạn phải sử dụng các biến này một cách riêng lẻ. Nếu bạn muốn lưu trữ 100 điểm thì bạn cần một dòng mã mỗi khi bạn muốn lưu trữ giá trị cho một biến ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")
Như bạn có thể thấy trong ví dụ trên, viết mã như thế này có nghĩa là hàng trăm dòng mã lặp đi lặp lại. Khi bạn sử dụng tập hợp hoặc mảng, bạn chỉ cần khai báo một biến. Sử dụng vòng lặp với tập hợp hoặc với mảng có nghĩa là bạn chỉ cần một dòng để thêm hoặc đọc giá trị
Nếu chúng ta viết lại ví dụ trên bằng cách sử dụng một bộ sưu tập thì chúng ta chỉ cần một vài dòng mã ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next
Bộ sưu tập Vs Mảng?Chúng tôi đã xem xét các bộ sưu tập và mảng có điểm chung. Vậy sự khác biệt là gì và tại sao lại sử dụng cái này hơn cái kia? Sự khác biệt chính là với một mảng bạn thường đặt kích thước một lần. Điều này có nghĩa là bạn biết kích thước trước khi bắt đầu thêm các phần tử. Hãy để tôi giải thích điều này với một ví dụ
Thí dụ. Trường hợp một mảng tốt hơnHãy tưởng tượng bạn có một bảng tính điểm của học sinh với một học sinh trên mỗi hàng
điểm học sinh
Bạn muốn lưu trữ thông tin về từng sinh viên. Trong ví dụ này, bạn có thể dễ dàng đếm số hàng để lấy số học sinh. Nói cách khác, bạn biết trước số lượng mặt hàng Trong mã ví dụ, bạn có thể thấy rằng chúng tôi lấy số học sinh bằng cách đếm các hàng. Sau đó chúng ta có thể sử dụng điều này để tạo một mảng có kích thước chính xác ' Get last row - this is the number of students Dim lStudentCount As Long lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Create array of correct size Dim arr() As Long ReDim arr(1 To lStudentCount)
Bây giờ chúng ta hãy xem ví dụ thứ hai mà chúng ta không biết trước số lượng mục
Ví dụ Trường hợp Bộ sưu tập Tốt hơnTrong ví dụ này, chúng tôi có cùng một bảng tính học sinh nhưng lần này chúng tôi chỉ muốn học sinh với một tiêu chí nhất định. Ví dụ: chỉ những học sinh đến từ Hoa Kỳ hoặc Anh học Toán hoặc Lịch sử. Nói cách khác, bạn sẽ không biết cách chọn một học sinh cho đến khi bạn đọc thông tin chi tiết của họ từ trang tính Cũng hãy tưởng tượng rằng các sinh viên có thể được thêm hoặc xóa khỏi danh sách khi ứng dụng chạy Vì vậy, trong ví dụ này số lượng học sinh không cố định và thay đổi rất nhiều. Ở đây bạn không biết trước số lượng học sinh. Do đó bạn không biết nên tạo mảng có kích thước như thế nào Bạn có thể tạo một mảng có kích thước lớn nhất có thể. Vấn đề là bạn sẽ có rất nhiều chỗ trống và sẽ phải thêm mã để giải quyết những chỗ trống này. Nếu bạn đọc 50 sinh viên từ tối đa 1000 thì bạn sẽ có 950 vị trí mảng chưa sử dụng Bạn cũng có thể thay đổi kích thước mảng cho từng mục khi nó được thêm vào. Điều này rất không hiệu quả và khá lộn xộn để làm
Vì vậy, đối với ví dụ này sử dụng bộ sưu tập sẽ tốt hơn ' Declare Dim coll As New Collection ' Add item - VBA looks after resizing coll.Add "Apple" coll.Add "Pear" ' remove item - VBA looks after resizing coll.Remove 1
Khi bạn thêm hoặc xóa một mục vào bộ sưu tập, VBA sẽ thực hiện tất cả việc thay đổi kích thước cho bạn. Bạn không phải chỉ định kích thước hoặc phân bổ không gian mới. VBA làm điều đó dưới mui xe. Tất cả những gì bạn phải làm là thêm một mục hoặc xóa mục đó
Một lợi thế khác của bộ sưu tậpBộ sưu tập dễ sử dụng hơn nhiều so với mảng, đặc biệt nếu bạn chưa quen với lập trình. Hầu hết thời gian bạn làm ba việc với các bộ sưu tập
Vì vậy, nếu bạn không xử lý số lượng mặt hàng lớn hơn thì việc sử dụng Bộ sưu tập có thể gọn gàng hơn nhiều để sử dụng
Một bất lợi của bộ sưu tậpCác kiểu dữ liệu cơ bản(i. e. các biến như chuỗi, ngày, dài, tiền tệ, v.v. ) trong Bộ sưu tập ở dạng chỉ đọc. Bạn có thể thêm hoặc xóa một mục nhưng bạn không thể thay đổi giá trị của mục đó. Nếu bạn định thay đổi các giá trị trong một nhóm các mục thì bạn sẽ cần sử dụng một mảng
Nếu bạn đang lưu trữ một đối tượng trong Bộ sưu tập thì bạn có thể thay đổi giá trị vì Bộ sưu tập lưu trữ một tham chiếu thay vì đối tượng thực tế
Bây giờ chúng ta đã biết khi nào và tại sao nên sử dụng một bộ sưu tập, hãy xem cách sử dụng một bộ sưu tập Bạn có thể tìm hiểu thêm về Bộ sưu tập so với Mảng trong video này… Cách tạo một bộ sưu tậpBạn có thể khai báo và tạo trong một dòng như đoạn mã sau ' Declare and create Dim coll As New Collection
Như bạn có thể thấy, bạn không cần chỉ định kích thước. Khi bộ sưu tập của bạn đã được tạo, bạn có thể dễ dàng thêm các mục vào đó Bạn cũng có thể khai báo và sau đó tạo bộ sưu tập nếu và khi bạn cần ' Declare Dim coll As Collection ' Create Collection Set coll = New Collection
Sự khác biệt nhỏ giữa các phương pháp nàySự khác biệt giữa các phương thức này là đối với phương thức đầu tiên, bộ sưu tập luôn được tạo. Đối với phương pháp thứ hai, bộ sưu tập chỉ được tạo khi đạt đến dòng Đặt. Vì vậy, bạn có thể đặt mã để chỉ tạo bộ sưu tập nếu một điều kiện nhất định được đáp ứng ________số 8_______
Ưu điểm của việc sử dụng phương pháp này là tối thiểu. Phân bổ bộ nhớ rất quan trọng vào những năm 1990 khi bộ nhớ máy tính bị hạn chế. Trừ khi bạn đang tạo một số lượng lớn bộ sưu tập trên một PC chậm, bạn sẽ không bao giờ nhận thấy bất kỳ lợi ích nào Sử dụng Đặt có nghĩa là bộ sưu tập sẽ hoạt động khác so với khi bạn đặt bộ sưu tập thành không có gì. Phần tiếp theo giải thích điều này Hãy xem video dưới đây để tìm hiểu thêm về Bộ sưu tập… Xóa tất cả các mục khỏi Bộ sưu tậpBộ sưu tập không có chức năng RemoveAll. Tuy nhiên, để xóa tất cả các mục khỏi bộ sưu tập, bạn chỉ cần đặt nó thành một bộ sưu tập mới Set Coll = New Collection.
VBA sẽ xóa bộ sưu tập vì chúng tôi không còn tham chiếu đến nó nữa. Khi chúng tôi xóa tất cả các mục, chúng tôi thường muốn sử dụng lại bộ sưu tập, vì vậy chúng tôi sẽ giết hai con chim một cách hiệu quả bằng cách sử dụng phương pháp này Dim mark As Long mark = sheetMarks.Range("A1")0
Một điều tinh tế cần lưu ý là nếu chúng ta có hai hoặc nhiều biến tham chiếu đến cùng một bộ sưu tập thì nó sẽ không bị xóa (xem ) Trong ví dụ bên dưới, các mục của bộ sưu tập ban đầu không bị xóa vì nó vẫn được tham chiếu bởi coll2 Dim mark As Long mark = sheetMarks.Range("A1")1
Thêm các mục vào Bộ sưu tậpThật đơn giản để thêm các mục vào bộ sưu tập. Bạn sử dụng thuộc tính add theo sau là giá trị bạn muốn thêm Dim mark As Long mark = sheetMarks.Range("A1")2
Bạn có thể có bất kỳ loại cơ bản nào trong bộ sưu tập, chẳng hạn như Double Dim mark As Long mark = sheetMarks.Range("A1")3
Khi bạn thêm các mục theo cách này, chúng sẽ được thêm vào chỉ mục có sẵn tiếp theo. Trong ví dụ về trái cây, Táo được thêm vào vị trí 1 và Lê vào vị trí 2
Trước và sauBạn có thể sử dụng các tham số Trước hoặc Sau để chỉ định nơi bạn muốn đặt mục trong bộ sưu tập. Lưu ý rằng bạn không thể sử dụng cả hai đối số này cùng một lúc Dim mark As Long mark = sheetMarks.Range("A1")4
Sau mã này, bộ sưu tập theo thứ tự
Dim mark As Long mark = sheetMarks.Range("A1")5
Sau mã này, bộ sưu tập theo thứ tự
Truy cập các mục của Bộ sưu tậpĐể truy cập các mục của bộ sưu tập, bạn chỉ cần sử dụng chỉ mục. Như chúng ta đã thấy chỉ mục là vị trí của mục trong bộ sưu tập dựa trên thứ tự chúng được thêm vào Thứ tự cũng có thể được đặt bằng tham số Trước hoặc Sau Dim mark As Long mark = sheetMarks.Range("A1")6
Bạn cũng có thể sử dụng Thuộc tính vật phẩm để truy cập một vật phẩm trong bộ sưu tập. Đây là phương thức mặc định của bộ sưu tập nên các dòng mã sau đây là tương đương Dim mark As Long mark = sheetMarks.Range("A1")7
Các mục trong Bộ sưu tập có phải là Chỉ đọc không?Đây là một điểm rất quan trọng. Khi một loại dữ liệu cơ bản được lưu trữ trong Bộ sưu tập, nó ở chế độ chỉ đọc. Một kiểu dữ liệu cơ bản là một chuỗi, ngày, số nguyên, dài, v.v. Nếu bạn cố cập nhật một mục Bộ sưu tập, bạn sẽ gặp lỗi. Đoạn mã sau tạo ra lỗi "bắt buộc phải có đối tượng" Dim mark As Long mark = sheetMarks.Range("A1")8
Tuy nhiên, bạn có thể thay đổi đối tượng lớp được lưu trữ trong Bộ sưu tập Dim mark As Long mark = sheetMarks.Range("A1")9
Điều này có vẻ giống như hành vi mâu thuẫn, nhưng có một lý do chính đáng. Bất kỳ mục nào được thêm vào Bộ sưu tập đều ở chế độ chỉ đọc. Tuy nhiên, khi bạn thêm một đối tượng vào Bộ sưu tập, đối tượng không được thêm dưới dạng mục. Một biến có địa chỉ bộ nhớ của đối tượng được thêm vào dưới dạng mục Điều này xảy ra liền mạch để bạn không nhận thấy nó. Biến mục thực sự là chỉ đọc nhưng đối tượng mà nó trỏ tới thì không Tất cả những gì bạn cần nhớ là các kiểu dữ liệu cơ bản trong Bộ sưu tập là chỉ đọc. Các đối tượng trong Bộ sưu tập có thể được thay đổi Bạn có thể đọc thêm về các đối tượng trong bộ nhớ
Thêm các loại khác nhauBạn cũng có thể thêm các loại mục khác nhau vào bộ sưu tập ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")0
Điều này hiếm khi cần thiết. Trong VBA, bộ sưu tập Trang tính chứa các trang tính thuộc loại Trang tính và loại Biểu đồ. (Để tạo một bảng Biểu đồ, chỉ cần nhấp chuột phải vào bất kỳ Biểu đồ nào, chọn Di chuyển và chọn nút radio cho Trang tính mới) Đoạn mã sau hiển thị loại và tên của tất cả các trang tính trong sổ làm việc hiện tại. Lưu ý để truy cập các loại khác nhau, bạn cần Đối với mỗi biến là một biến thể, nếu không bạn sẽ gặp lỗi ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")1
Khi bạn truy cập các mục khác nhau, Đối với mỗi biến phải là một biến thể. Nếu không, bạn sẽ gặp lỗi khi truy cập vào một loại khác với loại mà bạn đã khai báo. Nếu chúng ta đã khai báo sh là một trang tính trong ví dụ trên, nó sẽ báo lỗi khi chúng ta cố gắng truy cập một trang tính kiểu Biểu đồ Rất hiếm khi bạn cần một bộ sưu tập các loại khác nhau nhưng như bạn có thể thấy đôi khi nó có thể hữu ích
Thêm mục bằng phímBạn cũng có thể thêm các mục bằng một phím như ví dụ tiếp theo cho thấy ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")2
Tôi đã bao gồm các tên tham số để làm cho ví dụ trên rõ ràng. Tuy nhiên bạn không cần phải làm điều này. Chỉ cần nhớ khóa là tham số thứ hai và phải là một chuỗi duy nhất Đoạn mã sau hiển thị ví dụ thứ hai về cách sử dụng các phím ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")3
Sử dụng phím có ba lợi thế
Trong bộ sưu tập Sổ làm việc VBA, truy cập sổ làm việc theo khóa (tên) sẽ tốt hơn nhiều so với theo chỉ mục. Thứ tự phụ thuộc vào thứ tự chúng được mở và do đó khá ngẫu nhiên ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")4
Khi nào nên sử dụng phímMột ví dụ về thời điểm sử dụng các phím như sau. Hãy tưởng tượng bạn có một bộ ID cho 10.000 sinh viên cùng với điểm của họ Bạn cũng có một số báo cáo trang tính có danh sách ID sinh viên. Đối với mỗi bảng tính này, bạn cần in dấu cho từng học sinh Bạn có thể làm điều này bằng cách thêm 10.000 sinh viên vào bộ sưu tập bằng cách sử dụng id sinh viên của họ khi họ nhập khóa. Khi bạn đọc ID từ bảng tính, bạn có thể truy cập trực tiếp vào điểm của học sinh này Nếu bạn không sử dụng khóa, bạn sẽ phải tìm kiếm trong 10.000 ID cho mỗi ID trên báo cáo
Thiếu sót của việc sử dụng khóa trong bộ sưu tậpCó hai thiếu sót của các khóa trong Bộ sưu tập
Vấn đề đầu tiên rất dễ giải quyết. Đoạn mã sau kiểm tra xem có tồn tại khóa không ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")5
Bạn có thể sử dụng nó như thế này ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")6
Vấn đề thứ hai là không thể cập nhật giá trị trong Bộ sưu tập. Tuy nhiên, chúng tôi có thể cập nhật một đối tượng và lý do cho việc này là bộ sưu tập không thực sự lưu trữ đối tượng. Nó lưu trữ địa chỉ của đối tượng Nếu bạn cần cập nhật một giá trị cơ bản như chuỗi dài, v.v. thì không thể. Bạn phải xóa mục này và thêm mục mới Nếu bạn muốn sử dụng khóa, có một giải pháp thay thế cho Bộ sưu tập. Bạn có thể sử dụng từ điển. Từ điển cung cấp nhiều chức năng hơn để làm việc với các phím. Bạn có thể kiểm tra xem các khóa có tồn tại hay không, cập nhật các giá trị tại các khóa, nhận danh sách các khóa, v.v. Hãy xem video này để biết thêm về Dictionary vs Collection…
Kiểm tra xem một mục có tồn tại trong Bộ sưu tập khôngKhông có chức năng tích hợp để kiểm tra xem một mục có tồn tại trong bộ sưu tập hay không. Một cách để làm điều đó là đọc qua tất cả các mục trong bộ sưu tập và kiểm tra từng mục một. Điều này rất không hiệu quả, đặc biệt nếu bạn đang xử lý một tập dữ liệu lớn ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")7 ' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")5
' Declare a variable for each mark Dim mark1 As Long Dim mark2 As Long . . . Dim mark100 As Long ' Store the marks from the worksheet in a variable mark1 = sheetMarks.Range("A1") mark2 = sheetMarks.Range("A2") . . . mark100 = sheetMarks.Range("A100")9
Truy cập tất cả các mục trong Bộ sưu tậpĐể truy cập tất cả các mục trong một bộ sưu tập, bạn có thể sử dụng vòng lặp For hoặc vòng lặp For Each. Hãy xem xét những điều này một cách riêng lẻ
Sử dụng vòng lặp ForVới Vòng lặp For bình thường, bạn sử dụng chỉ mục để truy cập từng mục. Ví dụ sau in tên của tất cả các sổ làm việc đang mở ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next0
Bạn có thể thấy rằng chúng tôi sử dụng phạm vi từ 1 đến Workbooks. Đếm. Mục đầu tiên luôn ở vị trí một và mục cuối cùng luôn ở vị trí được chỉ định bởi thuộc tính Count của bộ sưu tập Ví dụ tiếp theo in ra tất cả các mục trong bộ sưu tập do người dùng tạo ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next1
Sử dụng For EachVòng lặp For Each là vòng lặp chuyên dụng được sử dụng cho Bộ sưu tập. Nó không sử dụng chỉ mục và định dạng được hiển thị trong ví dụ sau ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next2
Định dạng của vòng lặp For là nơi tôi là một dài và Coll là một bộ sưu tập
Định dạng của For Each Loop là trong đó var là một biến thể và Coll là một bộ sưu tập Để truy cập từng mục
Ví dụ sau hiển thị các vòng lặp cạnh nhau cho ví dụ về bộ sưu tập người dùng ở trên ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next3
Cho Mỗi So Với ChoĐiều quan trọng là phải hiểu sự khác biệt giữa hai vòng lặp Vòng lặp Cho mỗi Loop
Vòng lặp For
Hãy so sánh các vòng lặp trong từng thuộc tính này
Tốc độFor Each được coi là nhanh hơn For Loop. Ngày nay, đây chỉ là vấn đề nếu bạn có một bộ sưu tập lớn và/hoặc PC/Mạng chậm
Gọn gàng hơnVòng lặp For Each dễ viết hơn, đặc biệt nếu bạn đang sử dụng các vòng lặp lồng nhau. So sánh các vòng lặp sau. Cả hai đều in tên của tất cả các trang tính trong sổ làm việc đang mở ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next4 Vòng lặp For Each viết gọn gàng hơn và ít có khả năng xảy ra lỗi hơn
Gọi mónThứ tự của vòng lặp For Each luôn từ chỉ số thấp nhất đến cao nhất. Nếu bạn muốn lấy một đơn hàng khác thì bạn cần sử dụng Vòng lặp For. Thứ tự của vòng lặp For có thể được thay đổi. Bạn có thể đọc các mục ngược lại. Bạn có thể đọc một phần của các mục hoặc bạn có thể đọc từng mục thứ hai ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next5
Vòng lặp For giúp linh hoạt hơn ở đây nhưng thực tế là hầu hết thời gian bạn chỉ cần thứ tự cơ bản Bạn có thể tìm hiểu thêm về For Loops trong video bên dưới…
Sắp xếp một bộ sưu tậpKhông có sắp xếp tích hợp cho bộ sưu tập VBA. Tuy nhiên, chúng ta có thể sử dụng QuickSort này ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next6
Bạn có thể sử dụng nó như thế này ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next7
Sử dụng Bộ sưu tập với Chức năng và PhụSử dụng Bộ sưu tập làm tham số hoặc giá trị trả về rất dễ thực hiện. Chúng ta sẽ xem xét chúng lần lượt
Truyền Bộ sưu tập cho Phụ/Chức năngThật đơn giản để chuyển một bộ sưu tập cho một chức năng hoặc phụ. Nó được truyền giống như bất kỳ tham số nào như ví dụ mã sau đây cho thấy ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next8
Bạn có thể thấy mức độ hữu ích của PrintColl phụ trong ví dụ. Nó sẽ in tất cả các phần tử của BẤT KỲ bộ sưu tập nào. Kích thước hoặc loại phần tử không quan trọng. Điều này cho thấy cách sử dụng các bộ sưu tập linh hoạt
Vượt qua ByVal so với ByRefMột điểm tinh tế cần lưu ý ở đây là chuyển theo giá trị(By Val) và chuyển theo tham chiếu(ByRef) hơi khác nhau Đối với một biến đơn giản chuyển theo giá trị có nghĩa là một bản sao được tạo. Điều này có nghĩa là nếu Hàm/Phụ thay đổi thì giá trị sẽ không bị thay đổi khi bạn quay lại quy trình gọi
Trong ví dụ sau, chúng tôi vượt qua tổng số bằng cách sử dụng cả ByVal và ByRef. Bạn có thể thấy rằng sau khi chúng tôi sử dụng ByRef, giá trị đã thay đổi trong quy trình gọi ' Create collection Dim collMarks As New Collection ' Read 100 values to collection Dim c As Range For Each c In Sheet1.Range("A1:A100") ' This line is used to add all the values collMarks.Add c.Value Next9
Sử dụng ByVal và ByRef với Bộ sưu tập hơi khác một chút. Nếu bạn thêm hoặc xóa mục thì bộ sưu tập trong trình gọi ban đầu cũng sẽ bị thay đổi. Vì vậy, Subs trong ví dụ sau sẽ xóa cả mục đầu tiên của bộ sưu tập ban đầu ' Get last row - this is the number of students Dim lStudentCount As Long lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Create array of correct size Dim arr() As Long ReDim arr(1 To lStudentCount)0
Lý do cho điều này là một biến Bộ sưu tập chứa một con trỏ. Điều này có nghĩa là nó chứa địa chỉ của bộ sưu tập chứ không phải bộ sưu tập thực tế. Vì vậy, khi bạn thêm hoặc xóa một mục, bạn đang thay đổi con trỏ đang trỏ tới chứ không phải chính con trỏ. Tuy nhiên, nếu bạn thay đổi con trỏ, nó sẽ bị thay đổi bên ngoài sub Bạn không cần phải lo lắng về con trỏ. Tất cả những gì bạn cần biết là điều này ảnh hưởng như thế nào đến hành vi truyền tham số. Nếu bạn đặt tham số bộ sưu tập thành không có gì thì hành vi sẽ phụ thuộc vào việc bạn đã sử dụng ByRef hay ByVal
' Get last row - this is the number of students Dim lStudentCount As Long lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Create array of correct size Dim arr() As Long ReDim arr(1 To lStudentCount)1
Trả về một bộ sưu tập từ một chức năngTrả về một bộ sưu tập từ một Hàm cũng giống như trả về bất kỳ đối tượng nào. Bạn cần sử dụng từ khóa Set. Trong ví dụ sau, bạn có thể xem cách trả lại một bộ sưu tập ' Get last row - this is the number of students Dim lStudentCount As Long lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Create array of correct size Dim arr() As Long ReDim arr(1 To lStudentCount)2 Ghi chú. rằng bạn không sử dụng từ khóa Mới khi khai báo bộ sưu tập trong FruitReport() phụ. Điều này là do bộ sưu tập được tạo trong CreateCollection(). Khi bạn trả lại bộ sưu tập, bạn chỉ cần gán biến bộ sưu tập để trỏ đến bộ sưu tập này
Ví dụ – Đọc từ một trang tínhChúng ta hãy xem một ví dụ về cách sử dụng Bộ sưu tập với một trang tính Chúng tôi sử dụng dữ liệu này điểm học sinh Người dùng chọn chủ đề tại ô B2  Trong ví dụ này, chúng ta đọc qua danh sách sinh viên. Bất kỳ sinh viên nào đang học môn học được liệt kê trong ô B2 sẽ được thêm vào bộ sưu tập Sau đó, chúng tôi viết kết quả vào trang tính từ ô F5 trở đi Bạn có thể tải xuống ví dụ dưới đây như một phần của mã nguồn cho bài đăng này Mã ở đây ' Get last row - this is the number of students Dim lStudentCount As Long lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Create array of correct size Dim arr() As Long ReDim arr(1 To lStudentCount)3
Bạn có thể xem video bên dưới để biết thêm về cách đọc giữa Bộ sưu tập và Trang tính…
Phần kết luậnBộ sưu tập là một phần rất hữu ích của VBA. Có nhiều cách dễ sử dụng hơn Mảng và rất hữu ích khi bạn thực hiện nhiều thao tác thêm và xóa mục. Họ chỉ có bốn thuộc tính. Thêm, xóa, đếm và mục. Điều này làm cho họ rất dễ dàng để làm chủ Các điểm chính của bài đăng này là
Cái gì tiếp theo?Hướng dẫn VBA miễn phí Nếu bạn là người mới sử dụng VBA hoặc bạn muốn nâng cao các kỹ năng VBA hiện tại của mình thì tại sao không thử Hướng dẫn VBA cơ bản đào tạo liên quan. Nhận toàn quyền truy cập vào các hội thảo trực tuyến về đào tạo VBA của Excel và tất cả các hướng dẫn (GHI CHÚ. Lập kế hoạch xây dựng hoặc quản lý Ứng dụng VBA? . ) |
