Excel VBA lấy tên bảng

Nếu bạn muốn lưu trữ địa chỉ ô (ví dụ ô "A3") trong một biến rồi sử dụng nó để lấy tên bảng

Dim GetCellsTable As String
GetCellsTable = "A3"
MyTableName = Range(GetCellsTable).ListObject.Name

Hoặc, nếu bạn muốn lưu chính ô đó dưới dạng một đối tượng phạm vi trong một biến

Dim GetCellsTable as Range
Set GetCellsTable = Range("A3")
MyTableName = GetCellsTable.ListObject.Name

Đối với một nhà phân tích dữ liệu, Bảng Excel là một điều cần thiết. Chúng là cách hiệu quả nhất để sắp xếp dữ liệu thô của bạn và đề cập đến dữ liệu thu hẹp hoặc mở rộng một cách thường xuyên. Tương tự như vậy, các bảng Excel có thể cực kỳ hữu ích khi kết hợp với VBA.  

Cá nhân tôi sử dụng bảng dữ liệu như một cách để lưu trữ cài đặt người dùng mà không phải sửa đổi bất kỳ mã VBA nào. Bạn có thể xem các ví dụ về điều này trong Mẫu Trình xuất của tôi, nơi tôi sử dụng các bảng để lưu tên trang tính và địa chỉ email.  

Trong bài viết này, tôi muốn tập hợp tất cả các cách phổ biến để tham chiếu dữ liệu bảng bằng VBA vào một nơi. Hy vọng rằng đây sẽ là hướng dẫn mà bạn có thể xem đi xem lại để có thể kết hợp các bảng vào mã hóa macro VBA của mình một cách dễ dàng và hiệu quả. Vui thích

Mục Liên kết nhanh

• Tổng quan về bảng Excel

• Chọn vùng của bảng bằng VBA

• Chèn Hàng và Cột vào Bảng

• Xóa các phần của bảng

• Xóa/xóa dữ liệu trong bảng

• Lặp qua từng cột hoặc hàng của bảng

• Tra cứu các giá trị trong một bảng

• Áp dụng thứ tự sắp xếp cho cột bảng

• Đọc dữ liệu bảng vào một biến mảng

• Thay đổi kích thước bảng

• Thay đổi tính toán tổng số hàng của tất cả các cột trong bảng

• Đến ActiveTable

• Bài viết bổ sung

Tổng quan về bảng Excel

Bảng là gì?

Bảng chỉ đơn giản là một phạm vi có cấu trúc nơi bạn có thể tham chiếu đến các phần khác nhau được ánh xạ tự động (chẳng hạn như Hàng tiêu đề hoặc cột bên dưới tiêu đề "Số tiền"). Bảng là một tính năng tuyệt vời mà Microsoft đã thêm vào Excel vì chúng không chỉ cấu trúc dữ liệu của bạn mà còn mở rộng cùng với dữ liệu của bạn khi dữ liệu phát triển. Và nếu có một điều bạn nên biết về việc tạo bảng tính, thì đó là làm cho nó NĂNG ĐỘNG nhất có thể luôn là một điều tốt

Bạn có thể nhanh chóng tạo Bảng bằng cách đánh dấu một phạm vi (có tiêu đề phù hợp) và sử dụng phím tắt Ctrl + t. Bạn cũng có thể điều hướng đến tab Chèn và chọn nút Bảng trong nhóm Bảng

Các bộ phận của bảng

Infographic dưới đây sẽ giúp bạn hình dung các phần khác nhau của một đối tượng Table qua lăng kính của ngôn ngữ lập trình VBA

Các phần này của Bảng ListObject bao gồm

• Phạm vi

• Tiêu ĐềHàngPhạm Vi

• Dữ LiệuCơ ThểPhạm Vi

• Danh sáchRows

• Danh sáchCột

• Tổng sốHàngRange

Làm cách nào để tìm các bảng hiện có?

Các bảng có thể hơi khó tìm nếu bạn không quen làm việc với chúng vì chúng có thể kết hợp rất tốt với bảng tính tùy thuộc vào định dạng đã được áp dụng

Hãy xem xét 4 cách khác nhau để bạn có thể xác định xem bạn có đang làm việc với các ô trong Đối tượng Bảng hay không

1. Tab Thiết kế bảng xuất hiện

Nếu bạn bấm vào trong một ô là một phần của Bảng Excel, bạn sẽ thấy ngay tab Thiết kế Bảng xuất hiện trong Dải băng. Đây là một tab theo ngữ cảnh, có nghĩa là nó chỉ xuất hiện khi một đối tượng cụ thể được chọn trên bảng tính của bạn (một tab tương tự xuất hiện khi Bảng Pivot hoặc Hình dạng được chọn trên bảng tính)

Xem kích thước đầy đủ

Đây là một dấu hiệu nhận biết rất nhanh rằng ô bạn đang làm việc là một phần của Đối tượng Bảng

2. Chỉ báo góc xanh

Có một chỉ báo nhỏ ở ô dưới cùng bên phải của phạm vi Bảng để cho biết có một bảng. Như bạn có thể thấy trong hình bên dưới, chỉ báo này có thể rất dễ tìm, nhưng cũng có thể dễ dàng bị bỏ qua do kích thước nhỏ của nó

3. Sử dụng Trình quản lý tên của Excel

Một cách tuyệt vời khác để tìm một bảng (và tên của nó) là vào Trình quản lý tên. Bạn có thể truy cập trình quản lý tên bằng cách điều hướng đến tab Công thức và nhấp vào nút Trình quản lý tên bên trong nhóm Tên được xác định

Bằng cách sử dụng menu Bộ lọc ở góc bên phải của Trình quản lý Tên, bạn có thể thu hẹp danh sách tên của mình chỉ còn các Bảng trong Sổ làm việc. Trình quản lý tên sẽ hiển thị cho bạn chính xác vị trí của các bảng trong bảng tính và cả tên Bảng là gì

4. Mã VBA để kiểm tra xem ô có nằm trong bảng ListObject không

Có thể có những trường hợp khi bạn cần xác định xem một ô nhất định có nằm trong ListObject (Bảng) hay không. Mã VBA bên dưới chỉ cho bạn cách bạn có thể thực hiện kiểm tra xem ActiveCell (ô đã chọn) có phải là một phần của bất kỳ Bảng Excel nào trên bảng tính hay không

Sub IsActiveCellInTable()
'PURPOSE. Xác định xem ô được chọn hiện tại có phải là một phần của Bảng Excel hay không
'SOURCE. www. Bảng tínhGuru. com

Dim TestForTable As String

. Danh sáchObject. Tên
  On Error Resume Next
  TestForTable = ActiveCell.ListObject.Name
   Bật Lỗi Đi tới 0 . " & TestForTable

'Determine Results of Test
  If TestForTable <> "" Then
    'ActiveCell is within a ListObject Table
      MsgBox "Cell is part of the table named: " & TestForTable
   Khác
     'ActiveCell KHÔNG nằm trong Bảng ListObject
      MsgBox "Cell is not part of any table"
  End If

End Sub

Đây là một thử nghiệm xác thực tuyệt vời nếu bạn đang tạo mã cho phép người dùng thao tác trên bảng excel. Tôi đã sử dụng điều này nhiều lần để tạo các nút cho phép người dùng chèn hoặc xóa các hàng cụ thể trong bảng dựa trên vị trí họ chọn trên trang tính được bảo vệ bằng mật khẩu

Chọn vùng của bảng bằng VBA

SelectVBA CodingEntire TableActiveSheet. ListObjects("Bảng1"). Phạm vi. SelectTable Header RowActiveSheet. ListObjects("Bảng1"). Tiêu ĐềHàngPhạm Vi. SelectTable DataActiveSheet. ListObjects("Bảng1"). Dữ LiệuCơ ThểPhạm Vi. SelectThird ColumnActiveSheet. ListObjects("Bảng1"). Danh sáchCột(3). Phạm vi. SelectThird Column (Data Only)ActiveSheet. ListObjects("Bảng1"). Danh sáchCột(3). Dữ LiệuCơ ThểPhạm Vi. Chọn Select Row 4 của Table Data ActiveSheet. ListObjects("Bảng1"). ListRows(4). Phạm vi. Chọn Chọn tiêu đề thứ 3 ActiveSheet. ListObjects("Bảng1"). HeaderRowRange(3). Chọn Chọn Điểm dữ liệu trong Hàng 3, Cột 2 ActiveSheet. ListObjects("Bảng1"). DataBodyRange(3, 2). SelectSubtotalsActiveSheet. ListObjects("Bảng1"). Tổng sốHàngRange. Lựa chọn

Chèn hàng và cột vào bảng

SelectVBA CodingChèn một cột mới 4ActiveSheet. ListObjects("Bảng1"). Danh sáchCột. Thêm vị trí. =4Chèn cột vào cuối bảngActiveSheet. ListObjects("Bảng1"). Danh sáchCột. AddInsert Row Trên Row 5ActiveSheet. ListObjects("Bảng1"). Danh sáchRows. Thêm (5)Thêm hàng vào cuối bảngActiveSheet. ListObjects("Bảng1"). Danh sáchRows. Thêm LuônChèn. = True Thêm Tổng số Hàng ActiveSheet. ListObjects("Bảng1"). ShowTotals = Đúng

Xóa các phần khác nhau của bảng

Sub RemovePartsOfTable()

Làm mờ tbl As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Xóa cột thứ 3
  tbl. Danh sáchCột(3). Xóa

'Xóa hàng DataBody thứ 4
  tbl. ListRows(4). Xóa

'Xóa các hàng DataBody từ thứ 3 đến thứ 5
  tbl. Phạm vi. Hàng("3. 5"). Xóa

'Xóa hàng tổng
  tbl. Tổng sốHàngRange. Xóa

Kết thúc Phụ

Xóa/xóa dữ liệu trong bảng

Xóa tất cả các hàng dữ liệu khỏi một bảng (trừ hàng đầu tiên)

Sub ResetTable()

Dim tbl As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Xóa tất cả các hàng của bảng trừ hàng đầu tiên
   Với tbl.DataBodyRange
     Nếu . hàng. Đếm > 1 Sau đó
. Độ lệch (1, 0). Thay đổi kích thước (. hàng. Đếm - 1,. Cột. Đếm). hàng. Xóa
     Kết thúc Nếu
   Kết thúc< . Dữ LiệuCơ ThểPhạm Vi. Hàng(1). ClearContents With

'Clear out data from first table row
  tbl.DataBodyRange.Rows(1).ClearContents

End Sub

Nếu bạn có các công thức trong bảng của mình, bạn có thể muốn giữ nguyên các công thức đó. Sửa đổi sau đây sẽ chỉ xóa các giá trị không đổi khỏi hàng đầu tiên còn lại trong Đối tượng bảng

Sub ResetTable()

Dim tbl As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Xóa tất cả các hàng của bảng trừ hàng đầu tiên
   Với tbl.DataBodyRange
     Nếu . hàng. Đếm > 1 Sau đó
. Độ lệch (1, 0). Thay đổi kích thước (. hàng. Đếm - 1,. Cột. Đếm). hàng. Xóa
     Kết thúc Nếu
   Kết thúc< . Dữ LiệuCơ ThểPhạm Vi. Hàng(1). SpecialCells(xlCellTypeConstants). ClearContents With

'Clear out data from first table row (retaining formulas)
  tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents

End Sub

Lặp qua từng cột hoặc hàng của bảng

Sub LoopingThroughTable()

Dim tbl As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject
Dim x As Long

Set tbl = ActiveSheet.ListObjects("Table1")

'Lặp qua từng cột trong bảng
   Cho . Danh sáchCột. Đếm To tbl.ListColumns.Count
    tbl. Danh sáchCột(x). Phạm vi. ColumnWidth = 8
   Next x

'Lặp qua mỗi hàng trong bảng' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm' . Phạm vi. hàng. Đếm
  For x = 1 To tbl.Range.Rows.Count
    tbl. Phạm vi. Hàng(x). RowHeight = 20
   Tiếp theo x
  
. Danh sáchRows. Đếm
  For x = 1 To tbl.ListRows.Count
    tbl. ListRows(x). Phạm vi. RowHeight = 15
   Tiếp theo x

Kết thúc Sub

Áp dụng sắp xếp cho cột trong bảng

Bạn có thể thấy mình cần sắp xếp dữ liệu Bảng của mình theo thứ tự Tăng dần hoặc Giảm dần. Đoạn mã VBA sau đây sẽ chỉ cho bạn cách sắp xếp một cột trong Bảng ListObject của bạn theo một trong hai thứ tự

Sub PSorTableColumn()
'PURPOSE. Sắp xếp bảng theo thứ tự tăng dần/giảm dần
'SOURCE. www. Bảng tínhGuru. com

Dim tbl As ListObject
. ListObjects("Table1") SortOrder As Integer

'Choose Sort Order
  SortOrder = xlAscending '(or xlDescending)

'Store Desired Excel Table to a variable
  Set tbl = ActiveSheet.ListObjects("Table1")

'Xóa sắp xếp trước
  tbl. Loại. Trường sắp xếp. Xóa
    
'Áp dụng Sắp xếp trên Cột 1 của Bảng
    tbl. Loại. Trường sắp xếp. Thêm2 _
        Khóa. =tbl. Danh sáchCột(1). Phạm vi, _
        Sắp xếp. =xlSortOnValues, _
        Đặt hàng. =SortOrder, _
        DataOption. =xlSortNormal
    
'Tùy chọn sắp xếp (nếu bạn muốn thay đổi từ mặc định)
  tbl. Loại. Tiêu đề = xlCó
  tbl. Loại. MatchCase = Sai
  tbl. Loại. Định hướng = xlTopToBottom
  tbl. Loại. SortMethod = xlPinYin

'Áp dụng Sắp xếp cho Bảng
  tbl. Loại. Áp dụng

Kết thúc Phụ

Mặc dù mã VBA ở trên có tất cả các tùy chọn tiềm năng được viết ra để bạn điều chỉnh, nhưng hầu hết thời gian bạn sẽ không cần phải rời khỏi các tùy chọn sắp xếp mặc định

Dưới đây là cùng một mã, nhưng với tất cả các tùy chọn mà bạn có thể không cần phải thay đổi giá trị cài đặt mặc định của chúng đã bị xóa

Sub SortTableColumn_Simple()
'PUPOSE. Sắp xếp bảng theo thứ tự tăng dần/giảm dần
'SOURCE. www. Bảng tínhGuru. com

Dim tbl As ListObject
. ListObjects("Table1") SortOrder As Integer

'Choose Sort Order
  SortOrder = xlDescending  '(or xlAscending)

'Store Desired Excel Table to a variable
  Set tbl = ActiveSheet.ListObjects("Table1")

'Xóa sắp xếp trước
  tbl. Loại. Trường sắp xếp. Xóa

'Áp dụng Sắp xếp trên Cột 1 của Bảng
    tbl. Loại. Trường sắp xếp. Thêm2 _
        Khóa. =tbl. Danh sáchCột(1). Phạm vi, _
        Đơn hàng. =SortOrder

'Áp dụng Sắp xếp cho Bảng
  tbl. Loại. Áp dụng

Kết thúc Phụ

Tra cứu các giá trị trong một bảng

Nếu bạn đang lưu trữ các giá trị bên trong Bảng, có thể có các tình huống mà bạn muốn tra cứu hoặc tìm một giá trị. Có nhiều tình huống tra cứu khác nhau mà người ta có thể gặp phải, nhưng để đơn giản, tôi sẽ cung cấp một ví dụ chung. Đoạn mã sau sẽ tìm một chuỗi ID trong cột đầu tiên của một bảng cụ thể và trả về số hàng của bảng ID đó. Hy vọng rằng bạn có thể sử dụng logic trong ví dụ này và áp dụng nó vào nhu cầu cụ thể của mình

Sub LookupTableValue()

Dim tbl As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject
Dim FoundCell As Range
Dim LookupValue As String

'Lookup Value
  LookupValue = "ID-123"

'Store Table Object to a variable
  Set tbl = ActiveSheet.ListObjects("Table1")

'Cố gắng tìm giá trị trong Cột đầu tiên của Bảng
   Bật Error Resume Next
  Set FoundCell = tbl.DataBodyRange.Columns(1).Find(LookupValue, LookAt:=xlWhole)
   Bật Lỗi Đi tới 0

'Return Table Row number if value is found
  If Not FoundCell Is Nothing Then
    MsgBox "Found in table row: " & _
      tbl. ListRows( FoundCell. Hàng - tbl. Tiêu ĐềHàngPhạm Vi. Hàng ngang). Chỉ mục
   Khác
    MsgBox "Không tìm thấy giá trị"
   End If

End Sub

Lưu trữ dữ liệu bảng trong một biến mảng

Lấy dữ liệu từ các bảng là một chiến thuật tuyệt vời để kết hợp với mã hóa VBA của bạn. Bàn là lý tưởng bởi vì chúng

• Luôn có cấu trúc giống nhau

• Có thể di chuyển đến bất kỳ đâu trên bảng tính mà không ảnh hưởng đến mã của bạn

• Tự động điều chỉnh kích thước phạm vi của chúng

Một ví dụ về việc sử dụng Bảng làm nguồn dữ liệu trong macro được hiển thị trong một trong các đoạn mã Code Vault của tôi. Đoạn mã này cho phép bạn lọc dữ liệu của mình dựa trên các từ trong một bảng cụ thể. Có rất nhiều cách khác nhau mà bạn có thể sử dụng bảng để lưu trữ cài đặt và tùy chọn động cho macro của mình. Đoạn mã dưới đây chỉ cho bạn cách tải dữ liệu từ một cột và bảng nhiều cột

Bảng cột đơn

Sub SingleColumnTable_To_Array()

Dim myTable As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject
Dim myArray As Variant
Dim TempArray As Variant
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")

'Tạo danh sách mảng từ bảng
  TempArray = myTable. DataBodyRange
  
'Chuyển đổi từ danh sách mảng dọc sang ngang
  myArray = Application. Transpose(TempArray)

'Lặp qua từng mục trong Mảng bảng (được hiển thị trong Cửa sổ ngay lập tức [ctrl + g])
   For x = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(x)
   Tiếp theo x
  
End Sub

Bảng nhiều cột

Sub MultiColumnTable_To_Array()

Dim myTable As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject
Dim myArray As Variant
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")

'Tạo danh sách mảng từ bảng
  myArray = myTable. DataBodyRange

'Lặp qua từng mục trong Cột thứ ba của Bảng (hiển thị trong Cửa sổ ngay lập tức [ctrl + g])
   Dành cho . Print myArray(x, 3) x = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(x, 3)
   Next x
  
End Sub

Thay đổi kích thước bảng

Nếu cần, bạn có thể thay đổi kích thước kích thước của bảng bằng cách khai báo một vùng phạm vi mới để bảng Excel thu nhỏ hoặc mở rộng. Dưới đây là một vài ví dụ cho thấy cách bạn có thể thực hiện loại điều chỉnh kích thước này

(Xin gửi lời cảm ơn đặc biệt đến Peter Bartholomew vì đã yêu cầu điều này trên LinkedIn)

Sub ResizeTable()

Dim rng As . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) . Thay đổi kích thước(7, 5) Range
Dim tbl As ListObject

'Resize Table to 7 rows and 5 columns
  Set rng = Range("Table1[#All]").Resize(7, 5)
  
  ActiveSheet. ListObjects("Bảng1"). Thay đổi kích thước rng
  
  
'Mở rộng kích thước Bảng thêm 10 hàng
   Set tbl = ActiveSheet.ListObjects("Table1")
  
   Set rng = Range(tbl. Tên & "[#Tất cả]"). Thay đổi kích thước (tbl. Phạm vi. hàng. Đếm + 10, tbl. Phạm vi. Cột. Đếm)
  
  tbl. Thay đổi kích thước rng

End Sub

Thay đổi tất cả tính toán tổng số hàng của bảng

Sub ChangeAllColumnTotals()

Làm mờ tbl As . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") . ListObjects("Table1") ListObject
Dim CalcType As Integer
Dim x As Long

Set tbl = ActiveSheet.ListObjects("Table1")

'Hàng Tổng nên có phép tính nào?
  CalcType = 1 'hoặc. xlTotalsCalculationSum

'Lặp qua tất cả các cột của bảng
   For x = 1 To tbl.ListColumns.Count
    tbl. Danh sáchCột(x). TotalsCalculation = CalcType
   Next x

'______________________________________________
'Members of xlTotalsCalculation
    'Enum       Calculation
    ' 0           None
    ' 1           Sum
    ' 2           Average
    ' 3           Count
    ' 4           Count Numbers
    ' 5           Min
    ' 6           Max
    ' 7           Std Deviation
    ' 8           Var
    ' 9           Custom
'___________________________________________

End Sub

Lấy ActiveTable

Có thể có những trường hợp bạn muốn tạo macro cá nhân định dạng bảng đã chọn của mình theo một cách nhất định hoặc thêm các cột tính toán nhất định. Vì các nhà phát triển Excel không tạo lệnh ActiveTable bằng ngôn ngữ VBA của họ nên bạn không có cách nào đơn giản để thao tác với bảng do người dùng chọn. Nhưng với một chút sáng tạo, bạn có thể tạo biến ListObject ActiveTable của riêng mình và làm bất cứ điều gì bạn muốn với bảng đã chọn

