Hướng dẫn này sẽ trình bày cách làm việc với Tables và ListObjects trong VBA.
Bảng VBA và ListObjects
Bảng là một trong những tính năng hữu ích và mạnh mẽ nhất của Excel, trong hướng dẫn này, chúng ta sẽ đi qua cách sử dụng VBA để tạo bảng, thêm sắp xếp đơn giản vào bảng, lọc bảng và thực hiện các tác vụ khác liên quan đến bảng.
Tạo bảng bằng VBA
Phương thức ListObjects.Add có thể thêm một bảng vào một trang tính, dựa trên một phạm vi trong trang tính đó. Chúng tôi có phạm vi được hiển thị trong ($ A $ 1: $ B $ 8) trên một trang tính có tên là Sheet1.
Đoạn mã sau sẽ thêm một bảng, được gọi là Table1 vào trang tính của bạn, dựa trên phạm vi ($ A $ 1: $ B $ 8) bằng cách sử dụng Kiểu bảng mặc định:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Bảng 1"Kết thúc Sub |
Kết quả là:
Chèn một cột ở cuối bảng bằng VBA
Bạn có thể sử dụng phương thức ListColumns.Add để thêm một cột vào cuối bảng của mình. Chúng tôi có bảng của chúng tôi được gọi là Table1 được hiển thị bên dưới.
Bạn có thể thêm một cột vào bảng của mình bằng cách sử dụng mã sau, mã này sẽ luôn thêm một cột vào cuối bảng:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddKết thúc Sub |
Kết quả là:
Chèn một hàng ở cuối bảng bằng VBA
Bạn có thể sử dụng phương thức ListRows.Add để thêm một hàng vào cuối bảng của mình. Chúng tôi có bảng của chúng tôi được gọi là Table1 được hiển thị bên dưới.
Đoạn mã sau sẽ luôn thêm một hàng vào cuối bảng của bạn.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Table1"). ListRows.AddKết thúc Sub |
Kết quả là:
Thêm một sắp xếp đơn giản với VBA
Bạn có thể sắp xếp một bảng bằng VBA. Chúng tôi có bảng của chúng tôi được gọi là Table1 được hiển thị bên dưới và chúng tôi có thể sử dụng VBA để sắp xếp Cột bán hàng từ thấp nhất đến cao nhất.
Đoạn mã sau sẽ sắp xếp cột Bán hàng theo thứ tự tăng dần.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Phạm vi ("Table1 [[# Headers], [Sales]]"). ChọnActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Key: = Range ("Table1 [[# All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalVới ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sắp xếp.Header = xlYes.MatchCase = Sai.Orientation = xlTopToBottom.SortMethod = xlPinYin.Ứng dụngKết thúc vớiKết thúc Sub |
Kết quả là:
Lọc bảng bằng VBA
Bạn cũng có thể lọc bảng Excel bằng VBA. Chúng tôi có bảng của chúng tôi được gọi là Table1 và chúng tôi muốn lọc bảng để chỉ hiển thị doanh số bán hàng lớn hơn 1500.
Chúng ta có thể sử dụng phương pháp Autofilter, có năm tham số tùy chọn. Vì chúng tôi muốn lọc cột Bán hàng là cột thứ hai, chúng tôi đặt Trường thành 2 và chúng tôi sử dụng tham số toán tử xlAnd, được sử dụng cho ngày và số.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Trường Range.AutoFilter: = 2, Criteria1: = _"> 1500", Toán tử: = xlAndKết thúc Sub |
Kết quả là:
Xóa Bộ lọc bằng Phương pháp ShowAllData trong VBA
Bạn có thể truy cập Phương thức ShowAllData của lớp Trang tính để xóa bộ lọc. Nếu đó là (các) bộ lọc của bảng mà bạn muốn xóa, thì trước tiên bạn phải chọn một ô trong bảng, điều này bạn có thể thực hiện trong VBA.
Phương thức ShowAllData sẽ tạo ra lỗi nếu người ta không sử dụng logic có điều kiện để kiểm tra xem đã có bộ lọc nào được áp dụng trong trang tính chưa. Đoạn mã sau chỉ cho bạn cách thực hiện việc này:
| 123456789 | Sub ClearingTheFilter ()Phạm vi ("Table1 [[# Headers], [Sales]]"). ChọnNếu ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True thìActiveSheet.ShowAllDataKết thúc nếuKết thúc Sub |
Xóa tất cả bộ lọc khỏi bảng Excel
Bạn có thể truy cập Phương thức ShowAllData của lớp ListObject mà không cần phải chọn một ô trong bảng trước. Đoạn mã sau chỉ cho bạn cách thực hiện việc này:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataKết thúc Sub |
Xóa một hàng bằng VBA
Bạn có thể xóa một hàng trong cơ sở dữ liệu của bảng bằng phương pháp ListRows.Delete. Bạn phải chỉ định hàng nào bằng cách sử dụng số hàng. Chúng ta có bảng sau được gọi là Table1.
Giả sử bạn muốn xóa hàng thứ hai trong cơ sở dữ liệu của bảng, mã sau sẽ cho phép bạn thực hiện việc này:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteKết thúc Sub |
Kết quả là:
Xóa cột bằng VBA
Bạn có thể xóa một cột khỏi bảng của mình bằng phương pháp ListColumns.Delete. Chúng tôi có bảng sau được gọi là Table1 được hiển thị bên dưới:
Để xóa cột đầu tiên, bạn sẽ sử dụng mã sau:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .XóaKết thúc Sub |
Kết quả là:
Chuyển đổi một bảng trở lại một phạm vi trong VBA
Bạn có thể chuyển đổi bảng trở lại phạm vi bình thường bằng VBA. Đoạn mã sau chỉ cho bạn cách chuyển đổi một bảng có tên là Table1 trở lại một dải ô:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Bỏ công bốKết thúc Sub |
Thêm các cột có dải và định dạng cho tất cả các Bảng trong một Trang tính bằng cách sử dụng VBA
Bạn có thể truy cập tất cả các bảng trong trang tính của mình bằng cách sử dụng bộ sưu tập ListObjects. Trong trang tính bên dưới, chúng tôi có hai bảng và chúng tôi muốn thêm Cột có dải vào cả hai bảng cùng một lúc và thay đổi phông chữ của phần dữ liệu của cả hai bảng thành in đậm, sử dụng VBA.
| 12345678910111213 | Sub AddBandedColumns ()Dim tbl As ListObjectDim sht As WorksheetĐặt sht = ThisWorkbook.ActiveSheetĐối với mỗi tbl Trong sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueTbl tiếp theoKết thúc Sub |
Kết quả là:
Tạo bảng trong Access trong VBA bằng DoCmd.RunSQL
Một trong những cách chính để tạo bảng trong Access trong VBA là sử dụng phương thức DoCmd.RunSQL để chạy truy vấn hành động với câu lệnh SQL.
Chúng tôi có một nút trên biểu mẫu mẫu của mình và khi nhấp vào nút, chúng tôi muốn tạo một bảng có tên là ProductsTable với hai trường hoặc cột, một sẽ là trường khóa chính được gọi là ProductsID và một sẽ là trường có tên là Bán hàng.
Để tạo bảng này, chúng tôi sẽ sử dụng mã sau:
| 123456 | Riêng tư phụ cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Kết thúc Sub |
Kết quả là:
Lọc một bảng trong Access bằng VBA
Bạn cũng có thể lọc một bảng trong Access bằng phương pháp DoCmd.ApplyFilter. Chúng tôi có bảng đơn giản của chúng tôi được hiển thị bên dưới trong Access được gọi là ProductsTable.
Chúng tôi muốn nhấn nút này trên biểu mẫu của mình và sau đó chỉ thấy Doanh số bán hàng lớn hơn 1500.
Vì vậy, chúng tôi sẽ sử dụng mã sau để thực hiện việc này:
| 1234567 | Sub riêng tư cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Bán hàng]> 1500"Kết thúc Sub |
Kết quả là:
