VBA Excel - 在 VBA 中存储列表的方法

使用VBA编程语言在Excel中存储列表是一种非常常见的任务。VBA（Visual Basic for Applications）是一种用于编写和执行自定义宏的编程语言，它与Excel紧密集成，提供了许多功能来处理和操作数据。

vba
Sub StoreListUsingArray()
    Dim studentList() As String
    Dim i As Integer
    
    ' 定义列表大小
    ReDim studentList(1 To 5)
    
    ' 存储列表数据
    studentList(1) = "John"
    studentList(2) = "Mary"
    studentList(3) = "Tom"
    studentList(4) = "Emily"
    studentList(5) = "David"
    
    ' 遍历并输出列表数据
    For i = 1 To 5
        Debug.Print studentList(i)
    Next i
End Sub

vba
Sub StoreListUsingCollection()
    Dim cityList As Collection
    Dim cityName As Variant
    
    ' 创建集合对象
    Set cityList = New Collection
    
    ' 存储列表数据
    cityList.Add "Beijing"
    cityList.Add "Shanghai"
    cityList.Add "Tokyo"
    cityList.Add "New York"
    cityList.Add "Paris"
    
    ' 遍历并输出列表数据
    For Each cityName In cityList
        Debug.Print cityName
    Next cityName
End Sub

vba
Sub StoreListUsingDataTable()
    Dim ws As Worksheet
    Dim employeeList As ListObject
    Dim i As Integer
    
    ' 获取当前活动工作表
    Set ws = ActiveSheet
    
    ' 创建数据表
    Set employeeList = ws.ListObjects.Add(xlSrcRange, Range("A1:D6"), , xlYes)
    
    ' 存储列表数据
    employeeList.DataBodyRange.Cells(1, 1) = "ID"
    employeeList.DataBodyRange.Cells(1, 2) = "Name"
    employeeList.DataBodyRange.Cells(1, 3) = "Department"
    employeeList.DataBodyRange.Cells(1, 4) = "Salary"
    
    employeeList.DataBodyRange.Cells(2, 1) = 1
    employeeList.DataBodyRange.Cells(2, 2) = "John"
    employeeList.DataBodyRange.Cells(2, 3) = "HR"
    employeeList.DataBodyRange.Cells(2, 4) = 3000
    
    ' 遍历并输出列表数据
    For i = 1 To employeeList.DataBodyRange.Rows.Count
        Debug.Print employeeList.DataBodyRange.Cells(i, 1)
        Debug.Print employeeList.DataBodyRange.Cells(i, 2)
        Debug.Print employeeList.DataBodyRange.Cells(i, 3)
        Debug.Print employeeList.DataBodyRange.Cells(i, 4)
    Next i
End Sub