Sub DefineActiveTable()

Dim SelectedCell As SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên SelectedCell As . Danh sáchObject. Tên Range
Dim TableName As String
Dim ActiveTable As ListObject

Set SelectedCell = ActiveCell

'Determine if ActiveCell is inside a Table
  On Error GoTo NoTableSelected
    TableName = SelectedCell.ListObject.Name
     Đặt ActiveTable = ActiveSheet. ListObjects(TableName)
   Bật Lỗi GoTo 0

'Do something with your table variable (ie Add a row to the bottom of the ActiveTable)
  ActiveTable.ListRows.Add AlwaysInsert:=True
  
Thoát Sub

' .
NoTableSelected:
  MsgBox "Hiện không có Bảng nào được chọn. ", vbCritical

End Sub

Người học trực quan?

Xem kích thước đầy đủ

Ảnh chụp màn hình từ một trong các tab trong tệp có thể tải xuống

Sau nhiều yêu cầu, tôi đã tập hợp một sổ làm việc tương tác nhỏ thú vị sẽ cho bạn thấy một loạt mã được mô tả trong bài viết này thực sự hoạt động như thế nào trên một bảng tính. Nó cũng phục vụ như một tài liệu tham khảo tốt mà bạn có thể lưu vào máy tính của mình để bạn không phải tiếp tục tìm kiếm trên Google về Bảng Excel bất cứ khi nào có điều gì đó lướt qua tâm trí của bạn.  

Tải xuống tệp Excel mẫu

Nếu bạn muốn lấy một bản sao của tệp Excel mà tôi đã sử dụng trong suốt bài viết này, vui lòng tải xuống trực tiếp bảng tính bằng cách nhấp vào nút tải xuống bên dưới

Tải xuống tệp ví dụ

Các bài viết thú vị hơn về bảng Excel

• Sao chép và dán một bảng Excel vào Microsoft Word bằng VBA

• Lặp qua tất cả các bảng trong sổ làm việc hoặc trang tính Excel của bạn

• VBA để điền vào hộp tổ hợp biểu mẫu người dùng từ bảng Excel

• Chèn và xóa các hàng trong bảng với tính năng bảo vệ trang tính

• Chức năng để xác định xem tiêu đề cột có tồn tại trong bảng Excel không

Bất cứ điều gì khác về các bảng tôi đã bỏ lỡ?

Bạn đã đến trang này để cố gắng tìm hiểu cách thực hiện điều gì đó với bảng VBA và Excel và nó không được đề cập? . Nếu thấy hợp lý, hãy thêm nó vào hướng dẫn này và chắc chắn sẽ thêm nó vào nội dung. Tôi mong được đọc những suy nghĩ và/hoặc đề xuất của bạn

Giới thiệu về tác giả

chào bạn. Tôi là Chris và tôi điều hành trang web TheSpreadsheetGuru khi rảnh rỗi. Ban ngày, tôi thực sự là một chuyên gia tài chính phụ thuộc khá nhiều vào Microsoft Excel trong thế giới doanh nghiệp. Tôi thích lấy những điều tôi học được trong “thế giới thực” và chia sẻ chúng với mọi người ở đây trên trang web này để bạn cũng có thể trở thành chuyên gia bảng tính tại công ty của mình

Qua nhiều năm làm việc trong thế giới doanh nghiệp, tôi đã có thể nắm bắt các cơ hội để làm việc với Excel tốt hơn và đã xây dựng nhiều phần bổ trợ Excel khác nhau, từ việc chèn ký hiệu dấu tích đến tự động sao chép/dán từ Excel sang PowerPoint. Nếu bạn muốn cập nhật những tin tức mới nhất về Excel và nhận trực tiếp các mẹo Excel có ý nghĩa nhất mà tôi đã học được qua email trong nhiều năm, bạn có thể đăng ký nhận bản tin miễn phí của tôi. Tôi hy vọng tôi có thể cung cấp cho bạn một số giá trị ngày hôm nay và hy vọng sẽ sớm gặp lại bạn ở đây. - Chris

Bạn có thể tham khảo một bảng trong VBA không?

Tên bảng trong Excel là gì?

ListObject trong Excel VBA là gì?